Creating database aliases in code
Question
How can I create aliases in code?
Answer
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.
Searching through query result sets
Question
The TQuery component does not offer the index-based search capabilities of
the TTable component (FindKey, GotoKey, and GotoNearest). So how do you
search within the result data set from a TQuery to find a row with a spec-
ific field value?
Answer
One way to search a query result set is a sequential search. This type of
search starts at the first row in the data set and, in a While loop,
sequentially compares the value of a field in the row with a search value.
One of two results are possible: a value will be found (success) or the
end of the data set will be reached (failure). The problem with this way
of searching the data set is that the further into the data set a row with
a matching value is, the longer it takes to arrive at that row. And, a
failed search takes longest of all because it must go all the way to the
last row in the data set. If the data set being searched is a large one,
this process may take a considerable amount of time.
Here is a function that will perfoorm a sequential search of the result set
from a TQuery:
function SeqSearch(AQuery: TQuery; AField, AValue: String): Boolean;
begin
with AQuery do begin
First;
while (not Eof) and (not (FieldByName(AField).AsString = AValue)) do
Next;
SeqSearch := not Eof;
end;
end;
This function takes three parameters:
1. AQuery: type TQuery; the TQuery component in which the search is to
be executed.
2. AField: type String; the name of the field against which the search
value will be compared.
3. AValue: type String; the value being searched for. If the field is of
a data type other than String, this search value should be
changed to the same data type.
The Boolean return value of this function indicates the success (True) or
failure (False) of the search.
An alternative is using a bracketing approach. On a conceptual level, this
method acts somewhat like a bb-tree index. It is based on the given that
for a row at a given point in the data set, the value of the field being
searched compared to the search value will produce one of three possible
conditions:
1. The field value will be greater than the search value, or...
2. The field value will be less than the search value, or...
3. The field value will be equal to the search value.
A bracketing search process uses this means of looking at the current row
in respect to the search value and uses it to successively reduce the rows
to be search by half, until only one row remains. This search field value
for this sole remaining row will either be a match to the search value
(success) or it will not (failure, and no match exists in the data set).
Functionally, this process lumps the condition of the search field being
less than or equal to the search value into a single condition. This
leaves only two possible results for the comparison of the current
search field valuue with the search value: less than/equal to or greater
than. Initially, a range of numbers is established. The low end of the
range is represented by an Integer, at the start of the search process set
to 0 or one less than the first row in the data set. The far end of the
range is also an Integer, with the value of the RecordCount property of
the TQuery. The current row pointer is then moved to a point half way
between the low and high ends of the range. The search field value at that
row is then compared to the search value. If the field value is less than
or equal to the search value, the row being sought must be in the lower
half of the range of rows so the high end of the range is reduced to the
current row position. If the field value is greater than the search value,
the sought value must be in the higher half of the range and so the low
end is raised to the current point. By repeating this process, the number
of rows that are encompassed in the range are successivelly reduced by
half. Eventually, only one row will remain.
Putting this into a modular, transportable function, the code would look
like that below:
function Locate(AQuery: TQuery; AField, AValue: String): Boolean;
var
Hi, Lo: Integer;
begin
with AQuery do begin
First;
{Set high end of range of rows}
Hi := RecordCount;
{Set low end of range of rows}
Lo := 0;
{Move to point half way between high and low ends of range}
MoveBy(RecordCount div 2);
while (Hi - Lo) > 1 do begin
{Search field greater than search value, value in first half}
if (FieldByName(AField).AsString > AValue) then begin
{Lower high end of range by half of total range}
Hi := Hi - ((Hi - Lo) div 2);
MoveBy(((Hi - Lo) div 2) * -1);
end
{Search field less than search value, value in far half}
else begin
{Raise low end of range by half of total range}
Lo := Lo + ((Hi - Lo) div 2);
MoveBy((Hi - Lo) div 2);
end;
end;
{Fudge for odd numbered rows}
if (FieldByName(AField).AsString > AValue) then Prior;
Locate := (FieldByName(AField).AsString = AValue)
end;
end;
Because there will never be a difference of less than one between the low
and high ends of the range of rows, a final fudge was added to allow the
search to find the search value in odd numbered rows.
This function takes the same three three parameters as the SeqSearch
function described earlier.
The return value of this function is of type Boolean, and reflects the
success or failure of the search. As the search does move the row pointer,
the effects of this movement on the implicit posting of changed data and
on where the desired position of the row pointer should be after a failed
search should be taken into account in the calling application. For
instance, a TBookmark pointer might be used to return the row pointer to
where it was prior to a search if that search fails.
How is this process better than a sequential search? First, in bracketing
the search value, only a fraction of the number of rows will be visited as
would be the case in a sequential search. Unless the row with the value
being sought is in the first 1,000 rows, this search method will be faster
than a sequential search. Because this process always uses the same number
of records, the search time will be consistent whether searching for the
value in row 1,000 or row 90,000. This is in contrast with the sequential
search that takes longer the farther into the data set the desired row is.
Can this method be used with any TQuery result set? No. Because of the way
this method works in basing the direction of the search as either high or
low, it depends on the row being ordered in a descending manner based on
the field in which the search will be conducted. This means that it can
only be used if the datta set is naturally in a sequential order or an
ORDER BY clause is used in the SQL statement for the TQuery. The size of
the result set will also be a factor when deciding whether to perform a
sequential or bracketing search. This process is most advantageous for
speed when used with larger result sets. With smaller sets (1,00 or less
rows), though, a sequential search will often be as fast or faster.
Cascading deletes with Paradox referential integrity
Question
How cascade deletes with Paradox referential integrity?
Answer
Paradox tables offer a Referential Integrity feature. This feature pre-
vents adding records to a child table for which there is no matching
record in the parent table. It will also cause the key field(s) in the
child table to be changed when the corresponding key field(s) in the
parent are changed (commonly referred to as a cascading update). These
events occur automatically, requiring no intervention by a Delphi appli-
cation using these tables. However, the Paradox Referential Integrity
feature will not accommodate cascading deletes. That is, Delphi will not
allow you to delete a record in the parent table while matching records
exist in the child table. This would make "orphans" of the child records,
losing referential integrity. Delphi raises an exception when an attempt
is made to delete such a parent record.
To effect a cascading delete requires that the deletion of the matching
child records be deleted programmatically -- before the parent record is
deleted. In aa Delphi application, this is done by interrupting the process
of deleting the record in the parent table, deleting the matching records
in the child table (if there are any), and then continuing with the dele-
tion of the parent record.
A record in a table is deleted by a call to the Delete method of the
TTable component, which deletes the current record in the associated
table. Interrupting the this process to first perform some other opera-
tions is a matter creating a procedure associated with the BeforeDelete
event of the TTable. Any commands in a BeforeDelete event procedure are
executed before the call actually goes out from the application to the
Borland Database Engine (BDE) to physically remove the record from the
table file.
To handle the deletion of one or more child records, in a BeforeDelete
event procedure the Delete method for the TTable representing the child
table is called in a loop. The loop is based on the condition of the
record pointer in the taable not being positioned at the end of the data
set, as indicated by the Eof method of the TTable. This also accounts for
there being no child records at all matching the parent record to be
deleted: if there are no matching records, the record pointer will already
be at the end of the data set, the loop condition will evaluate to False,
and the Delete method in the loop never gets executed.
procedure TForm1.Table1BeforeDelete(DataSet: TDataset);
begin
with Table2 do begin
DisableControls;
First;
while not Eof do
Delete;
EnableControls;
end;
end;
In the above example, the parent table is represented by the TTable comp-
onent Table1 and the child by Table2. The DisableControls and Enable-
Controls methods are used as a cosmetic measure to freeze any data-aware
components that might be displaying data from Table2 while the records
are being deleted. These two methods make the process visually appear
smoother, but aree only optional and not essential to this process. The
Next method need not be called within this loop. This is because the loop
begins at the first record and, as each record is deleted, the record that
previously followed the deleted record moves up in the data set, becoming
both the first and the current record.
This example presumes that the parent and child tables are linked with a
Master-Detail relationship, as is typical for tables for which such
Referntial Integrity is configured. Linking the tables in this manner
results in only those records in the child table that match the current
record in the parent table being available. All other records in the child
table are made unavailable through the Master-Detail filtering. If the
tables are not so linked, there are two additional considerations that
must be accounted for when deleting the child records. The first is that
a call to the First method may or may not put the record pointer on a
record that matches the ccurrent record in the parent table. This necessi-
tates using a search method to manually move the record pointer to a
matching record. The second consideration affects the condition for the
loop. Because records other than those matching the current record in the
parent table will be accessible, the condition for the loop must check
that each record is a matching record before attempting to delete it. This
checking is in addition to querying the Eof method. Because the records
will be ordered by this key field (from a primary or secondary index),
all of the matching records will be contiguous. This leads to the given
that, as soon as the first non-matching record is reached, it can be
assumed that all matching records have been deleted. Thus, the previous
example would be modified to:
procedure TForm1.Table1BeforeDelete(DataSet: TDataset);
begin
with Table2 do begin
DisableControls;
FindKey([Table1.Fields[0].AsString])
while (Fields[0].AsStrring = Table1.Fields[0].AsString) and
(not Eof) do
Delete;
EnableControls;
end;
end;
In the above, it is the first field in the parent table (Table1) upon
which the Referential Integrity is based, and the first field in the
child table (Table2) against which matching is judged.
dBase expression indexes: a primer
Question
Answer
Indexes for dBASE tables may be based on a the values from a single field,
unmodified, or on an expression. Index expressions, unique to dBASE
indexes, may be composed of multiple fields, modifications of field
values, or combinations of these. The expression for a dBASE expression
index is created by using dBASE functions and syntax to concatenate multi-
ple fields or to perform the modifications of field values for fields
included in the index expressions.
Two section are included at the end of this technical article which desc-
ribe the mechanics of creating dBASE expression indexes, one applicable
to doing this in the Database Desktop utility and the other for including
this capability in Delphi applications.
Expression Indexes Based On Multiple Fields
===========================================
dBASE functions are available for use in Delphi or the Database Desktop
for the express use in index expressions, and then only in conjunction
with dBASE indexes. That iis, you cannot use dBASE functions or syntax
to build an index expression for a Paradox or Local InterBase Server
(LIBS) table. Nor can dBASE functions be used in Delphi programming. They
are only available for dBASE expression indexes. The dBASE functions and
syntax that can be used for expression indexes are provided by the Borland
Database Engine (BDE) Dynamic Linked Library (DLL) file IDDBAS01.DLL.
When creating a dBASE index that is to be based on the values from two or
more fields in the table for which the index is being created, the two or
more fields are concatenated (connected together) in a manner similar to
how String type values are concatenated in Delphi syntax: the "+" oper-
ator. For example, the expression needed to create an index that orders
first on a LastName field and then on a FirstName field would be:
LastName + FirstName
Unlike in dBASE itself, such indexes that are based on multiple fields are
limited to using just those fields in the one table. dBASE allows the
creation of indexes based on multiple fields contained in different
tables. This is possible only by having the other table open at the time
the index is created or when the table containing the index is used.
With multi-field indexes for other table types (e.g., Paradox and Inter-
Base), the multiple fields are delimited by the semi-colon (;), as in:
LastName;FirstName
In dBASE expression indexes that concatenate multiple fields, an actual
expression is used:
LastName + FirstName
When creating index expressions that concatenate two or more fields, all
of the fields included in the index expression must be of the same data
type. Additionally, if they are to be concatenated instead of added
together, the fields must all be of String type. For example, given two
Integer type fields, Value1 and Value2, the index expression...
Value1 + Value2
...would not cause an error. But then, neither would it concatenate the
two field values; it would add them together. Thus, if Value1 for a given
record contained 4 and Value2 5, the resulting index node would be an
Integer value of 9, not a String concatenation "45".
If fields to be included in an expression index are not of String type,
they must be converted. Here are some dBASE functions to convert various
data types to String for purposes of creating index expressions:
STR( [, [, ]])
Converts from either Float or Numeric dBASE types to Character (String)
DTOS()
Converts Date value to Character, format YYYYMMDD
MLINE(, )
Extracts a single line from a memo field as a Character value
Another consideration in creating indexes based on the concatenation of
multiple field is the maximum allowable length of the index value. The
value returned by an index expression may not exceed 100 characters. This
is a limit on the length of the value returned by thee expression, not on
the length of the expression itself. For example, you cannot index on the
concatenation of two fields that both have a length of 255 characters.
Expression Indexes Based On Modifications Of Field Values
=========================================================
In addition to creating indexes based on the concatenation of two or more
field values, it is also possible to construct an index that is based on
a modification of a field value. Examples of this include indexing on just
the first three characters of a String type field, on just the year and
month from a Date field, indexing on a contantenation of a String and Date
field such that the ordering of the String field is ascending but the Date
descending, and even indexing on Boolean field values.
Creating indexes that are based on modifications of field values requires
at least a working knowledge of dBASE functions and syntax -- because the
process uses dBASE, and not Delphi, functions and ssyntax. The dBASE func-
tion SUBSTR() extracts a substring of a String value. The Delphi equiv-
alent for this dBASE function is Copy. But, of these two functions that
serve the same purpose, only SUBSTR() may be used in dBASE index express-
ions.
To use dBASE functions in dBASE index expressions, simply include the
function wherever an index expression is called for, using the approp-
riate dBASE syntax for the function, along with a reference to the
name(s) of the field(s) used in the function. For example, an index expr-
ession based on only the last three characters of a String type field
called Code, that is 20 characters long, would be:
RIGHT(Code, 3)
Caution should be used in constructing dBASE index expressions that modify
field values to ensure that the resulting expression would return a value
of a consistent length for every record in the table. For instance, the
dBASE TRIM() function removes the trailing blanks (ASCII decimal 32) from
a String type value. If this were used in conjunction with concatenating
two String fields where the field does not contain values of the same
length for all records, the value resulting from the expression will not
be the same for all records. Case in point, an index expression based on
the concatenation of a LastName and a FirstName field, where the TRIM()
function is applied to the LastName field:
TRIM(LastName) + FirstName
This expression would not return values of a consistent length for all
records. If the LastName and FirstName fields contained the values...
LastName FirstName
-------- ---------
Smith Jonas
Wesson Nancy
...the result of applying the index expression above would be:
SmithJonas
WessonNancy
As can be seen, the length of the value for the first record would be 10
characters, while that for the second 11 characters. The index nodes
created for this index expression would be based on the field values for
the first record encouuntered. This would result in an index node 10 char-
acters long being applied to the field values for all record. In this
example, that would result in the truncation of the expression value for
the second record to "WessonNanc". This would subsequently cause searches
based on the full field value to fail.
The solution to this dilemma would be to not use the TRIM() function so
that the full length of the LastName field, including padding from the
trailing spaces, is used. In indexes that use the IIF() function to order
by one field or another, based on the evaluation of a logical expression
in the IIF(), if the two fields are of different lengths, the shorter
field would need to be padded with spaces to make it the same length as
the longer field. For example, assuming an index that uses the IIF() func-
tion to index either on a Company or a Name field, based on the contents
of Category field, and where the Company field is 40 characters long but
the Name field is 25 chharacters long, the Name field would need to be
padded with 15 spaces; accomplished with the dBASE function SPACE(). That
index expression would then be:
IIF(Category = "B", Company, Name + SPACE(15))
Searches And dBASE Expression Indexes
=====================================
dBASE expression indexes are exceptions to the norm in how they are
handled by Delphi and the BDE as opposed to how multiple field indexes for
other table types are handled.
This puts such dBASE indexes into a separate class. Handling of such
indexes by Delphi and the BDE is different than those for other table
types. One of these differences is that not all index-based searching
using Delphi syntax can be used with these dBASE expression indexes. The
FindKey, FindNearest, and GotoKey methods of the TTable component cannot
be used with expression indexes. If an attempt to use FindKey is made,
this will result in the error message: "Field index out of range." If the
GotoKey method is ttried, this error message may occur or the record
pointer may just not move (indicating the search value was not found).
Only the GotoNearest method may be used with expression indexes. Even
then, the GotoNearest method may not work with some index expressions.
Experimentation will be needed to see whether the GotoNearest method
will work with a given index expression.
Filtering With dBASE Expression Indexes
=======================================
As with index-based searches, dBASE expression indexes present some
exceptions when using Delphi filtering.
With an expression index active, the SetRange method of the TTable comp-
onent will produce the error: "Field index out of range." However, with
the same expression index active, the SetRangeStart and SetRangeEnd
methods will successfully filter the data set.
For example, with an expression index concatenating a LastName and a
FirstName field active, the code below using the FindKey method (intended
to filter tto just those records where the first character of the LastName
field is "S") will fail with an error:
begin
Table1.SetRange(['S'], ['Szzz'])
end;
Whereas, the code below, with the same expression index active and filter-
ing on the same LastName field, will successfully filter the data and not
incur an error:
begin
with Table1 do begin
SetRangeStart;
FieldByName('LastName').AsString := 'S';
SetRangeEnd;
FieldByName('LastName').AsString := 'Szzz';
ApplyRange;
end;
end;
And, as is the case with index-based searches, with filtering, success of
a filtering attempt will also be dependent on the index expression. The
use of the SetRangeStart and SetRangeEnd methods in the preceeding example
worked with an index that simply concatenated two String type fields. But
if the expression for the index was instead based conditionally on one or
the other fields through use of the IIF() function, the same filtering
routinne would fail (although without an error).
Some Handy dBASE Index Expressions
==================================
Here are some handy dBASE index expressions. Some are readily apparent in
the intended purpose, others are more arcane.
Character field ascending and Date field descending
---------------------------------------------------
With a Character field called Name and a Date field OrdDate:
Name + STR(OrdDate - {12/31/3099}, 10, 0)
Character field ascending and Numeric (or Float) field descending
-----------------------------------------------------------------
With a Character field called Company and a Numeric field Amount (the
Amount field being 9 digits wide with two decimal places):
Company + STR(Amount - 999999.99, 9, 2)
Ordering by a Logical field
---------------------------
To have True values appear before False values for a Logical field called
Paid:
IIF(Paid, "A", "Z")
Two Numeric (or Float) fields
------------------------------
Assuming two Numeric fields with widths of five and two decimal places,
the first field named Price and the second Quantity:
STR(Price, 5, 2) + STR(Quantity, 5, 2)
Ordering by one field of two, depending on a logical condition
--------------------------------------------------------------
Ordering by the names of months in a Character field
----------------------------------------------------
Assuming a field containing the names of the months ("Jan," "Feb" etc.)
to put the records in proper month order (field named M):
IIF(M="Jan", 1, IIF(M="Feb", 2, IIF(M="Mar", 3, IIF(M="Apr", 4,
IIF(M="May", 5, IIF(M="Jun", 6, IIF(M="Jul", 7, IIF(M="Aug", 8,
IIF(M="Sep", 9, IIF(M="Oct", 10, IIF(M="Nov", 11, 12)))))))))))
(The above is a single expression line, broken into multiple lines here
due to page width.)
Ordering by the first line of a memo field
------------------------------------------
For a memo field named Notes:
MLINE(Notes, 1)
Ordering by the middle three characters in a nine character long field
----------------------------------------------------------------------
For a nine character long field called StockNo:
SUBSTR(StockNo, 4, 3)
Creating dBASE Expression Indexes In Database Desktop
=====================================================
In the Database Desktop utility, indexes may be created for a table either
duting the process of creating a new table or by restructuring an existing
table. In both cases, the Define Index dialog is used to create one or
more indexes for the table used.
To get to the Create Index dialog while creating a new table, in the
Create dBASE Table dialog (showing the structure), from the Table Proper-
ties listbox select "Indexes" and click the Define button.
To get to the Create Index dialog to create an index for an existing
table, select Utilities|Restructure, select the table file from the Select
File dialog, and in the Restructure dBASE Table diallog (showing the table
structure) from the Table Properties listbox select "Indexes" and click
the Define button.
Once in the Create Index dialog, expression indexes can be created by
clicking the Expression Index button and entering the expression to be
used in the Expression Index entry field. To assist in this process, you
can double-click on a field name in the Field List listbox and that field
name will be inserted into the Index Expression entry field at the current
insertion point (caret position).
Once the index expression has been entered, click the OK button. Enter the
name of the new index tag in the Index Tag Name entry field on the Save
Index As dialog and click OK. (Remember, dBASE index tag names cannot
exceed ten characters in length and must abide by the normal dBASE naming
conventions.)
Creating dBASE Expression Indexes In Delphi Applications
========================================================
dBASE indexes can be created programmatically iin Delphi applications,
either as a new table is being created (CreateTable method of the TTable
component) or by adding an index to an existing table.
Creating an index as part of a new table being created is a matter of
calling the Add method for the IndexDefs property of the TTable. A special
consideration that the index options must include the option ixExpression.
This index option is unique to dBASE indexes, and should only be used with
dBASE expression indexes. For example:
with Table1 do begin
Active := False;
DatabaseName := 'Delphi_Demos';
TableName := 'CustInfo';
TableType := ttdBASE;
with FieldDefs do begin
Clear;
Add('LastName', ftString, 30, False);
Add('FirstName', ftString, 20, False);
end;
with IndexDefs do begin
Clear;
Add('FullName', 'LastName + FirstName', [ixExpression]);
end;
CreateTable;
end;
Adding an index to an existing table is accomplished by calling the Add--
Index method of the TTable. Again, the index options must include the
TIndexOptions value ixExpression.
Table1.AddIndex('FullName', 'LastName + FirstName', [ixExpression]);
Learning More About dBASE Functions And Syntax
==============================================
Only dBASE functions and syntax that apply to data manipulation can be
used to construct a dBASE expression index. However, it is beyond the
scope of this technical article to fully list and describe all of these
functions. To learn more about dBASE data manipulation functions, the user
is advised to consult the dBASE Language Reference manual or one of the
many third-party dBASE books.
Interbase BLOB Fields: a primer
Question
Answer
InterBase BLOB fields are not all the same. They actually consist in a
variety of forms, or sub-types of the general BLOB type. Knowing which
sub-type of BLOB field to use when is essential to creating database appl-
ications that incorporate InterBase BLOB fields. BLOB fields come in three
varieties: sub-type 0 and sub-type 1 (the two predefined sub-types), and
user-defined sub-types.
Sub-type 0 BLOB fields are the type created when a CREATE command is
issued and a sub-type is not specified. For clarity in SQL syntax, though,
it is possible to explicitly indicate that the BLOB field is to be of sub-
type 0. This sub-type of BLOB field is for the storage of binary data.
InterBase makes no analysis of the data stored, it just stores it in the
BLOB field on a byte-for-byte basis. The most common intended use for BLOB
fields in Windows applications is the storage of bitmap binary data, typi-
cally for display in a TDBImage component. Either the BLOB field sub-type
0 or a uuser-defined sub-type BLOB field will work for this purpose.
The second predefined sub-type is 1. This BLOB field sub-type is designed
for the storage of text. Typically, this is the free-form memo or notes
data displayed and edited with the TDBMemo component. This BLOB field sub-
type is better for storing text data than the VARCHAR field because,
unlike with the VARCHAR field, there is no design-time limit placed on the
storage capacity of the field.
In SQL syntax, the sub-type 1 BLOB field is created by following the BLOB
field type keyword with the SUB_TYPE keyword and the integer one:
CREATE TABLE WITHBLOB
(
ID CHAR(3) NOT NULL PRIMARY KEY,
MEMO BLOB SUB_TYPE 1,
AMOUNT NUMERIC
)
Aside from the two predefined BLOB field sub-types, there are user-defined
sub-types. User-defined sub-types are designated by a negative integer
value in association with the SUB_TYPE keyword. The actual integer value,
as long as it is negative, is actually arbitrary and up to the discretion
of the table creator. A designation of -1 is functionally the same as that
of a -2. The only consideration when using user-defined sub-types is
ensuring that the same type of binary data is stored for every row in the
table for a BLOB field of a given user-defined sub-type. InterBase will
not evaluate whether this criteria is met, and it is the responsibility of
the application inserting the binary data to store the appropriate type of
data. No error will occur from the InterBase side if an incorrect type of
binary data is stored in a user-defined BLOB field sub-type, but an appl-
ication can incur difficulties if it is expecting one type of data but
encounters another.
A BLOB field of a user-defined sub-type is created with the SQL syntax
such as that below:
CREATE TABLE IMAGE_DATA
(
FILENAME CHAR(12) NOT NULL PRIMARY KEY,
BITMAP BLOB SUB_TYPE -1,
EXEs BLOB SUB_TYPE -2,
)
When using a table created with the abovee command, the field BITMAP would
only be used to store one distinct type of binary data for all records. In
this case, bitmap data. The field EXEs implies the storage of executable
files loaded from disk. If an application using this table were to mis-
takenly store binary data that should have been in the EXEs field into the
BITMAP field, InterBase would generate no errors, but the application
would have extreme difficulties displaying a stored executable file in a
TDBImage component.
InterBase BLOB fields and Delphi
--------------------------------
When defining TField objects for InterBase BLOB fields in Delphi, the
various BLOB field sub-types are assigned TField derivative types as
follows:
Sub-type 0: TBlobField
Sub-type 1: TMemoField
User-defined: TBlobField
Because both the predefined sub-type 0 and user-defined sub-types are
recognized as TBlobField objects, care must be taken when designing an
application to not mistake a field of one subb-type for that of another.
The only way to differentiate between a field of sub-type 0 from that of
a user-defined type is by viewing the metadata information for the table,
which cannot be done from within Delphi. The Local InterBase Server
utility WISQL can be used to view table metadata.
InterBase BLOB fields and Database Desktop
------------------------------------------
The Database Desktop utility that comes with Delphi (DBD) does not create
user-defined subtypes. When using BLOB fields created in Database Desktop,
use the "BLOB" field type for binary data, including bitmap data. This
creates a BLOB field of the predefined sub-type 0.
The DBD also offers a BLOB field type TEXT BLOB. This is equivalent to the
pre-defined subtype 1, and should be used where free-form text storage
will be needed. While it is functionally equivalent to the pre-defined
subtype 1 BLOB field, it will appear with a slightly different type des-
ignation if you view the metadata for the table in the WISQL utility.
Working with auto-increment field types
Question
Answer
In Delphi applications, the use of tables containing fields that
autoincrement, or change automatically in some manner unknown to
the application, can be problematic. Paradox, InterBase, Sybase
and Informix tables all provide means of inserting or updating
field values automatically, without intervention from the front-
end application. Not every table operation is affected by this
mechanism, however. So, this document will attempt to provide a
guideline for dealing with issues relating to the use of such
field types in Paradox 5.0, Informix 5.x, MS/Sybase SQL Server
4.x, InterBase 4.0 and Local InterBase tables.
For each table type, a different mechanism provides this
behind-the-scenes behavior. Paradox tables support an
Autoincrement field type. When new records are added to such
tables, the Borland Database Engine determines the highest
current value in that column, adds one, and updates the new row
with the new value.
For Informix tables, this behavior is provided by an
Informix-specific field type called Serial. Serial columns
differ from Paradox Autoincrement fields in that their values may
be changed, while Autoincrement columns are read-only.
InterBase and MS/Sybase SQL Server tables do not support a special
type for this kind of behavior, but may employ triggers to
accomplish the same task. Triggers are specialized procedures
that reside on the database server and automatically execute in
response to events such as table inserts, updates and deletes.
The use of tables with associated triggers can be particularly
problematic, since triggers are capable of doing much more than
just incrementing column values.
The three areas that are affected by these field types are simple
inserts, batchmoves, and table linking.
Handling Update and/or Append BatchMoves
-----------------------------------------------------------------
Paradox Tables
Since the Autoincrement field type is a read-only type,
attempting to perform a batchmove operation with such a column in
the destination table may cause an error. To circumvent this,
the TBatchMove components Mappings property must be set to match
source table fields to the target destination fields excluding
the destination table's Autoincrement field.
Informix Tables
Batch moving rows to Informix tables with Serial columns will not
cause an error in and of itself. However, caution should be used
since Serial columns are updateable and are often used as primary
keys.
InterBase Tables
MS/Sybase SQL Server Tables
Triggers on InterBase and SQL Server tables may catch any
improper changes made to the table, but this depends strictly
upon the checks placed in the trigger. Here again, caution
should be used since trigger-updated columns are often used as
primary keys.
Linking Tables via MasterSource & MasterFields
-----------------------------------------------------------------
Paradox Tables
Informix Tables
If the MasterFields and MasterSource properties are used to
create linked tables in a master-detail relationship and one of
the fields in the detail table is an Autoincrement or Serial
field, then the matching field in the master table must be a Long
Integer field or a Serial field. If the master table is not a
Paradox table then the master table's key field may be any integer
type it supports.
InterBase Tables
MS/Sybase SQL Server Tables
Linking with these tables types presents no particular problems
relating to trigger-modified fields. The only necessity is
matching the appropriate column type between the two tables.
Simple Inserts/Updates
-----------------------------------------------------------------
Paradox Tables
Since Paradox Autoincrement fields are read-only, they are not
typically targeted for update when inserting new records.
Therefore, the Required property for field components based on
Autoincrement fields should always be set to False. This can be
accomplished from within Delphi, using the Fields Editor to
define field components at design time by double clicking on the
TQuery or TTable component or at runtime with a statement similar
to the following.
Table1.Fields[0].Required := False;
or
Table1.FieldByName('Fieldname').Required := False;
Informix Tables
Although Informix Serial fields are updateable, if their
autoincrement feature is to be used, then the Required property
of field components based on them should be set to False. Do
this in the same manner described for Paradox Tables.
InterBase Tables
MS/Sybase SQL Server Tables
Handling inserts on these trigger-modified table types requires a
number of steps for smooth operation. These additional steps are
particularly necessary if inserts are accomplished via standard
data-aware controls, such as DBEdits and DBMemos.
Inserting rows on trigger-modified InterBase and SQL Server
tables may often yield the error message 'Record/Key Deleted'.
This error message appears despite that the table is properly
updated on the server. This will occur if:
1. The trigger updates the primary key. This is not only
likely when a trigger is used, but is probably the most
common reason for using a trigger.
2a. Other columns in the table have bound default values.
This is accomplished with the DEFAULT clause at table
creation in the case of InterBase. or with the
sp_bindefault stored procedure in SQL Server.
or
2b. Blob type fields are updated when a new row is inserted.
or
2b. Calculated fields are defined in an InterBase table.
The fundamental cause for this is that when the record (or
identifying key) is changed at the server, the BDE no longer has
means of specifically identifying the record for re-retrieval.
That is, the record no longer appears as it did when it was
posted, therefore the BDE assumes that the record has been
deleted (or the key changed).
Firstly, the field components of trigger-modified fields must
have their Required property set to False. Do this in the same
manner described for Paradox Tables.
Secondly, to avoid the spurious error, order the table by an
index that does not make use of fields updated by the trigger.
This will also prevent the newly entered record from disappearing
immediately after insertion.
Lastly, if requirement 1 above holds but neither 2a, 2b nor 2c
hold, then code similar to the following should be used for the
table component's AfterPost event handler.
procedure TForm1.Table1AfterPost(DataSet: TDataset);
begin
Table1.Refresh
end;
A Refresh of the table is necessary to re-retrieve the values
changed by the server.
If criteria 2a, 2b or 2c cannot be avoided, then the table should
be updated without using Delphi's data-aware controls. This can
be accomplished using a TQuery component targeted at the same
table. Once the query has posted the update, any table components
using the same table should be Refreshed.
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.
Showing deleted record in a DBF file
Question
How can I view dBASE records marked for deletion. That is,
I want to view those records marked as "soft deletion"?
Answer
In a dBASE table, records are not removed from the table
until the table is packed. Until that happens, records that
are "deleted" are actually just marked as "to be" deleted. To
show these existing but not displayed records, the following
function, ShowDeleted(), makes use of a BDE API function,
DbiSetProp(), to show records marked for deletion. It is not
necessary to close and re-open the table when using this
function. ShowDeleted() takes a TTable and a boolean variable
as parameters. The boolean parameter determines whether or not
to show deleted records.
Example code follows:
-----
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids,
DB, DBTables;
type
TForm1 = class(TForm)
Table1: TTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
CheckBox1: TCheckBox;
procedure CheckBox1Click(Sender: TObject);
public
procedure ShowDeleted(Table: TTable; ShowDeleted: Boolean);
end;
var
Form1: TForm1;
implementation
uses DBITYPES, DBIERRS, DBIPROCS;
{$R *.DFM}
procedure TForm1.ShowDeleted(Table: TTable; ShowDeleted: Boolean);
var
rslt: DBIResult;
szErrMsg: DBIMSG;
begin
Table.DisableControls;
try
Check(DbiSetProp(hDBIObj(Table.Handle), curSOFTDELETEON,
LongInt(ShowDeleted)));
finally
Table.EnableControls;
end;
Table.Refresh;
end;
procedure TForm1.CheckBox1Click(Sender: TObject);
begin
ShowDeleted(Table1, CheckBox1.Checked);
end;
end.
Referential Integrity
Question
What happens to me: I have four tables.The first is Master for the
second which is master for third and so on. Deletion is ok for the
second and third tables. The fourth table is deleted entirely !
Answer
TI2837:
NUMBER : 2837
PRODUCT : Delphi
VERSION : All
OS : Windows
DATE : December 13, 1995
TITLE : Cascading Deletes With Pdox Referential Integrity
Paradox tables offer a Referential Integrity feature. This feature pre-
vents adding records to a child table for which there is no matching
record in the parent table. It will also cause the key field(s) in the
child table to be changed when the corresponding key field(s) in the
parent are changed (commonly referred to as a cascading update). These
events occur automatically, requiring no intervention by a Delphi appli-
cation using these tables. However, the Paradox Referential Integrity
feature will not accommodate cascading deletes. That is, Delphi will not
allow you to delete a record in the parent table while matching records
exist in the child table. This would make "orphans" of the child records,
losing referential integrity. Delphi raises an exception when an attempt
is made to delete such a parent record.
To effect a cascading delete requires that the deletion of the matching
child records be deleted programmatically -- before the parent record is
deleted. In a Delphi application, this is done by interrupting the process
of deleting the record in the parent table, deleting the matching records
in the child table (if there are any), and then continuing with the dele-
tion of the parent record.
A record in a table is deleted by a call to the Delete method of the
TTable component, which deletes the current record in the associated
table. Interrupting the this process to first perform some other opera-
tions is a matter creating a procedure associated with the BeforeDelete
event of the TTable. Any commands in a BeforeDelete event procedure are
executed before the call actually goes out from the application to the
Borland Database Engine (BDE) to physically remove the record from the
table file.
To handle the deletion of one or more child records, in a BeforeDelete
event procedure the Delete method for the TTable representing the child
table is called in a loop. The loop is based on the condition of the
record pointer in the table not being positioned at the end of the data
set, as indicated by the Eof method of the TTable. This also accounts for
there being no child records at all matching the parent record to be
deleted: if there are no matching records, the record pointer will already
be at the end of the data set, the loop condition will evaluate to False,
and the Delete method in the loop nevers gets executed.
procedure TForm1.Table1BeforeDelete(DataSet: TDataset);
begin
with Table2 do begin
DisableControls;
First;
while not Eof do
Delete;
EnableControls;
end;
end;
In the above example, the parent table is represented by the TTable comp-
onent Table1 and the child by Table2. The DisableControls and Enable-
Controls methods are used as a cosmetic measure to freeze any data-aware
components that might be displaying data from Table2 while the records
are being deleted. These two methods make the process visually appear
smoother, but are only optional and not essential to this process. The
Next method need not be called within this loop. This is because the loop
begins at the first record and, as each record is deleted, the record that
previously followed the deleted record moves up in the data set, becoming
both the first and the current record.
This example presumes that the parent and child tables are linked with a
Master-Detail relationship, as is typical for tables for which such
Referntial Integrity is configured. Linking the tables in this manner
results in only those records in the child table that match the current
record in the parent table being available. All other records in the child
table are made unavailable through the Master-Detail filtering. If the
tables are not so linked, there are two additional considerations that
must be accounted for when deleting the child records. The first is that
a call to the First method may or may not put the record pointer on a
record that matches the current record in the parent table. This necessi-
tates using a search method to manually move the record pointer to a
matching record. The second consideration affects the condition for the
loop. Because records other than those matching the current record in the
parent table will be accessible, the condition for the loop must check
that each record is a matching record before attempting to delete it. This
checking is in addition to querying the Eof method. Because the records
will be ordered by this key field (from a primary or secondary index),
all of the matching records will be contiguous. This leads to the given
that, as soon as the first non-matching record is reached, it can be
assumed that all matching records have been deleted. Thus, the previous
example would be modified to:
procedure TForm1.Table1BeforeDelete(DataSet: TDataset);
begin
with Table2 do begin
DisableControls;
FindKey([Table1.Fields[0].AsString])
while (Fields[0].AsString = Table1.Fields[0].AsString) and
(not Eof) do
Delete;
EnableControls;
end;
end;
In the above, it is the first field in the parent table (Table1) upon
which the Referential Integrity is based, and the first field in the
child table (Table2) against which matching is judged.
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.
