Under Construction: Delphi, dbExpress And MySQL

Under Construction: Delphi, dbExpress And MySQL

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 doesnt 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 isnt 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 Ive 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, its amazing how well-known the name of MySQL has become in just a few years. The fact that its 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.

Its 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 wont get official status as long as MySQL 4 itself remains in beta, of course).

Both issues are less likely to arise if youre 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 its 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 (IBMs DB2 comes to mind). The c:MySQLDocs directory contains a manual in HTML and plain text format, which unfortunately isnt 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:MySQLbin 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 isnt 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:mysqlData 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, well now see how we can talk to MySQL using Delphi and dbExpress.

MySQL And dbExpress

Once youve 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 youve selected MySQLConnection, the LibraryName gets set to dbexpmys.dll, which is the dbExpress MySQL driver for MySQL version 3.22.x. Since were using a higher version number 3.23.x we must explicitly change that property value from dbexpmys.dll to dbexpmysql.dll otherwise it wont 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:MySQLbin, and you can either add c:MySQLbin to the search path, or copy the MySQL.dll somewhere in the path, like the WinNTSystem32 directory. A third alternative consists of using a hardcoded c:mysqlbin 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 weve 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 months 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 its 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

As you can see, the master-detail relationship is defined at the ClientDataSet level here. Lets 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

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

Table 4

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, its a memory versus speed trade-off, and youll 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. Ill cover deployment in more detail next time, when Ill 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.