|
BDE
Set database property during runtime
Current record number for a dataset
Flush the BDE cache.
Pack Table
DBEdit and real values
Which driver a TDatabase is connected to
Re-index Paradox files within my program
Defining a BDE Alias in Code
Path stored in BDE Alias
Detect deleted records in .DBF
Install BDE
Alias on the fly
Aliases
EXTRACTING INDEX DATA FROM A TABLE
Using the ASCII driver with comma-delimited files
Determining record number in a dBase table
Configuring an ODBC driver and alias for Delphi
Witing BDE buffer to disk
Detecting installed BDE
BDE alias info
Set database property during runtime
Question
I want to set Database property of TTable in a program code. I tried:
TTable.Database := Database
It does not work. Delphi says that the Database property is ReadOnly.
Answer
Set the DatabaseName property to either:
the directory where your table files are.
the BDE alias of your database.
the DatabaseName of your TDatabase component if you have one.
Current record number for a dataset
Question
How can I determine the current record number for a dataset?
Answer
A:
If the dataset is based upon a Paradox or dBASE table then the record number
can be determined with a couple of calls to the BDE (as shown below). The
BDE doesn't support record numbering for datasets based upon SQL tables, so
if your server supports record numbering you will need to refer to its
documentation.
The following function takes as its parameter any component derived from
TDataset (i.e. TTable, TQuery, TStoredProc) and returns the current record
number (greater than zero) if it is a Paradox or dBASE table. Otherwise,
the function returns zero.
NOTE: for dBASE tables the record number returned is always the physical
record number. So, if your dataset is a TQuery or you have a range set
on your dataset then the number returned won't necessarily be relative to
the dataset being viewed, rather it will be based on the record's physical
position in the underlying dBASE table.
uses DbiProcs, DbiTypes, DBConsts;
function RecordNumber(Dataset: TDataset): Longint;
var
CursorProps: CurProps;
RecordProps: RECProps;
begin
{ Return 0 if dataset is not Paradox or dBASE }
Result := 0;
with Dataset do
begin
{ Is the dataset active? }
if State = dsInactive then DBError(SDataSetClosed);
{ We need to make this call to grab the cursor's iSeqNums }
Check(DbiGetCursorProps(Handle, CursorProps));
{ Synchronize the BDE cursor with the Dataset's cursor }
UpdateCursorPos;
{ Fill RecordProps with the current record's properties }
Check(DbiGetRecord(Handle, dbiNOLOCK, nil, @RecordProps));
{ What kind of dataset are we looking at? }
case CursorProps.iSeqNums of
0: Result := RecordProps.iPhyRecNum; { dBASE }
1: Result := RecordProps.iSeqNum; { Paradox }
end;
end;
end;
A:
{Retrieves physical record number a la xBase Requires DBITYPES, DBIPROCS,
and DBIERRS in the uses clause of the unit. Function takes one argument
of type TTable (e.g., Table1).}
function Form1.Recno( oTable: TTable ): Longint;
var
rError: DBIResult;
rRecProp: RECprops;
szErrMsg: DBIMSG;
begin
Result := 0;
try
oTable.UpdateCursorPos;
rError := DbiGetRecord( oTable.Handle, dbiNOLOCK, nil, @rRecProp );
if rError = DBIERR_NONE then
Result := rRecProp.iPhyRecNum
else
case rError of
DBIERR_BOF: Result := 1;
DBIERR_EOF: Result := oTable.RecordCount + 1;
else
begin
DbiGetErrorString( rError, szErrMsg );
ShowMessage( StrPas( szErrMsg ));
end;
end;
except
on E: EDBEngineError do ShowMessage( E.Message );
end;
end;
Flush the BDE cache.
Question
I just had a user system crash and lost about 10 inserts. Is it possible to flush the cache to disk ever so often?
Answer
A:
I do it by closing the Table and reopening it. I don't know if that's the best
way, but it works, and saved some user's work. I save a bookmark before
closing the table and after opening, restore the table cursor to it's
prevoius position so the user (almost) does not know of the procedure.
A:
I do it by closing the Table and reopening it. I don't know if that's the best
way, but it works, and saved some user's work. I save a bookmark before
closing the table and after opening, restore the table cursor to it's
prevoius position so the user (almost) does not know of the procedure.
Pack Table
Question
In delphi programming is it possible do the operation of PACK TABLE (erasing
deleted records)?
Answer
A:
The following is courtesy of Steve Teixeira of Borland
Packing a dBASE table requires a call to the BDE function DbiPackTable.
An example of its use appears below, including error checking. To use
the DbiPackTable function, the calling unit must have the BDE wrapper
units DbiTypes, DbiErrs, and DbiProcs in its Uses section.
If the DbiPackTable fails, it does not generate an error message. To
gauge its success or failure, you must check the function's return value.
If the function succeeded, the return value will be DBIERR_NONE. Any
other return value indicates an error condition, and the specific return
value would be used to determine the cause of the error condition and
what needs to be done about it.
Example code follows...
procedure TForm1.Button1Click(Sender: TObject);
var
Error: DbiResult;
ErrorMsg: String;
Special: DBIMSG;
begin
table1.Active := False;
try
Table1.Exclusive := True;
Table1.Active := True;
Error := DbiPackTable(Table1.DBHandle, Table1.Handle, nil, szdBASE, True);
Table1.Active := False;
Table1.Exclusive := False;
finally
Table1.Active := True;
end;
case Error of
DBIERR_NONE:
ErrorMsg := 'Successful';
DBIERR_INVALIDPARAM:
ErrorMsg := 'The specified table name or the pointer to the table ' +
'name is NULL';
DBIERR_INVALIDHNDL:
ErrorMsg := 'The specified database handle or cursor handle is ' +
'invalid or NULL';
DBIERR_NOSUCHTABLE:
ErrorMsg := 'Table name does not exist';
DBIERR_UNKNOWNTBLTYPE:
ErrorMsg := 'Table type is unknown';
DBIERR_NEEDEXCLACCESS:
ErrorMsg := 'The table is not open in exclusive mode';
else
DbiGetErrorString(Error, Special);
ErrorMsg := '[' + IntToStr(Error) + ']: ' + Special;
end;
MessageDlg(ErrorMsg, mtWarning, [mbOk], 0);
end;
DBEdit and real values
Question
I have a Paradox database that needs to use numbers with values of real and a
picture of ###.# in the table. When I use DBEdit, I have created a parser of
sort with the following code:
procedure TForm.SpinButton1DownClick(Sender: TObject);
begin
value := value - 0.1;
Str (value:6:2,s);
Edit1.Text := s;
end;
The problem is that the table is storing very large and incorrect values for
the numbers. Example: I want to add 1.1 to a value of 4.3 and I get something
like 5.4000000000182 as the value set in the table. Basically, I put real
values in and get garbage out.
Answer
A:
When working with real numbers stored in a Paradox table, are you using type
Real as your data type? If so, try using Double. Double is an 8-byte (64-bit)
real number, which is what the BDE normally works with, while Real is a 6-byte
version that pretty much only Delphi and BP use. Or you could try using
Extended, which is 10 bytes.
Which driver a TDatabase is connected to
Question
I need to know if I'm connected to e.g. Oracle or Interbase so as to use
different sql syntaxes. Is there a way I can tell to which driver a
tdatabase is connected?
I show my user(s) a list of alias they can choose from and then assign the
alias name to the tdatabase...
Answer
A:
You could use the IDAPI dbiGetDatabaseDesc call. Here is a quick
snipit (be sure to add DB to your uses clause)
var
pDatabase: DBDrsc:
begin
{ pAlias is a PChar holding the Alias Name}
dbiGetDatabaseDesc ( pAlias, @pDatabase ) ;
Then just check pDatabase.szDbType to see what it is
Re-index Paradox files within my program
Question
I'm using Paradox tables for my application. However, some of the
indexes seem to be out of sync. How do i re-index the files
within my program?
Answer
A:
We are trying now with direct calling to BDE functions. In some Delphi pages,
there is a file with the description of that funtions. A collegue of mine
transform it in help file. Since I think that post it to the list can overload
the mail of someone, I can send it directly to the person who wants it.
I'm preparing a little app that can be useful to repair broken tables.
Allen, I will post it in the list like I did for the task app when it
will be readable for english man (it's in italian, I have to translate
it), but I can say by now that this don't fix the problem for me. One
of our customer always have the paradox tables broken, because it is
very large (it have WAV files - digitized voices - in blob fields). A
clean solution can be to always mantein small tabled linked in refe-
rential integrity, and to load large blobs in that separate tables.
Now nothing of this BDE functions works, and the only solution is to
delete manually the index files (all the .x00, .y01, etc., they are
.x?? and .y??, don't destroy other files!), elsewhere I CAN'T OPEN THE
TABLE, either with DBD!
Then I can re-create all the indexes manually (and then I forgot some-
thing and the app will crash...). If the app I will send don't work, I
think the only solution can be to fisically destroy the indexes and
then re-create it with BDE calls.
Defining a BDE Alias in Code
Question
How to defining BDE aliases in code?
Answer
A:
This Technical Information document will help step thru concepts regarding
the creation and use of ALIASES within your Delphi Applications.
Typically, you use the BDE Configuration Utility BDECFG.EXE to create and
configure aliases outside of Delphi. However, with the use of the TDatabase
component, you have the ability to create and use this ALIAS within your
application-- not pre-defined in the IDAPI.CFG.
The ability to create Aliases that are only available within your
application is important. Aliases specify the location of database tables
and connection parameters for database servers.
Ultimately, you can gain the advantages of using ALIASES within your
applications-- without having to worry about the existance of a
configuration entry in the IDAPI.CFG when you deploy your
application.
Summary of Examples:
- ------- -- ---------
Example #1:
Example #1 creates and configures an Alias to use
STANDARD (.DB, .DBF) databases. The Alias is
then used by a TTable component.
Example #2:
Example #2 creates and configures an Alias to use
an INTERBASE database (.gdb). The Alias is then
used by a TQuery component to join two tables of
the database.
Example #3:
Example #3 creates and configures an Alias to use
STANDARD (.DB, .DBF) databases. This example
demonstrates how user input can be used to
configure the Alias during run-time.
Example #1: Use of a .DB or .DBF database (STANDARD)
1. Create a New Project.
2. Place the following components on the form: - TDatabase, TTable,
TDataSource, TDBGrid, and TButton.
3. Double-click on the TDatabase component or choose Database Editor from
the TDatabase SpeedMenu to launch the Database Property editor.
4. Set the Database Name to 'MyNewAlias'. This name will serve as your
ALIAS name used in the DatabaseName Property for dataset components such as
TTable, TQuery, TStoredProc.
5. Select STANDARD as the Driveer Name.
6. Click on the Defaults Button. This will automatically add a PATH= in
the Parameter Overrides section.
7. Set the PATH= to C:\DELPHI\DEMOS\DATA (PATH=C:\DELPHI\DEMOS\DATA)
8. Click the OK button to close the Database Dialog.
9. Set the TTable DatabaseName Property to 'MyNewAlias'.
10. Set the TDataSource's DataSet Property to 'Table1'.
11. Set the DBGrid's DataSource Property to 'DataSource1'.
12. Place the following code inside of the TButton's OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Tablename:= 'CUSTOMER';
Table1.Active:= True;
end;
13. Run the application.
*** If you want an alternative way to steps 3 - 11, place the following
code inside of the TButton's OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Database1.DatabaseName:= 'MyNewAlias';
Database1.DriverName:= 'STANDARD';
Database1.Params.Clear;
Database1.Params.Add('PATH=C:\DELPHI\DEMOS\DATA');
Table1.DatabaseName:= 'MyNewAlias';
Table1.TableName:= 'CUSTOMER';
Table1.Active:= True;
DataSource1.DataSet:= Table1;
DBGrid1.DataSource:= DataSource1;
end;
*****
Example #2: Use of a INTERBASE database
1. Create a New Project.
2. Place the following components on the form: - TDatabase, TQuery,
TDataSource, TDBGrid, and TButton.
3. Double-click on the TDatabase component or choose Database Editor from
the TDatabase SpeedMenu to launch the Database Property editor.
4. Set the Database Name to 'MyNewAlias'. This name will serve as your
ALIAS name used in the DatabaseName Property for dataset components such as
TTable, TQuery, TStoredProc.
5. Select INTRBASE as the Driver Name.
6. Click on the Defaults Button. This will automatically add the
following entries in the Parameter Overrides section.
SERVER NAME=IB_SERVEER:/PATH/DATABASE.GDB
USER NAME=MYNAME
OPEN MODE=READ/WRITE
SCHEMA CACHE SIZE=8
LANGDRIVER=
SQLQRYMODE=
SQLPASSTHRU MODE=NOT SHARED
SCHEMA CACHE TIME=-1
PASSWORD=
7. Set the following parameters
SERVER NAME=C:\IBLOCAL\EXAMPLES\EMPLOYEE.GDB
USER NAME=SYSDBA
OPEN MODE=READ/WRITE
SCHEMA CACHE SIZE=8
LANGDRIVER=
SQLQRYMODE=
SQLPASSTHRU MODE=NOT SHARED
SCHEMA CACHE TIME=-1
PASSWORD=masterkey
8. Set the TDatabase LoginPrompt Property to 'False'. If you supply the
PASSWORD in the Parameter Overrides section and set the LoginPrompt to
'False', you will not be prompted for the
password when connecting to the database. WARNING: If an incorrect
password in entered in the Parameter Overrides section and LoginPrompt is
set to 'False', you are not prompted by the Password dialog to re-enter a
valid password.
9. Click the OK button to close the Database Dialog.
10. Set the TQuery DatabaseName Property to 'MyNewAliias'.
11. Set the TDataSource's DataSet Property to 'Query1'.
12. Set the DBGrid's DataSource Property to 'DataSource1'.
13. Place the following code inside of the TButton's OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Query1.SQL.Clear;
Query1.SQL.ADD(
'SELECT DISTINCT * FROM CUSTOMER C, SALES S
WHERE (S.CUST_NO = C.CUST_NO)
ORDER BY C.CUST_NO, C.CUSTOMER');
Query1.Active:= True;
end;
14. Run the application.
Example #3: User-defined Alias Configuration
This example brings up a input dialog and prompts the user to enter the
directory to which the ALIAS is to be configured to.
The directory, servername, path, database name, and other neccessary Alias
parameters can be read into the application from use of an input dialog or
.INI file.
1. Follow the steps (1-11) in Example #1.
2. Place the following code inside of the TButton's OnClick event.
procedure TForm1.Buttton1Click(Sender: TObject);
var
NewString: string;
ClickedOK: Boolean;
begin
NewString := 'C:\';
ClickedOK := InputQuery('Database Path',
'Path: --> C:\DELPHI\DEMOS\DATA', NewString);
if ClickedOK then
begin
Database1.DatabaseName:= 'MyNewAlias';
Database1.DriverName:= 'STANDARD';
Database1.Params.Clear;
Database1.Params.Add('Path=' + NewString);
Table1.DatabaseName:= 'MyNewAlias';
Table1.TableName:= 'CUSTOMER';
Table1.Active:= True;
DataSource1.DataSet:= Table1;
DBGrid1.DataSource:= DataSource1;
end;
end;
3. Run the Application.
- -------------------------------------
See Also:
Delphi On-line help -->
Database Properties Editor
TDatabase
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.
Path stored in BDE Alias
Question
Does anyone know how to get the path stored in a BDE Alias without using
dummy components ?
Answer
A:
Use Session.GetAliasParams. You will get a Tstrings object from where you
can extract the value for 'PATH". Look in the help for TSession. The Session
object is
declared in the DB unit.
This code may work:
uses
db;
var
aliaspath : string[128];
begin
aliaspath := Session.GetAliasParams['MyAlias'].values['PATH'];
end;
A:
uses SysUtils,DbiProcs, DBiTypes;
...
function GetDataBaseDir(const Alias : string): String;
(* Will return the directory of the database given the alias
(without trailing backslash) *)
var
sp : PChar;
Res : pDBDesc;
begin
try
New(Res);
sp := StrAlloc(length(Alias)+1);
StrPCopy(sp,Alias);
if DbiGetDatabaseDesc(sp,Res) = 0
then Result := StrPas(Res^.szPhyName)
else Result := '';
finally
StrDispose(sp);
Dispose(Res);
end;
end;
Detect deleted records in .DBF
Question
How to detect deleted records in .DBF
Answer
A:
The routines are extracted from "Dtopics Database 1.10 from 3K computer
Consultancy "
<--------- Begin ------------>
Dbase causes more 'special case' situations to the BDE than SQL tables
and Paradox, due to its support of Expressions in indexes, etc. ie.
1) Index Creation/recreation
- DbiRegenIndexes( Table1.Handle ); { Regenerate all indexes }
- create index (depends if expression exists or not)
if (( Pos('(',cTagExp) + Pos('+',cTagExp) ) > 0 ) then
Table1.AddIndex( cTagName, cTagExp, [ixExpression]) (<- ixExpression is a _literal_)
else
Table1.AddIndex( cTagName, cTagExp, []);
2) Master/Detail links on an expression child index
- call BDE proc DbiLinkDetailToExp() instead of
the usual DbiLinkDetail()
3) Packing Tables
- with Table1 do
StrPCopy( TName, TableName );
Result := DBIPackTable( DbHandle, Handle, TName, szDBASE, TRUE );
4) Setting visiblity of Deleted records on/off (ie dBase SET
DELETED ON/OFF)
- DbiSetProp( hDBIObj(Table1.Handle), curSOFTDELETEON, LongInt(bValue));
5) Setting character matches partial/exact on/off (ie dBase SET
EXACT ON/OFF)
- DbiSetProp( hDBIObj(Table1.Handle), curINEXACTON, LongInt(bValue));
<------- End ---------->
And this is what I used exactly:
<------- Begin --------->
Q. "How can I view dBASE records marked for deletion?"
A. Call the following function on the AfterOpen event of the table. You
Must include DBITYPES, DBIERRS, DBIPROCS in the uses clause. To call,
send as arguments name of TTable and TRUE/FALSE depending to show/not
show deleted records. Ex:
procedure TForm1.Table1AfterOpen(DataSet: TDataset);
begin
SetDelete(Table1, TRUE);
end;
procedure SetDelete(oTable:TTable; Value: Boolean);
var
rslt: DBIResult;
szErrMsg: DBIMSG;
begin
try
Table.DisableControls;
try
rslt := DbiSetProp(hDBIObj(oTable.Handle), curSOFTDELETEON,
LongInt(Value));
if rslt <> DBIERR_NONE then
begin
DbiGetErrorString(rslt, szErrMsg);
raise Exception.Create(StrPas(szErrMsg));
end;
except
on E: EDBEngineError do ShowMessage(E.Message);
on E: Exception do ShowMessage(E.Message);
end;
finally
Table.Refresh;
Table.EnableControls;
end;
end;
Q. "How can I create a column in the grid to which records in a dBASE
table are marked for deletion?"
A. Create a calculated field, then for the OnCalcField event of the
table replace the calculated field you've created like so:
procedure TForm1.Table1CalcFields(DataSet: TDataset);
var
RCProps : RecProps;
Result : DBIResult;
begin
Result := DbiGetRecord(Table1.Handle, dbiNo
Install BDE
Question
Does anybody know how you can install the BDE-files from your own
setup-program. I have finished a database-project, and I want to include all
the necessary files in my own install-program, so the end-users don't have
to install the BDE-distribution-disks apart of my database-application.
I'm talking of Delphi version 1.1 and the BDE that came with it. I have
found some help on how to make aliases programmaticaly, but not on how to
install the BDE programmaticaly.
Answer
A:
Here are the file names you need to install at runtime .
Borland Database Engine
Unique File on BDE Disk #1
File Name :- IDAPICFG.PAK
Install Exe :-Setup.exe
Borland SQL Links
Unique File on SQL Disk #1
File Name :- MNOVLWP.PAK
Install Exe :-Setup.exe
Borland ReportSmith Runtime
Unique File on RPT Disk #1
File Name :- INSTXTRA.PAK
Install Exe :-Setup.exe
Borland State that YOU must use their Install programs
when installing the runtime versions.
Alias on the fly
Question
Could how be created ALIAS of temporal manner in time of execution?
Answer
Try this:
type
TDataMod = class(TDataModule)
Database: TDatabase;
public
procedure TempAlias(NewAlias, NewDir: String);
end;
procedure TDataMod.TempAlias(NewAlias, NewDir: String);
begin
with Session do
if not IsAlias(NewAlias) then
begin
ConfigMode := cmSession; (* the NewAlias will be TEMPORARY *)
try
AddStandardAlias(NewAlias, NewDir, 'PARADOX');
Database.Close;
Database.AliasName := NewAlias;
Database.Open;
finally
ConfigMode := cmAll;
end;
end;
end;
COMMENTS:
a) Insert the Database component in a DataModule form;
b) Put the property DatabaseName of Database, p.es. 'TempDB';
c) Put the property DatabaseName of TTable components = 'TempDB'
d) For further reference, check MastApp example shipped with D2:
Aliases
Question
Can anyone tell me how to retreive the path of an Alias after seclecting it
from a DBComboBox or from a simple DBEdit field?
Answer
Try the following code:
var
theStrList : TStringList;
GPath : String;
begin
theStrList := TStringList.Create;
{Use GetAliasParams to lookup an alias and get the associated path}
Session.GetAliasParams(,theStrList);
{Strip out the first six characters which always equal "PATH="}
GPath := copy(theStrList[0],6,length(theStrList[0]))
theStrList.Free;
EXTRACTING INDEX DATA FROM A TABLE
Question
Which information can I obtain about indexes from a table?
Answer
Getting a list of the indexes associated with a table at run-time can be
as simple as a call to the GetIndexNames method of the TTable, TQuery, or
TStoredProc component. The GetIndexNames method returns a list of the
that are available for the data set in the form of a TStrings list, which
may then be inserted into such visual components as a TListBox through its
Items property:
ListBox1.Clear;
Table1.GetIndexNames(ListBox1.Items);
Of course, the TStrings list returned by the GetIndexNames method need not
be used with a visual component. It could just as well serve as an array
of index names stored entirely in memory, that can be used as a list or
array.
But it is also possible to retrieve much more information about the
indexes for a table than just the names. Other descriptive attributes
include the name of each index, the names of the fields that comprise each
index, and the index options used when each index was created. Retrieving
these values is slighhtly more involved than the use of the GetIndexNames.
Basically, this process involves iterating through the IndexDefs property
of the TTable, TQuery, or TStoredProc component. The IndexDefs property is
essentially an array of records, one record for each index for the table.
Each index record contains information about the index. It is a relatively
straightforward process to iterate through this array of index descrip-
tions, extracting information about individual indexes.
The IndexDefs property of the TTable component contains information about
the indexes for the table used by the TTable, TQuery, or TStoredProc comp-
onent in use. The IndexDefs property itself has various properties that
allow for the extraction of information about specific indexes. The two
properties in the IndexDefs object are:
Count: type Integer; available only at run-time and read-only; indicates
the number ofentries in the Items property (i.e., the number of
indexes in thhe table).
Items: type TIndexDef; available only at run-time and read-only; an
array of TIndexDef objects, one for each index in the table.
The Count property of the IndexDefs object is used as the basis for a
loop program construct to iterate through the Items property entries to
extract specific information about each index. Each IndexDef object con-
tained in the Items property consists of a number of properties that pro-
vide various bits of information that describe each index. All of the
properties of the IndexDef object are available only at run-time and are
all read-only. These properties are:
Expression: type String; indicates the expression used for dBASE multi-
field indexes.
Fields: type String; indicates the field or fields upon which the
index is based.
Name: type String; name of the index.
Options: type TIndexOptions; characteristics of the index (ixPrimary,
ixUnique, ettc.).
Before any index information (Count or Items) can be accessed, the Update
method of the IndexDefs object must be called. This refreshes or init-
ializes the IndexDef object's view of the set of indexes.
Examples
========
Here is a simple For loop based on the Count property of the IndexDefs
object that extracts the name of each index (if any exist) for the table
represented by the TTable component Table1:
procedure TForm1.ListBtnClick(Sender: TObject);
var
i: Integer;
begin
ListBox1.Items.Clear;
with Table1 do begin
if IndexDefs.Count > 0 then begin
for i := 0 to IndexDefs.Count - 1 do
ListBox1.Items.Add(IndexDefs.Items[i].Name)
end;
end;
end;
Below is an example showing how to extract information about indexes at
run-time, plugging the extracted values into a TStringGrid (named SG1).
procedure TForm1.FormShow(Sender: TObject);
var
i: Integer;
S: String;
begin
with Tablee1 do begin
Open;
{Refresh IndexDefs object}
IndexDefs.Update;
if IndexDefs.Count > 0 then begin
{Set up columns and rows in grid to match IndexDefs items}
SG1.ColCount := 4;
SG1.RowCount := IndexDefs.Count + 1;
{Set grid column labels to TIndexDef property names}
SG1.Cells[0, 0] := 'Name';
SG1.ColWidths[0] := 200;
SG1.Cells[1, 0] := 'Fields';
SG1.ColWidths[1] := 200;
SG1.Cells[2, 0] := 'Expression';
SG1.ColWidths[2] := 200;
SG1.Cells[3, 0] := 'Options';
SG1.ColWidths[3] := 300;
{Loop through IndexDefs.Items}
for i := 0 to IndexDefs.Count - 1 do begin
{Fill grid cells for current row}
SG1.Cells[0, i + 1] := IndexDefs.Items[i].Name;
SG1.Cells[1, i + 1] := IndexDefs.Items[i].Fields;
SG1.Cells[2, i + 1] := IndexDefs.Items[i].Expression;
if ixPrimary in IndexDefs.Items[i].Options then
S := 'ixPrimary, ';
if ixUnique in IndexDefs.Items[i].Options then
S := S + 'ixUnique, ';
if ixDescending in IndexDefs.Items[i].Options then
S := S + 'ixDescending, ';
if ixCaseInsensitive in IndexDefs.Items[i].Options then
S := S + 'ixCaseInsensitive, ';
if ixExpression in IndexDefs.Items[i].Options then
S := S + 'ixExpression, ';
if S > ' ' then begin
{Get rid of trailing ", "}
System.Delete(S, Length(S) - 1, 2);
SG1.Cells[3, i + 1] := S;
end;
end;
end;
end;
end;
Special Considerations
======================
There are idiosyncracies associated with extracting information about
indexes for different table types that Delphi can access.
dBASE Tables
------------
With dBASE indexes, which properties of Fields and Expression will be
filled will depend on the type of index, simple (ssingle-field) or
complex (based on multiple fields or a dBASE expression). If the index
is a simple one, the Fields property will contain the name of the field
in the table on which the index is based and the Expression property will
be blank. If the index is a complex one, the Expression property will
show the expression on which the index is based (e.g., "Field1+Field2")
and the Fields property will be blank.
Paradox Tables
--------------
With Paradox primary indexes, the Name property will be blank, the Fields
property will contain the field(s) on which the index is based, and the
Options property will contain ixPrimary. With secondary indexes, the Name
property will contain the name of the secondary index, the Fields prop-
erty will contain the field(s) on which the index is based, and the
Options property may or may not have values.
The Fields property for indexes based on more than one field will show
the field names separated by semi-colons. Indexes based onn only a single
field will show the name of only that one field in the Fields property.
InterBase Tables
----------------
For both index types, single- or multiple-field, the Expression property
will be blank. For single-field indexes, the Fields property will contain
the field on which the index is based. For multi-field indexes, the Fields
property will show all of the multiple fields that comprise the index,
each separated by a semi-colon.
Indexes designated as PRIMARY when the CREATE TABLE command is issued will
have "RDB$PRIMARYn" in the Name property, where n is a number character
uniquely identifying the primary index within the database metadata.
Secondary indexes will show the actual name of the index.
Foreign key constraints also result in an index being created by the sys-
tem. These indexes appear in the IndexDefs property, and will have the
name "RDB$FOREIGNn" where n is a number character that uniquely identifies
the index within the database metaddata.
The Fields property for indexes based on more than one field will show
the field names separated by semi-colons. Indexes based on only a single
field will show the name of only that one field in the Fields property.
Using the ASCII driver with comma-delimited files
Question
How to use the ASCII driver with comma-delimited files?
Answer
Delphi (and the BDE) has the capability to use ASCII files to a limited
degree as tables. The ASCII driver has the capability to translate the
data values in an ASCII fixed-length field or a comma-delimted file into
fields and values that can be displayed through a TTable component. How
this translation of the ASCII file takes place depends on an accompanying
schema file. The schema file for an ASCII data file defines various attri-
butes necessary for parsing the ASCII data file into individual field
values. The field definitions for an ASCII fixed-length field file is
relatively straightforward, the offsets of various fields in the ASCII
file being consistent across all rows in the file. However, for comma-
delimited files, this process is slightly more complicated due to the
fact that not all data values in such a file may be the same length for
all rows in the file. This article, then, concentrates on this more
difficult task of reading data from comma-delimited, or varrying-length
field, files.
The Schema File
===============
The schema file for an ASCII data file contains information that defines
both the file type (comma-delimited versus fixed-length field), as well as
defining the fields that are represented by the data values in each row of
the ASCII data file. (All of the settings used in a schema file are case
insensitive, so "ascii" is just as valid as "ASCII".) In order that a
schema file be recognized as such, it must have the same filename as the
ASCII data file for which it provides definitions, but with the filename
extension .SCH (for SCHema). The attributes that describe the file are:
File name: Enclosed in square brackets, this setting specifies the
name of the ASCII data file (sans the filename extension,
which must be .TXT).
Filetype: Specifies whether the ASCII data file is structured as a
fixed-length field file (use a setting of FIXED) or a comma-
deelimited file (with data values of potentially varying
length (use a setting of VARYING).
Delimiter: Specifies the character that surrounds String type data val-
ues (typically, the double quotation mark, ASCII decimal 34).
Separator: Specifies the character that is used to separate individual
data values (typically, a comma). This character must be a
visible character, i.e., cannot be a space (ASCII decimal
32).
CharSet: Specifies the language driver (use a setting of ASCII).
Following the file definition settings are field definitions, one for each
data value on each row of the ASCII data file. These field definitions
supply the information Delphi and the BDE will need to create a virtual
field in memory to hold the data value, that virtual field's data type
which will affect how the value is translated after being read from the
ASCII file, and size and positioning attributes. The various seettings that
will appear in each field definition are:
Field: Virtual field name, will always be "Field" followed
by an integer number representing that field's ord-
inal position in respect to the other fields in the
ASCII data file. E.G., the first field is Field1,
the second Field2, and so on.
Field name: Specifies the display name for the field, which
appears as the column header in a TDBGrid. Naming
convention for ASCII table fields follows that for
Paradox tables.
Field type: Specifies the data tyoe BDE is to use in translating
the data value for each field and tells Delphi what
type of virtual field to create.
Use the setting For values of type
--------------- ----------------------
CHAR Character
FLOAT 64-bit floating point
NUMBER 16-bit integer
BOOL Boolean (T or F)
LONGINT 32-bit long integer
DATE Date field.
TIME Time field.
TIMESTAMP Date + Time field.
(The actual format for date and time data values
will be determined by the current setting in the BDE
configuration, Date tab page.)
Data value length: Maximum length of a field's corresponding data
value. This setting determines the length of the
virtual field that Delphi creates to receive values
read from the ASCII file.
Number of decimals: Applicable to FLOAT typpe fields; specifies the
number of digit positions to the right of the deci-
mal place to include in the virtual field defini-
tion.
Offset: Offset from the left that represents the starting
position for the field in relation to all of the
fields that preceed it.
For example, the field definition below is for the first field in the
ASCII table. It defines a String type data value with a name of "Text",
a maximum data value length of three characters (and the field will
appear as only three characters long in Delphi data-aware components such
as the TDBGrid), no decimal places (a String data value will never have
any decimal places), and an offset of zero (because it is the first field
and there would not be any preceeding fields).
Field1=Text,Char,3,00,00
Here is an example of a schema file with three fields, the first of String
type and tthe second and third of type date. This schema file would be
contained in a file named DATES.SCH to provide file and field definitions
for an ASCII data file named DATES.TXT.
[DATES]
Filetype=VARYING
Delimiter="
Separator=,
CharSet=ascii
Field1=Text,Char,3,00,00
Field2=First Contact,Date,10,00,03
Field3=Second,Date,10,00,13
This schema defines a comma-delimited field where all String type data
values can be recognized as being surrounded by the double quotation mark
and where distinct data values are separated by commas (excepting any
commas that may appear within the specified delimiter, inside individual
String data values). The character field has a length of three characters,
no decimal places, and an offset of zero. The first date field has a
length of 10, no decimals, and an offset of three. And the second date
field has a length of 10, no decimals, and an offset of 13.
For reading ASCII comma-delimited files, the length and offset parameteers
for the field definitions do not apply to data values in the ASCII files
(as is the case for fixed-length field files), but to the virtual fields,
defined in the application, into which the values read will be placed. The
length parameter will need to reflect the maximum length of the data value
for each field -- not counting the delimiting quotation marks or the comma
separators. This is most difficult to estimate for String type data values
as the actual length of such a data value may vary greatly from row to row
in the ASCII data file. The offset parameter for each field will not be
the position of the data value in the ASCII file (as is the case for
fixed-length field files), but the offset as represented by the cumulative
length of all preceding fields (again, the defined fields in memory, not
the data values in the ASCII file.
Here is a data file that would correspond to the schema file described
above, in a file named DATES.TXT:
"A",08/01/1995,08/11/19955
"BB",08/02/1995,08/12/1995
"CCC",08/03/1995,08/13/1995
The maximum length of an actual data value in the first field is three
("CCC"). because this is the first field and there are no preceding
fields, the offset for this field is zero. The length of this first field
(3) is used as the offset for the second field. The length of the second
field, a date value, is 10, reflecting the maximum length of a data value
for that field. The accumulated length of the first and second fields are
then used as the offset for the third field (3 + 10 = 13) .
It is only when the proper length for the data values in the ASCII file
are used and each fields length added to any preceding fields to produce
offset values for succeeding fields that this process will correctly read
the data. If data is misread because of improper length settings in the
schema file, most values will suffer adverse translation effects, such
as truncation of character data or numeric values being interprreted as
zeros. Data will usually still be displayed, but no error should occur.
However, values that must be in a specific format in order to be trans-
lated into the appropriate data type will cause errors if the value read
includes characters not valid in a date value. This would include a date
data value which, when incorrectly read may contain extraneous characters
from other surrounding fields. Such a condition will result in a data
translation exception requiring an adjustment of the field length and
offset settings in the schema file.
Determining record number in a dBase table
Question
How to determine the record number in dBase tables?
Answer
dBASE tables employ a fairly static record numbering system. The record
number for a given record reflects its physical position in the table
file. These record number are not subject to change dependent on the
filtering of data or index ordering. For instance, a record that is the
first record stored in the .DBF file would be record number 1. It is
possible that, through the ordering of an index, this record may be
displayed as the last of 100 records. In such a case, its record would
remain the same -- one -- and would not be changed to 100 to reflect its
position in the index ordered data set. This is in contrast with Paradox
tables, where there is a sequence number. The Paradox sequence number is
like the dBASE record number except that it is much more fluid and the
number for a given record will reflect its position relative to the data
set. That is, a record may not always have the same sequence number given
filtering of the data set to reduce the number of records orr when an index
is active that may change the displayed order of the record.
In database applications created with Delphi and the Borland Database
Engine (BDE), there is no provision built into the stock data components
for extracting or determining the record for dBASE tables. Such an opera-
tion is, however, possible by making a call from the application a BDE
function.
There are a number of BDE functions that will return information about the
current dBASE record, such as the record number. Basically, any function
that fills a BDE pRECProps structure would suffice. Such BDE functions
include DbiGetRecord, DbiGetNextRecord, and DbiGetPriorRecord. Of course,
only the first of these functions really applies to retrieving information
about the current record. The other two move the record pointer when in-
voked, similar in effect to the Next and Prior methods of the TTable or
TQuery components.
The pRECProps structure consists of the fields:
iSeqNum: type LongInnt; specifies the sequence number of the record
(relative to the data set, including filtering and index ordering);
applicable if the table type supports sequence numbers (Paradox only).
iPhyRecNum: type LongInt; specifies the record number for the record;
applicable only when the table type supports physical record numbers
(dBASE only).
bRecChanged: type Boolean; not currently used.
bSeqNumChanged: type Boolean; not currently used.
bDeleteFlag: type Boolean; indicates whether the record is deleted;
applicable only for table types that support soft-deletes (dBASE
only).
One of these BDE functions may be invoked in a Delphi application to fill
this structure, from which the physical record number may be retrieved.
Below is an example of the DbiGetRecord function used for this purpose.
function RecNo(ATable: TTable): LongInt;
var
R: RECProps;
rslt: DbiResult;
Error: array [0..255] of Char;
begin
ATable.UUpdateCursorPos;
rslt := DbiGetRecord(ATable.Handle, dbiNoLock, nil, @R);
if rslt = DBIERR_NONE then
Result := R.iPhyRecNum
else begin
DbiGetErrorString(rslt, Error);
ShowMessage(StrPas(Error));
Result := -1;
end;
end;
As with invoking any BDE function in a Delphi application, the BDE wrapper
units DbiTypes, and DbiErrs, DbiProcs must be included in the Uses section
of the unit in which the BDE function will be invoked (the Uses section
not shown here). To make this function more transportable, it does not
reference the subject TTable component directly, but a reference to the
TTable is passed as a parameter. If this function is used in a unit that
does not reference the Delphi units DB and DBTables, they must be added so
that references to the TTable component will be valid.
The UpdateCursorPos method of the TTable is called to ensure that the
record number current in the TTable component is synchronized with that
of thee underlying table.
BDE functions do not in themselves cause an exception if they fail.
Rather, they return a value of BDE type DbiResult that indicates the succ-
ess or failure of the intended operation. This return value must then be
retrieved and evaluated by the front-end application, and the appropriate
action taken. A result other than DBIERR_NONE indicates an unsuccessful
execution of the function. An extra step may be taken (as in the example
above) to query the BDE to translate an error code into a readable mess-
age with the BDE function DbiGetErrorString. In this example, the return
value from the invoking of DbiGetRecord is stored in the variable rslt,
and then compared against DBIERR_NONE to determine the success of the
function call.
If the call to DbiGetRecord succeeds, the physical record number from the
iPhyRecNum field of the pRECProps structure is stored to the variable
Result, which is the function's return value. To indicate when the
function hass failed (i.e., the involing of the DbiGetRecord function
failed), a value of negative one is returned instead of the record
number. This value is purely arbitrary, and any value of a compatible
type may be used at the discretion of the programmer.
Configuring an ODBC driver and alias for Delphi
Question
How to configure an ODBC driver and alias for Delphi?
Answer
CONTROL PANEL'S ODBC OPTION
Delphi installs an ODBC option to the Windows Control Panel
program. The ODBC option indicates the available data sources
(drivers) installed for use by ODBC. As you will find by
selecting the ODBC option, a number of formats are installed
with Delphi and are seen in the main window titled Data Sources.
Additional formats may be supported by the drivers installed and
can be configured by selecting the Add... button.
If a new driver is to be added or removed,
1. Select the Drivers... button from the Data Sources Window.
From the drivers dialog, select the Add... button and provide
the path where the ODBC driver will be found.
2. Return to the Data Sources Windows and include the possible
data sources available through the new driver by selecting its
Add... button.
3. To configure options available for a particular data
source use the Setup... button. The function of the Setup...
button will vary with each data format. Very often options like
the working directory for the driver are configured in this
area.
Online help is available for each dialog involved with the ODBC
option.
BDE CONFIGURATION UTILITY
After installing the ODBC driver, run the BDE Configuration
utility to configure the database engine to use the new driver.
1. From the drivers page, select the New ODBC driver button.
2. A dialog titled Add ODBC driver will appear. The option for
SQL link driver is what will distinguish the databases created
using this ODBC driver.
3. Next select the default ODBC driver. Dropping down the list
from the combobox will reveal the file types supported by ODBC
drivers installed on the system.
4. Select the default data source for the ODBC driver. Having
set the ODBC driver in step 3 above, the list of this combobox
will have the data source names appropriate for use with the
selected driver.
5. Select Ok.
6. Returning to the drivers page, select File/Save from the main
menu to save this configuration.
CREATING AN ALIAS IN THE DATABASE DESKTOP
While this can be done from the BDE Configuration utility, it is
more convenient overall to create ODBC aliases from the Database
Desktop.
1. From the File menu, select Aliases...
2. From the resulting Alias Manager dialog, select New.
3. Type the name for your new alias in the area labeled Database
Alias.
4. Use the drop down list of the Driver Type combobox to select
the driver appropriate for this alias. Paradox and dBase tables
are considered STANDARD. If the ODBC driver was properly
configured in the BDE Configuration utility its name will
appear in this list.
5. Additional options may appear depending upon the driver type
you select.
6. When finished, select Keep New to store the new alias. Then
select Ok. You will be prompted for whether or not to save the
aliases to IDAPI.CFG. Select Okay.
The alias will now be usable from both the Database Desktop and
Delphi.
Witing BDE buffer to disk
Question
Answer
General:
=======
Changes made to a table are not written directly to disk until
the table is closed. A power failure or system crash can
result in a loss of data, and an inconvenience. To avoid this
loss of data, two direct Database Engine calls can be made,
both of which have the similar effects. These functions are
DbiUseIdleTime and DbiSaveChanges.
DbiSaveChanges(hDBICur):
=======================
DbiSaveChanges saves to disk all the updates that are in the
buffer of the table associated with the cursor (hDBICur). It
can be called at any point. For example, one may want to make
save changes to disk every time a record is updated (add
dbiProcs to uses clause):
procedure TForm1.Table1AfterPost(DataSet: TDataSet);
begin
DbiSaveChanges(Table1.handle);
end;
This way, one does not have to worry about losing data if a
power failure or system crash occurs after a record update.
DbiSaveChanges can also be used to make a temporary table
(created by DbiCreateTempTable) permanent.
This function does NOT apply to SQL tables.
DbiUseIdleTime:
==============
DbiUseIdleTime can be called when the "Windows Message Queue"
is empty. It allows the Database Engine to save "dirty buffers"
to disk. In other words, it does what DbiSaveChanges does, but
performs the operation on ALL the tables that have been
updated. This operation however, will not necessarily occur
after every record update, because it can only be executed when
there is an idle period.
In Delphi, it can be used in this fashion (add dbiProcs to uses clause):
procedure TForm1.FormCreate(Sender: TObject);
begin
Application.onIdle := UseIdle;
end;
procedure Tform1.UseIdle(Sender: TObject; var Done: Boolean);
begin
DbiUseIdleTime;
end;
USAGE NOTES:
===========
Using both DbiUseIdleTime and DbiSaveChanges (after every
record modification) is redundant and will result in
unnecessary function calls. If the application is one that
perfroms a great deal of record insertions or modifications in
a small period of time, it is recommended that the client
either call DbiUseIdleTime during an idle period, or call
DbiSaveChanges after a group of updates.
If not very many updates are being performed on the table, the
client may choose to call DbiSaveChanges after every post or
set up a timer and call DbiUseIdleTime when a timer even is generated.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.
Detecting installed BDE
Question
How can I detect if the bde is installed on a clients PC ?
Answer
For delphi 2 you could check the registry under
HKEY_LOCAL_MACHINE\Software\Borland\Database Engine
For Delphi 1 check the Win.INI file for a section called IDAPI
[IDAPI]
DLLPATH=3DD:\WINPROG\DELPHI\IDAPI
CONFIGFILE01=3DD:\WINPROG\DELPHI\IDAPI\IDAPI.CFG
They might not be the best way since a user might have removed the BDE =
and the Registry or INI settings are still there.
BDE alias info
Question
Does anyone know how to interrogate the IDAPI configuration to get the
directory mapping for an alias?
Answer
A:
var MyAliasPath: string;
const AliasName='MyAlias';
{**** Get the MyAlias path from the BDE}
ParamsList:= TStringList.Create;
try
with Session do
begin
Session.GetAliasNames(ParamsList);
Session.GetAliasParams(AliasName,ParamsList);
MyAliasPath:=Copy(ParamsList[0],6,50)+'\';
end;
finally
ParamsList.Free;
end;
A:
uses DbiProcs, DBiTypes;
function GetDataBaseDir(const Alias : string): String;
(* Will return the directory of the database given the alias
(without trailing backslash) *)
var
sp : PChar;
Res : pDBDesc;
begin
try
New(Res);
sp := StrAlloc(length(Alias)+1);
StrPCopy(sp,Alias);
if DbiGetDatabaseDesc(sp,Res) = 0
then Result := StrPas(Res^.szPhyName)
else Result := '';
finally
StrDispose(sp);
Dispose(Res);
end;
end;
|