Delphi, dbExpress And MySQL - Part 1

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

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.