Delphi, dbExpress And MySQL - Part 2

MySQL And dbExpress

Once you’ve installed MySQL 3.23.52, the first thing you need to do in Delphi is test your connection from dbExpress. The TSQLConnection component has a default MySQLConnection value for the Connection property. However, we must still change a number of properties before it works right. After you’ve selected MySQLConnection, the LibraryName gets set to dbexpmys.dll, which is the dbExpress MySQL driver for MySQL version 3.22.x. Since we’re using a higher version number 3.23.x we must explicitly change that property value from dbexpmys.dll to dbexpmysql.dll otherwise it won’t work (symptoms if you continue to use the ‘old’ DLL with version 3.23.x include the fact that you will not see table names, can only access the first 10 fields of records, and get frequent access violations).

Apart from the LibraryName, we must also take a look at the VendorLib which gets set to LIBMYSQL.dll. Unfortunately, after you install MySQL, this DLL is not added to the search path. It can be found in c:\MySQL\bin, and you can either add c:\MySQL\bin to the search path, or copy the MySQL.dll somewhere in the path, like the WinNT\System32 directory. A third alternative consists of using a hardcoded c:\mysql\bin\ LIBMYSQL.dll property value. Finally, you may want to edit the Connection properties to specify the correct database to connect to (see Figure 2).

» Figure 2: dbExpress MySQL Connection Properties.

When you want to make the connection, the correct username/ password combination is either empty (for the Test database), or just root as the username (for all other databases), unless you have already created some new users with one of the MySQL administration tools. You can use a TSQLTable, TSQLDataSet or TSQLQuery component connected to the TSQLConnection to open the customer table that we’ve just created using the MySQL monitor. Since this table is empty, you may want to write a little application to enter some records. Remember that dbExpress datasets are read-only unidirectional datasets (see Issue 69 for details), so you need to use four components instead of the usual two to be able to get your hands on the data. Assuming you already have an SQLConnection1 component that connects to the MySQL database, Table 1 defines the four components, with the properties and values that you have to set in order to open the customer table.

» Table 1

Component	Property	Value
TSQLTable	Name SQLConnection TableName	SQLTableCustomer SQLConnection1 customer
TDataSetProvider	Name DataSet	dspCustomer SQLTableCustomer
TClientDataSet	Name ProviderName	cdsCustomer dspCustomer
TDataSource	Name DataSet	dsCustomer cdsCustomer

The most important difference between using dbExpress and the BDE is that we need the DataSetProvider and ClientDataSet between our (unidirectional and read-only) dbExpress data access components and the DataSource and data-aware controls. This takes some time to get used to, but will quickly become second nature.

With a DBNavigator and a number of DBEdits (or a DBGrid) connected to the DataSource component, you can finish this data entry form. Note, however, that in order to save the changes back into the MySQL customer table we have to explicitly call ApplyUpdates. This can be done in the OnAfterPost or OnAfterDelete event handler of the cdsCustomer, or you can use an explicit OnClick event handler for a button (if you decide to use the latter, then make sure you also include a check for the cdsCustomer.ChangeCount in the OnClose event handler of your form, to prevent your users from accidentally closing the form without saving the contents of the customer table). See Listing 2 for some example code that I often use.

» Listing 2: Calling ApplyUpdates.

procedure TForm1.cdsCustomerAfterPostOrDelete(DataSet: TDataSet);
begin
(DataSet as TClientDataSet).ApplyUpdates(0)
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
if cdsCustomer.ChangeCount > 0 then
cdsCustomer.ApplyUpdates(0) // save without asking!
end;

Note that the fact that we have to explicitly call ApplyUpdates in order to save changes, also means that we can decide not to save changes right away, and instead offer the user the ability to undo local changes (that is, before they are applied back to the MySQL database table itself). Undo is actually a nice feature that was hard to implement using the Borland Database Engine, but is almost effortless when using the dbExpress components. There are different ways to implement this, for example using RevertRecord (operating on the current record), or the UndoLastChange method of the ClientDataSet component.

Of course, this means that you cannot use the automatic ApplyUpdates call in the OnAfterPost event handler, since that would clear the ‘undo’ buffer in the ClientDataSet again. See this month’s code for an undo code example.

dbExpress Master-Detail

If you want to create another table, for example an orders table with a CustNo, OrderNo, Name, and Price field, then we can execute the following SQL statement:

CREATE TABLE orders (
OrderNo INT(4) NOT NULL,
CustNo INT(4) NOT NULL,
Name VARCHAR(24),
Price DOUBLE(8,2)
PRIMARY KEY (OrderNo))

SQL statements like this, that do not return a dataset, can be passed as a string to the ExecuteDirect method of the SQLConnection component. So you can actually create your own tables like this directly, which illustrates just another way to use SQL to create tables in MySQL.

Once both tables are defined, we can use them to enter customer and orders data. However, this means we must define a master-detail relationship between the two MySQL tables. Using the BDE, a master-detail relationship was easy: just drop a DataSource, point it to the Master table, use it as the MasterSource of the Detail table, and finally use the Field Link Designer to give the MasterFields property a value. However, with dbExpress it’s not so straightforward. Specifically, there are no less than three different ways in which we can define that the orders are a detail from the master table.

ClientDataSet level

The easiest approach is to use two TSQLTable components that connect to the customer and orders tables, and feed their contents to a ClientDataSet component (using a TDataSetProvider in between). This means that the full contents of both MySQL tables are loaded into the two ClientDataSets (in memory) and can be used to define the master-detail relationship in memory. This is fast, but it consumes potentially large amounts of memory, since the entire master-detail relationship is maintained in memory. Table 2 lists the components and property values needed for this setup (assuming we already have a SQLConnection1 as well as the master TSQLDataSet available on the form or data module).

» Table 2

Component	Property	Value
TSQLTable	Name SQLConnection TableName	SQLTableOrders SQLConnection1 orders
TDataSetProvider	Name DataSet	dspOrders SQLTableOrders
TClientDataSet	Name ProviderName MasterSource MasterFields	cdsOrders dspOrders dsCustomer CustNo - CustNo
TDataSource	Name DataSet	dsOrders cdsOrders

As you can see, the master-detail relationship is defined at the ClientDataSet level here. Let’s now look at two alternatives.

dbExpress Level

A less memory intensive, but slower, solution relies on a dbExpress dataset to build the details (or, more specifically, to refresh the details). We can do this in two ways, using either a TSQLTable component that connects its MasterSource property to the ClientDataSet of the master, or by using a TSQLQuery component with a parameter that gets revolved using a value from the master ClientDataSet again (this time by pointing the DataSource property of the TSQLQuery to the master ClientDataSet).

Both techniques deliver the same results, but do rely on an explicit refresh statement in the OnDataChange event handler of the master DataSource (see Listing 3). Tables 3 and 4 list the components and property values needed for the two details defined at the dbExpress layer (assuming the master is already set up according to the previous table).

» Listing 3: DataSource.OnDataChange.

procedure
TForm1.dsCustomerDataChange(
Sender: TObject; Field: TField);
begin
if cdsOrdersByCustomer.Active
then
cdsOrdersByCustomer.Refresh;
if cdsQOrders.Active then
cdsQOrders.Refresh;
end;

» Table 3

Component	Property	Value
TSQLTable	Name SQLConnection TableName MasterSource MasterFields	SQLTableOrdersByCustomer SQLConnection1 orders dsCustomer CustNo - CustNo
TDataSetProvider	Name DataSet	dspOrdersByCustomer SQLTableOrdersByCustomer
TClientDataSet	Name ProviderName	cdsOrdersByCustomer dspOrdersByCustomer
TDataSource	Name DataSet	dsOrdersByCustomer cdsOrdersByCustomer

Table 3 is the solution using a TSQLTable component, and Table 4 is the solution using a TSQLQuery component with a parameter.

» Table 4

Component	Property	Value
TSQLQuery	Name SQLConnection SQL DataSource	SQLQueryOrders SQLConnection1 select * from orders where (orders.custno = :custno) dsCustomer
TDataSetProvider	Name DataSet	dspQOrders SQLQueryOrders
TClientDataSet	Name ProviderName	cdsQOrders dspQOrders
TDataSource	Name DataSet	dsQOrders cdsQOrders

In order to explicitly refresh the contents of the ClientDataSets (that receive their data through the DataSetProviders), we must respond to the OnDataChange of the DataSource connected to the master ClientDataSet. This will make sure that when we move through the data (which triggers the OnDataChange), we refresh the two detail ClientDataSets. Of course, in practice you only use one detail, and not all three, so in practice you only have to perform one explicit refresh.

Compared to the master-detail defined at the ClientDataSet level, this solution takes more time to execute (the explicit refresh), but also less memory, since only the relevant master and detail records are kept in memory. As always, it’s a memory versus speed trade-off, and you’ll have to decide for yourself the solution to use, depending on your needs and constraints.

All in all, there are a number of explicit differences between using the Borland Database Engine components and the dbExpress components: the additional use of a DataSetProvider and ClientDataSet, the need to call ApplyUpdates (as well as the benefit of offering undo capabilities), and finally the different ways in which you can define a master-detail relationship.

At the end of the day, however, dbExpress will turn out to be much faster and less memory intensive than the old Borland Database Engine. It will be easier to deploy, too. I’ll cover deployment in more detail next time, when I’ll show how to create and deploy web server applications using MySQL. Since it is available on a number of web servers, we may as well make use of that fact.

» Figure 3: Master and three Detail solutions at design-time.

Next Time

After this first encounter with MySQL, I want to move on to using MySQL in a web server environment. This not only means writing web server applications, but also deploying and using MySQL on a web server. All this and more next time, so stay tuned...

Bob Swart (aka Dr.Bob, www. drbob42.com) is an author, trainer, consultant and webmaster for Bob Swart Training & Consultancy (eBob42), who has spoken at Delphi and Borland Developer Conferences since 1993.