Delphi- dbExpress And MySQL.doc

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

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