Under Construction:
Delphi, dbExpress And MySQL

by Bob Swart

The Borland Database Engine (BDE) has long been the number one choice for quick-and-dirty (and easy, although not always easy to install) data access, based on the dBASE and Paradox table formats. But now the BDE is officially frozen and SQL Links is even deprecated. In other words, there will be no further development of and enhancements added to the BDE, so we should be seriously looking at alternatives for data access in Delphi.

This may sound easier than it actually is. The BDE has never been the only choice in Delphi. Quite apart from all the third-party database libraries, Delphi itself comes with a number of alternatives, which doesn't make the choice any easier. We have the following tabs for data access on the Component Palette: dbExpress, BDE, ADO and InterBase.

Of these, dbExpress is the one most promoted by Borland these days, not least because it provides cross-platform support for Delphi and C++Builder on Windows, and Kylix on Linux. And even if this is of no concern to you, then you should appreciate the fact that dbExpress has been rewritten from the ground up to be a much faster data access library (read: less overhead) than the BDE.

dbExpress

Zooming in on dbExpress, you quickly realise that decision time isn't over, yet. My copy of Delphi 7 Enterprise comes with dbExpress drivers for (in alphabetical order) DB2, Informix, InterBase, MS SQL Server, MySQL and Oracle. When it comes to a free solution (without any additional cost or licence fees) only MySQL and FireBird (the Open Source InterBase-compatible database) are available in this list, and for that reason I've decided to focus this month on the combination of dbExpress and MySQL  as a serious option for replacing the BDE. This article describes my experiences, as well as a number of helpful hints, tips and work-arounds for issues that could otherwise result in some frustration.

MySQL

For a free database, it's amazing how well-known the name of MySQL has become in just a few years. The fact that it's included in the small list of dbExpress drivers (next to commercial powerhouses from Oracle, IBM and Microsoft) available in the box with Delphi itself also speaks volumes. So what is MySQL, and why should we want to take a closer look at it in the first place?

Well, first of all, MySQL is free under the GPL model, although you can also purchase a commercial licence. Whether you need to buy a licence seems to come down to whether you distribute the MySQL server itself with your software, rather than whether your software itself is commercial. But licences are cheap: £130 for a single server for MySQL Classic.

It's available on both Windows and Linux, and is available as the first choice on a large number of web servers. In contrast, try finding an ISP that offers InterBase or FireBird and you may be looking for a while. Apart from that, MySQL offers a lot as a relational DBMS, including a very good reputation when it comes to the speed of reading records (other operations are fast as well, but MySQL seems to shine especially when reading data), and support for SQL, client/server development, and even transactions.

MySQL 3.23.52

MySQL comes in different versions. From the MySQL website at www.MySQL.org you can download production version 3.23.52 as well as a development `beta' version (currently at 4.0.4). The danger of using a development version is twofold: first of all it can contain bugs, and second it may or may not be supported by your version of Delphi (there is a public beta version of the MySQL 4 dbExpress driver, but it won't get official status as long as MySQL 4 itself remains in beta, of course).

Both issues are less likely to arise if you're using a production version of MySQL, which has been in use longer, most bugs have probably been found and fixed, or at least reported, and Delphi will probably be able to connect to it. So, from now on, I will limit our MySQL experience to version 3.23.52, which will have enough surprises in store anyway.

The mysql-3.23.52-win.zip file that you can download is only 13,145,665 bytes big, and the contents were last updated on 15 August 2002 (which is a few days after Delphi 7 was released). You can unzip the file to an install directory and run setup to actually install MySQL (see Figure 1). Note that it's recommended to install it in c:\MySQL, otherwise you must perform some manual configuration steps afterwards to ensure that it can find everything.

» Figure 1: Installing Components and Documentation.

Even a full install takes less than 28Mb, which is a welcome change compared to tools that can take up to half a Gigabyte these days (IBM's DB2 comes to mind). The c:\MySQL\Docs directory contains a manual in HTML and plain text format, which unfortunately isn't too well organised. If you want to learn how to start MySQL, connect to a database, create a new table, and so on, then you have to search for a while. Furthermore, most of the manual seems to be aimed at Linux users, so keep that in mind when working with it.

MySQL Management

The c:\MySQL\bin directory contains a number of executables and the libmySQL.dll that we will need to use with Delphi in a moment. To start the MySQL monitor, you can run mysql.exe which will give you a command-line interface to MySQL. This is a low-level way of using databases, creating tables, granting access, and so on, which isn't hard to use.

There is also a Windows application called MySqlManager that offers you a more visual interface to the databases, tables and field definitions (but not much). And finally, a tool called WinMySqladmin 1.4 is installed as a tray icon. I was not at all impressed by these last two Windows tools, and have done most of my MySQL settings with the MySQL monitor console. For serious MySQL management you should consider a more professional tool, such as DBTools Manager 1.0.15 from DBTools Software (www.dbtools.com.br). With this Windows tool you can manage your databases, tables, users, permissions as well as user defined functions in your MySQL databases.

The c:\mysql\Data directory contains the different subdirectories for the databases (by default consisting of mysql and test). You can add your own new databases here, for example called TDM. Using the MySQL monitor, you can enter the commands in Listing 1  to use the TDM database, and create a table called customer with three fields: CustNo (the primary key), Name, and Company.

» Listing 1

mysql> use TDM
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE customer
    ->  (CustNo INT(4) NOT NULL,
    ->   Name VARCHAR(24), Company VARCHAR(42),
    ->     PRIMARY KEY (CustNo));
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye

The result of this little session is a table called customer in the TDM database. Of course, using the DBTools Manager you can customise this table even further. Apart from that, we'll now see how we can talk to MySQL using Delphi and dbExpress.

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.

dbExpress: Delphi 6 Versus Delphi 7 When it comes to dbExpress, I have to agree with our very own Mr One Last Compile that it may sometimes be a good idea to skip a version of Delphi. Or let me slightly rephrase that: when it comes to dbExpress, you will get best results if you have only one version of the latest release of Delphi on your machine (either Delphi 6 or Delphi 7), but not both. Since I have both Delphi 6.02 and 7.01 on my machine, I encounter the following problem: the dbExpress drivers from Delphi 7 are not always downwards compatible with those of Delphi 6 (there have been issues with the InterBase driver), and those of Delphi 6 are certainly not upwards compatible with those of Delphi 7. While this doesn't sound like a big issue, I will describe a few situations that have bitten me already. On a machine with both Delphi 6 and 7, I can build a dbExpress application at design-time without problems, but when I hit F9 to run it from the Delphi 7 IDE, I suddenly get a dbExpress Error message Operation Not Supported (see Figure 4). » Figure 4: dbExpress Error. At design-time it works fine, showing all the data, and even at runtime (outside the IDE) everything is fine, but when started from the Delphi 7 IDE the project raises an exception the moment the SQLConnection component is activated. It actually took me a few hours to figure out that the problem was caused by the fact that my Delphi 7 application was looking for a dbExpress DLL and found it... in the Delphi6\bin directory (and not in the Delphi7\bin directory, since Delphi6\bin came first in the PATH). And it gets worse. Some people, you know who you are, have been installing the dbExpress DLLs (like dbexpmys.dll and dbexpmysql.dll for MySQL) in the WinNT\System32 directory on their client machines. This seemed like a good idea, but with the changes in Delphi 7 this turns out to be a potential nightmare: the Delphi 6 versions of dbexpmys.dll and dbexpmysql.dll break all Delphi 7 applications, and while Delphi 7's version of the dbExpress MySQL DLLs work fine with Delphi 6 applications, there will be problems with Delphi 7's dbExpress InterBase DLLs and Delphi 6 applications (due to dbExpress field mappings that have changed from one version of Delphi to another: see the README for details). If you're the only developer, you can recompile everything with Delphi 7, burn all ties with Delphi 6, and never look back. Until Delphi 8 ships (unless Borland learns something from this  [Hah! Ed.]). A better solution may be to ensure that the dbExpress driver DLLs are only deployed in the application directory itself (and not in the system directory), which kinda defeats the idea of using DLLs in my view. Finally, linking with the dbExpress driver unit instead of using the external DLL can be a solution in some cases (linking the dbExpress driver into your application), but not all. A serious quoting bug has been found in the dbExpress driver import unit for DB2 and Delphi 7, which renders this solution useless for DB2. So for now we just have to remain very cautious when we are deploying dbExpress and dbExpress applications.

---