The VCL components used to interface the dbExpress library encompass a group of dataset components plus a few ancillary ones. To differentiate these components from other database-access families, the components are prefixed with the letters SQL, underlining the fact that they are used for accessing RDBMS servers.
These components include a database connection component, a few dataset components (a generic one; three specific versions for tables, queries, and stored procedures; and one encapsulating a ClientDataSet component), and a monitor utility.
The TSQLConnection class inherits from the TCustomConnection component. It handles database connections, the same as its sibling classes (the Database, ADOConnection, and IBConnection components).
Unlike other component families, in dbExpress the connection is compulsory. In the dataset components, you cannot specify directly which database to use, but can only refer to a SQLConnection.
The connection component uses the information available in the drivers.ini and connections.ini files, which are dbExpress's only two configuration files (these files are saved by default under Common Files\Borland Shared\DBExpress). The drivers.ini file lists the available dbExpress drivers, one for each supported database. For each driver there is a set of default connection parameters. For example, the InterBase section reads as follows:
[Interbase] GetDriverFunc=getSQLDriverINTERBASE LibraryName=dbexpint.dll VendorLib=GDS32.DLL BlobSize=-1 CommitRetain=False Database=database.gdb Password=masterkey RoleName=RoleName ServerCharSet=ASCII SQLDialect=1 Interbase TransIsolation=ReadCommited User_Name=sysdba WaitOnLocks=True
The parameters indicate the dbExpress driver DLL (the LibraryName value), the entry function to use (GetDriverFunc), the vendor client library, and other specific parameters that depend on the database. If you read the entire drivers.ini file, you'll see that the parameters are really database-specific. Some of these parameters don't make a lot of sense at the driver level (such as the database to connect to), but the list includes all the available parameters, regardless of their usage.
The connections.ini file provides the database-specific description. This list associates settings with a name, and you can enter multiple connection details for every database driver. The connection describes the physical database you want to connect to. As an example, this is the portion for the default IBLocal definition:
[IBLocal] BlobSize=-1 CommitRetain=False Database=C:\Program Files\Common Files\Borland Shared\Data\employee.gdb DriverName=Interbase Password=masterkey RoleName=RoleName ServerCharSet=ASCII SQLDialect=1 Interbase TransIsolation=ReadCommited User_Name=sysdba WaitOnLocks=True
As you can see by comparing the two listings, this is a subset of the driver's parameters. When you create a new connection, the system will copy the default parameters from the driver; you can then edit them for the specific connection—for example, providing a proper database name. Each connection relates to the driver for its key attributes, as indicated by the DriverName property. Notice also that the database referenced here is the result of my editing, corresponding to the settings I'll use in most examples.
It's important to remember that these initialization files are used only at design time. When you select a driver or a connection at design time, the values of these files are copied to corresponding properties of the SQLConnection component, as in this example:
object SQLConnection1: TSQLConnection ConnectionName = 'IBLocal' DriverName = 'Interbase' GetDriverFunc = 'getSQLDriverINTERBASE' LibraryName = 'dbexpint.dll' LoginPrompt = False Params.Strings = ( 'BlobSize=-1' 'CommitRetain=False' 'Database=C:\Program Files\Common Files\Borland Shared\Data\employee.gdb' 'DriverName=Interbase' 'Password=masterkey' 'RoleName=RoleName' 'ServerCharSet=ASCII' 'SQLDialect=1' 'Interbase TransIsolation=ReadCommited' 'User_Name=sysdba' 'WaitOnLocks=True') VendorLib = 'GDS32.DLL' end
At run time, your program will rely on the properties to have all the required information, so you don't need to deploy the two configuration files along with your programs. In theory, the files will be required if you want to change the DriverName or ConnectionName properties at run time. However, if you want to connect your program to a new database, you can set the relevant properties directly.
When you add a new SQLConnection component to an application, you can proceed in different ways. You can set up a driver using the list of values available for the DriverName property and then select a predefined connection by selecting one of the values available in the ConnectionName property. This second list is filtered according to the driver you've already selected. As an alternative, you can begin by selecting the ConnectionName property directly; in this case it includes the entire list.
Instead of hooking up an existing connection, you can define a new one (or see the details of the existing connections) by double-clicking the SQLConnection component and launching the dbExpress Connection Editor (see Figure 14.4). This editor lists on the left all the predefined connections (for a specific driver or all of them) and allows you to edit the connection properties using the grid on the right. You can use the toolbar buttons to add, delete, rename, and test connections, and to open the read-only dbExpress Drivers Settings window (also shown in Figure 14.4).
In addition to letting you edit the predefined connection settings, the dbExpress Connection Editor allows you to select a connection for the SQLConnection component by clicking the OK button. Note that if you change any settings, the data is immediately written to the configuration files—clicking the Cancel button doesn't undo your editing!
To define access to a database, editing the connection properties is certainly the suggested approach. This way, when you need to access the same database from another application or another connection within the same application, all you need to do is select the connection. However, because this operation copies the connection data, updating the connection doesn't automatically refresh the values within other SQLConnection components referring to the same named connection: You must reselect the connection to which these other components refer.
What really matters for the SQLConnection component is the value of its properties. Driver and vendor libraries are listed in properties you can freely change at design time (although you'll rarely want to do this), whereas the database and other database-specific connection settings are specified in the Params properties. This is a string list including information such as the database name, the username and password, and so on. In practice, you could set up a SQLConnection component by setting up the driver and then assigning the database name directly in the Params property, forgetting about the predefined connection. I'm not suggesting this as the best option, but it is certainly a possibility; the predefined connections are handy, but when the data changes, you still have to manually refresh every SQLConnection component.
To be complete, I have to mention that there is an alternative. You can set the LoadParamsOnConnect property to indicate that you want to refresh the component parameters from the initialization files every time you open the connection. In this case, a change in the predefined connections will be reloaded when you open the connection, at either design time or run time. At design time, this technique is handy (it has the same effect as reselecting the connection); but using it at run time means you'll also have to deploy the connections.ini file, which can be a good idea or inconvenient, depending on your deployment environment.
The only property of the SQLConnection component that is not related to the driver and database settings is LoginPrompt. Setting it to False allows you to provide a password among the component settings and skip the login request dialog box, both at design time and at run time. Although this is handy for development, it can reduce the security of your system. Of course, you should also use this option for unattended connections, such as on a web server.
The dbExpress component's family provides four different dataset components: a generic dataset, a table, a query, and a stored procedure. The latter three components are provided for compatibility with the equivalent BDE components and have similarly named properties. If you don't have to port existing code, you should generally use the general SQLDataSet component, which lets you execute a query and also access a table or a stored procedure.
The first important thing to notice is that all these datasets inherit from a new special base class, TCustomSQLDataSet. This class and its derived classes represent unidirectional datasets, with the key features I've already described. In practice, this means that the browse operations are limited to calling First and Next; Prior, Last, Locate, the use of bookmarks, and all other navigational features are disabled.
Technically, some of the moving operations call the CheckBiDirectional internal function and eventually raise an exception. CheckBiDirectional refers to the public IsUnidirectional property of the TDataSet class, which you can eventually use in your own code to disable operations that are illegal on unidirectional datasets.
In addition to having limited navigational capabilities, these datasets have no editing support, so a lot of methods and events common to other datasets are not available. For example, there is no AfterEdit or BeforePost event.
As I mentioned earlier, of the four dataset components for dbExpress, the fundamental one is TSQLDataSet, which can be used both to retrieve a dataset and to execute a command. The two alternatives are activated by calling the Open method (or setting the Active property to True) and by calling the ExecSQL method.
The SQLDataSet component can retrieve an entire table, or it can use a SQL query or a stored procedure to read a dataset or issue a command. The CommandType property determines one of the three access modes. The possible values are ctQuery, ctStoredProc, and ctTable, which determine the value of the CommandText property (and also the behavior of the related property editor in the Object Inspector). For a table or stored procedure, the CommandText property indicates the name of the related database element, and the editor provides a drop-down list containing the possible values. For a query, the CommandText property stores the text of the SQL command, and the editor provides a little help in building the SQL query (in case it is a SELECT statement). You can see the editor in Figure 14.5.
When you use a table, the component will generate a SQL query for you, because dbExpress targets only SQL databases. The generated query will include all the fields of the table, and if you specify the SortFieldNames property, it will include a sort by directive.
The three specific dataset components offer similar behavior, but you specify the SQL query in the SQL string list property, the stored procedure in the StoredProcName property, and the table name in the TableName property (as in the three corresponding BDE components).
The SimpleDataSet component is new in Delphi 7. It is a combination of four existing components: SQLConnection, SQLDataSet, DataSetProvider, and ClientDataSet. The component is meant to be a helper—you need only one component instead of four (which must also be connected). The component is basically a client dataset with two compound components (the two dbExpress ones), plus a hidden provider. (The fact that the provider is hidden is odd, because it is created as a compound component.)
The component allows you to modify the properties and events of the compound components (besides the provider) and replace the internal connection with an external one, so that multiple datasets share the same database connection. In addition to this issue, the component has other limitations, including difficulty manipulating the dataset fields of the data access dataset (which is important for setting key fields and can affect the way updates are generated) and unavailability of some provider events. So, other than for simple applications, I don't recommend using the SimpleDataSet component.
Delphi 6 shipped with an even simpler and more limited component called SQLClientDataSet. Similar components were available for the BDE and IBX data access technologies. Now Borland has indicated that all these components are obsolete. However, the directory Demos\Db\SQLClientDataset contains a copy of the original component, and you can install it in Delphi 7 for compatibility purposes. But just as the SimpleDataSet component is somewhat limited, I found the SQLClientDataSet component totally unusable.
The final component in the dbExpress group is SQLMonitor, which is used to log requests sent from dbExpress to the database server. This monitor lets you see the commands sent to the database and the low-level responses you receive, monitoring the client/server traffic at a low level.