Borland's Delphi is one of the newest visual development tools for Microsoft Windows. It has positioned itself in the market as a high performance alternative to Microsoft Visual Basic. What separates Delphi from its competition in this regard is
its ability to compile stand-alone executables and .DLLs, with no run-time libraries required.
Delphi provides full support of ODBC and numerous object classes for communicating with ODBC data sources. It ships with a single-user desktop version of Borland's InterBase Server and its ODBC-enabled ReportSmith querying and reporting tool. The
client/server edition also ships native IDAPI drivers for Oracle, Sybase, and Informix, among others.
Object Pascal, the programming language used in Delphi development, supports many of the object-oriented features typically associated with C++, including public and private data members and functions, inheritance, and polymorphism. The language
also supports a wide range of data types and advanced features, including true pointer types and structured exception handling.
While Object Pascal and Delphi provide an abundance of noteworthy features, this appendix focuses primarily on Delphi's support for database applications. An overview of the more significant features of the language is included, as well as a brief
discussion of the strengths and weaknesses of Delphi.
To make a data source available to Delphi, IDAPI.CFG must be edited using the BDE configuration utility. This utility can be accessed from the Tools menu in the IDE. When the utility is started, it displays currently configured data sources, some of
which may be pre-configured IDAPI interfaces, depending on the options selected when Delphi was installed. The main window of the BDE configuration utility should appear as shown in Figure D.1.
Figure D.1. The main window of the Delphi BDE configuration utility.
Delphi can interface with any ODBC data source configured and defined in ODBC.INI. To configure an existing ODBC data source for Delphi's use, click on the New ODBC Driver command button. A simple dialog box displays and requires three basic items. The
SQL Link Driver name is simply an identifier to be used by Delphi, and any unique name can be used. Using the Default ODBC Driver drop-down, select from the list of currently installed drivers. If the required driver does not appear in the list, it has not
been correctly installed. ODBC drivers can be installed using ODBCADMN.EXE, a Microsoft utility that is typically provided by driver vendors. After selecting a driver, the Default Data Source Name drop-down will be populated with a list of the data sources
defined for the selected driver. Figure D.2 provides an example of how this dialog box might look when configuring the Oracle7 ODBC driver.
Figure D.2. The Add ODBC Driver dialog box can be used to set up the Oracle7 driver for use in Delphi.
After selecting the default data source, click on the OK button to add the driver. The newly added driver should now appear in the main window of the configuration utility, as shown in Figure D.3.
Figure D.3. Parameters can be set from the main window of the configuration utility.
Of the available parameters, SQLQRYMODE and SQLPASSTHRU MODE are the most significant. Setting SQLQUERYMODE to SERVER causes Delphi to deliver all queries to the database server for processing. This prevents the local BDE from attempting to process the
query from the desktop. The SQLPASSTHRU mode should be set to SHARED NOAUTOCOMMIT. This setting prevents transactions sent to the server from being committed automatically. The tabs across the bottom of the BDE configuration utility's main window allow the
setting of additional parameters, including date, time, and numeric formats. These settings apply to the local database engine and should not need to be modified for ODBC connections.
The BDE configuration utility supports multiple configuration files. The changes can be saved in a new file, and the utility will optionally update WIN.INI to use the new file as the default. When adding a new data source, you may want to save the
configuration file with a different name so that the old file can be used as a backup, in case other data sources were changed inadvertently. After the configuration file is saved, Delphi can access the new data source.
Delphi supplies several classes that can be used to establish and maintain connections to the database, process transactions, and retrieve result sets. The most useful of these classes include:
All of these objects are available on the Data Access tab of the toolbar.
Communication with the database is initiated by the TDatabase component. Before establishing a connection, the DriverName and DatabaseName properties must be set. These should correspond to the driver name (specified when the data source was set up
through BDE Configuration) and a valid data source name for the specific driver. The application connects to the database by using the Open method, or by setting the Connected property to True.
While all objects' properties and methods can be accessed through the object inspector, some objects and properties have special dialog boxes. For example, double clicking on the TDatabase component displays a
dialog box that makes it easier to view and modify its properties.
If the Params property does not have all required parameters set, the TDatabase object displays a login form automatically, provided that the LoginPrompt is set to True. Connection parameters are set using the Params property, which is actually a
TStrings object. Parameters are set using the Add method, as demonstrated in the following code fragment:
dbOra.Params.Add('SERVER NAME=ORACLE');
dbOra.Params.Add('USER NAME=scotty');
dbOra.Params.Add('PASSWORD=tiger');
dbOra.Connected := True;
The KeepConnected property, when set to True, keeps the connection open even when no result sets are open and no transactions are in progress. The TransIsolation property determines how records are read when they are currently involved in a transaction.
Oracle supports tiReadCommitted, which allows only committed changes to be read, and tiRepeatableRead in read-only mode. The latter mode prevents Oracle from attempting to refresh the Snapshot for records that have already been read.
The TDatabase object is also used for transaction control, (assuming that SQLPASSTHRU MODE was set to SHARED NOAUTOCOMMIT when the data source was configured.) The StartTransaction, Commit, and Rollback methods should be used whenever SQL transactions
are applied. The exception to this rule is when the application calls a stored procedure which handles commits and rollbacks internally.
In some cases, the behavior of the TDatabase object can be overridden by the TSession object. The TSessions object is particularly useful for applications that connect to more than one database or need more than one connection for asynchronous
processing. The KeepConnections property of the TSessions object will override the KeepSessions property of individual TDatabase objects. The TSession object is always global and has additional methods for querying the BDE configuration files. It stores an
array of connected TDatabase objects in its Databases property, and the number of connected TDatabase objects in its DatabaseCount property.
The TQuery object is the primary means for communicating with the database. It is used not only to retrieve results, but also to apply SQL transactions. The SQL property is a TStrings object used to store the SQL to be executed. An application can use a
TQuery to build simple dynamic SQL very easily, using the Params property. Application variables can be substituted for literal values wherever they might appear. For example, the following statements can be used to generate a result set dynamically based
on a value entered by a user in a TEdit object:
qryGetCusts.SQL.Add('SELECT * FROM CUSTOMERS WHERE LAST_NAME LIKE :LastName');
qryGetCusts.Params[0].AsString(txtLastName.Text);
This is particularly useful for data entry forms where records will be inserted frequently into the same table or view. The Params property is an array of TParams objects. They are referenced by subscripts and set using the AsString, AsInteger, AsFloat,
or one of the other properties that store a value based on a data type.
When SQL is parameterized, it should be prepared only once. It can then be executed repeatedly with different parameters. The code fragment in Listing D.1 illustrates the use of the Prepare and ExecSQL Methods, as well as transaction control provided by
the TDatabase object.
{this code is executed once}
qryAddCust.SQL.Add('INSERT INTO CUSTOMERS (LAST_NAME, FIRST_NAME) ');
qryAddCust.SQL.Add('VALUES (:Last, :First)');
qryAddCust.Prepare;
{this code can be executed many times}
dbOracle.StartTransaction;
qryAddCust.Params[0].AsString(txtLast.Text);
qryAddCust.Params[1].AsString(txtFirst.Text);
try
qryAddCust.ExecSQL;
dbOracle.Commit;
except
dbOracle.Rollback;
end;
The ExecSQL method should be used only for statements which do not return a result set. For SELECT statements, the Open method should be used. After a result set has been returned from the database, the TQuery object will have an array of TFields
objects, which can be accessed in much the same way as TParam objects. Listing D.2 demonstrates the methods used to retrieve result sets from a TQuery object.
The right mouse button has special uses for many objects. Right-clicking on a TQuery object displays a pop-up menu that you can use to access two dialog boxes that are specific to designing a TQuery. You can use
the Fields Editor and Define Parameters dialog boxes to build queries visually.
qryGetCusts.Open;
while (qryGetCusts.EOF = False) do
begin
lstFullName.Add(Concat(qryGetCusts.Fields[0].AsString, + ','
+ qryGetCusts.Fields[0].AsString));
qryGetCusts.MoveBy(1);
end;
qryGetCusts.Close;
qryGetCusts.SQL.Clear;
Several important properties and methods that apply to the TQuery are illustrated by Listing D.2. The EOF and BOF properties are Boolean values, set to true when the record pointer is positioned at end-of-file or at beginning-of-file, respectively. The
properties AsString, AsInteger, and so on apply to the TFields objects in much the same way as they are applied to TParam objects, only they are typically used to read, rather than set values. MoveBy can be used to move the record pointer any number of
records in the current direction, (which is forward by default). First, Last, Prior, and Next can also be used to position the record pointer. The Close method should always be called when the result set is no longer needed, and the Clear method of the SQL
property should be used to clean up when an application sets this property dynamically at run time.
There are several other important TQuery properties that are not demonstrated by the code example in Listing D.2. The RequestLive property can be used to create a cursor that is updatable and refreshes automatically. However, there are numerous
restrictions that can prevent this property from being used. In most cases, this should be set to False even when it is supported because it is likely to increase network traffic and database contention. The UpdateMode property is applicable only to live
result sets and is used to set the requirements for matching records on updates. When an application does not need scrollable cursors, the Unidirectional property should be set to True. This improves performance, particularly when scrollable cursors are
not supported by the driver. In these cases, Delphi attempts to emulate a scrollable cursor, potentially causing it to read the entire result set before returning from the Open method.
One property that should not be overlooked is the Database property. This value must correspond to the Database property of a connected TDatabase object. The Database property should not be confused with the DataSource property, which is used to locate
unbound parameters at run-time through a TDataSource object, which can be very useful in creating relationships between queries.
The TDataSource component is used to bind data-aware controls to result sets. Delphi includes a full complement of data-aware controls, including a grid, radio buttons, check boxes, lists, and drop-down combos. The DataSet property of the TDataSource
object is used to specify the query or table that it will use to bind results to controls. The AutoEdit property is applicable only to live results sets. When set to True, this property forces the underlying result set into Edit mode when a bound control
is modified. When communicating with remote database servers, using the AutoEdit property for updating records is generally unacceptable, because transaction control is lost.
Bound controls are most useful in displaying read-only result sets or when used in conjunction with a separate query to apply an update to an existing record. A record can be retrieved and displayed with minimal code using bound controls, but in order
to use the transaction methods of the TDatabase component, code must be written to apply any updates using a separate query.
A TDataSource component does not have its own set of TField objects—it simply accesses an existing result set to supply information to bound controls. Controls are bound to a TDataSource through the DataSource and DataField properties. The
DataSource must correspond to an existing TDataSource object's name, and the DataField property should correspond to a column in the underlying query or table. If these properties are set correctly, bound controls are populated automatically when the
TQuery or Ttable is opened and the TDataSource is enabled.
The simplest way to implement this technique requires no code at all. A TTable object can be placed on the form with its Database and TableName properties set and its Active property set to True. Next, a TDataSet object is placed on the form, and its
DataSet property is set to the name of the TTable object. Finally, a data-aware TDBGrid is placed on the form, and its DataSource property is set to the name of the TDataSource object. At run-time, as soon as the application connects to the database, the
table is read, and the grid is populated. This simplicity is one of the great attractions to using bound controls.
However, the use of bound controls is a questionable technique, particularly in MDI applications. In order for bound controls to display data, the query or table object must remain open; this prevents the object from being used to apply other
transactions or queries. If you have several MDI windows concurrently open with bound controls, using multiple TQuery or TTable objects can become very expensive in terms of workstation, network, and server resources. Although it requires more handwritten
code, using a query to populate controls and closing the query immediately is a safer and more efficient approach.
A better use of the TDataSource object involves the creation of dynamic SQL. In addition to accessing data from TQuery and TTable objects, the TDataSource object can be used to provide parameters to TQueries at run-time. The DataSource property of the
TQuery object is used to specify the name of a TDataSource to check when attempting to resolve unbound parameters. If the name of an unbound TQuery parameter matches a column name in a TDataSource data set, the value of the matching column at the current
record position will be used as the parameter. For example, an accounts receivable system may need to display summary records of outstanding accounts and also provide transaction details as requested. This can be accomplished using two TQueries and a
TDataSource that use information supplied at run-time. One TData query might provide the summary level information in a read-only data-aware grid, including the account number. Right-clicking on a particular account might be used as the mechanism to
display a pop-up with the account details for the selected account. The SQL for the TQuery object used to retrieve the detail information might be defined at design-time as:
SELECT DATE, DEBIT_AMT, CREDIT_AMT FROM TRANSACTIONS WHERE ACCT_NO = :acct_no ORDER BY DATE
A TDataSource object can be used to supply the parameter value to this query. The DataSet property of the TDataSource object should be set to the summary-level TQuery, which uses the SQL below:
SELECT ACCT_NO, ACCT_NAME, BALANCE FROM ACCTS_REC ORDER BY ACCTS_REC
The TQuery object should then set its DataSource property equal to the name of the TDataSource object. When the detail TQuery is opened, it will receive the value of ACCT_NO for the current summary record as the parameter to its SQL with no intervention
at run-time. The TDataSource object is particularly useful in creating this kind of master-detail relationship.
Delphi also provides support for stored procedures through its implementation of the TStoredProc component. As with the other descendants of TResultSet, the TStoredProc object requires the name of a connected TDatabase object in its Database property.
The name of the stored procedure in the database should be selected from the drop-down list provided for the StoredProcName property. The ParamBindMode is used to indicate how parameters will be bound at run-time, by name or by index. The Active property
should be set to False unless the stored procedure will return a result set. If this is the case, setting the Active property to True will cause the stored procedure to fire.
The TStoredProc component is ideal for applying parametered transactions on data entry forms. Stored procedures are executed in much the same way as SQL transactions applied through TQuery objects. Listing D.3 provides an example of using a stored
procedure to insert records from a simple data entry form.
{This code should only be executed once,
perhaps in the form's constructor }
spInsIndiv.Prepare;
{This code will be executed for each transaction}
spInsIndiv.ParamByName('Last').AsString := txtLast.Text;
spInsIndiv.ParamByName('First').AsString := txtFirst.Text;
spInsIndiv.ParamByName('DOB').AsString := txtDOB.Text;
spInsIndiv.ParamByName('Notes').AsString := txtNotes.Text;
try
spInsIndiv.ExecProc;
Application.MessageBox('A new record was added.',
'Oracle Unleashed', mb_OK)
except
Application.MessageBox('Unable to add the current record.',
'Oracle Unleashed', mb_OK);
end;
Note that the method for executing a stored procedure, ExecProc, is different than the method used to execute a query. As with dynamic SQL queries, the procedure needs to be prepared before parameters are bound, but only once. Assigning values to
parameters using the ParamByName method works in much the same way as accessing the array of TParams by index. Using ParamByName is probably a better choice because it results in more readable code.
The parameters to stored procedures can be viewed at design-time by right-clicking the TStoredProc object and selecting Define Parameters from the pop-up menu.
When calling stored procedures with output parameters, you must allocate sufficient space for variables that will be bound to the output parameters. This is a potential problem when using string variables. For example, the following lines of code will
result in a general protection fault:
var
PChar: szOut;
begin
spGetNotes.Prepare;
spGetNotes.ParamByName('NotesOut').AsString := szOut;
The next code fragment should work, (assuming that the length of the string returned from the database will never exceed 255 bytes):
var
String: szOut[255];
begin
spGetNotes.Prepare;
spGetNotes.ParamByName('NotesOut').AsString := szOut;
When using stored procedures, explicit transaction control may not be necessary. In most cases, any commits or rollbacks should be handled within the stored procedure. This is the safest way to process transactions, and it simplifies the code that
applies transactions from the client side.
The methods of communicating with the database described in this section are a small subset of all the options provided by Delphi. Additional database components include the TBatchMove object and the TReport object, which allows ReportSmith reports to
be integrated with Delphi applications.
As mentioned in the introduction to this appendix, the object-oriented features, support for pointers and complex data types, and language structure provided by Object Pascal are among Delphi's greatest strengths.
In addition to the standard data types, Object Pascal allows the creation of any type of pointer, including pointers to user-defined classes and structures, (records, in Pascal terminology). Pointers are allowed within complex data types and class
definitions, as well.
Class declarations in Object Pascal are very similar to C++ declarations. The ability to declare member data elements and private functions improves encapsulation and allows for information hiding. Also in the C++ tradition, Delphi's model of
inheritance allows base class functions to be overridden by descendants, allowing polymorphic behavior in user-defined classes. Unfortunately, support for overloaded member functions seems to be lacking.
Another strong feature of Object Pascal is its support for structured exception handling. Listings D.1 and D.3 provide examples of the try..except blocks used to handle exceptions in Delphi. The finally keyword can be used to create a resource
protection block that frees memory without handling the exception. The code listings in D.1 and D.3 do not actually handle the exceptions, either. Delphi provides a fairly comprehensive list of pre-defined exceptions that can be handled using the syntax:
on exception do statement
An exception instance is never destroyed until it is handled. User-defined exceptions can also be declared and thrown.
Delphi's weaknesses are few, considering that it is a first version of a fairly complicated development tool. Aside from the occasional UAE and some bugs in the security features of the local database server, Delphi seems to be a stable and solid
product. The database objects, while easy to use, do seem to be geared more to desktop applications than client/server development, with heavy emphasis on bound controls. However, SQL Traces will show that it makes very efficient use of the ODBC API in
certain situations. This is because it maps to the API more cleanly than most Windows development tool's ODBC layers, which typically don't provide methods for using prepared execution. The close relationship to ODBC is somewhat surprising considering that
IDAPI drivers are shipped with the product. For developers that are already familiar with the ODBC API, this should make a switch to IDAPI nearly transparent.
Overall, Delphi goes a long way toward living up to its billing as "the RADical performance tool". The IDE is fairly intuitive, the object hierarchy well-designed, and the Object Pascal programming language is arguably the best among the desktop development tools. The easy-to-configure ODBC connectivity and support for stored procedures make it a good choice for developing client applications to interface with Oracle.