dbExpress driver for MySQL 4.20	History
Readme	Support

dbExpress is database-independent layer that defines a common interface to provide fast access to SQL database servers. For each supported server, dbExpress provides a driver as an independent library that implements the common dbExpress interfaces for processing queries and stored procedures. As data-access layer is thin and simple, dbExpress provides high performance database connectivity and is easy to deploy. dbExpress drivers are available for Windows as dynamic-link libraries.

DbxMda driver provides direct access to MySQL database server based on Borland dbExpress data access technology. It can work using MySQL client or native MySQL network protocol.

• Key features
• Compatibility
• Windows installation notes
• Linux installation notes
• Using
• Type map
• Extended driver options
• TCRSQLConnection
• Using DBMonitor
• Deployment
• Trial limitations
• Notes
• Known problems
• Using Embedded server
• Demo projects

Key features

The following list describes the main features of the DbxMda driver

• Access to MySQL server using client library or through protocol directly
• High performance
• Supports latest versions of MySQL
• Support for MySQL Embedded server
• All MySQL data types support
• Ability of monitoring query execution with DBMonitor
• Extended options for advanced behaviour
• Complete support for dbExpress version 4
• Available source code
• Support for data compression
• Support for different network protocols
• Included annual DbxMda Subscription with Priority Support
• Licensed royalty-free per developer, per team, or per site

Compatibility

DbxMda works with Delphi 2009 for Win32, C++Builder 2009, CodeGear RAD Studio 2007, C++Builder 2007, Delphi 2007 for Win32, Turbo Delphi, Turbo Delphi for .NET, Turbo C++, Delphi 2006, Delphi 2005, Delphi 8, Delphi 7, Delphi 6, C++Builder 6, Kylix 3 and Kylix 2.

DbxMda supports MySQL servers 5.1, 5.0, 4.1, 4.0 and 3.23.

Applications that do not use direct mode, require the MySQL client library. For Windows it's the libmysql.dll file, for Linux - libmysqlclient.so (libmysqlclient.so.X). Please refer to descriptions of LoadLibrary() and dlopen() functions accordingly for detailed information about MySQL client library file location.

If you work with MySQL Embedded server, you should use libmysqld.dll (libmysqld.so) instead of libmysql.dll. Please refer to "Using Embedded Server" article for details.

You should also pay attention to the following notes:

1. It is strongly recommended to use the corresponding library with your IDE. Please take a look at this table for more details.
2. Update Pack 2 is required for Delphi 2006 .NET Personality.
3. Update Pack 3 is required for C++Builder 2007 and Delphi 2007 for Win32.

Windows installation notes

RAD Studio 2007 and 2009

The standard TSQLConnection component in RAD Studio 2007 and 2009 provides exactly the same functionality like TCRSQLConnection does. Therefore TCRSQLConnection component is not registered automatically. But source code of the package containing this component is installed for providing backward compatibility. If you need to use TCRSQLConnection, you should compile and install its packages manually. They are located in the <Driver_Installation_Directory>\Source\DelphiXX\ directory. The dclcrdbxXXX.dproj package is used with Delphi for Win32 and C++Builder. And the Devart.Dbx.dproj package is used with Delphi for .Net.

If you have only C++Builder installed, you can compile the package from the command line using a command like the following one:

C:\Program Files\CodeGear\RAD Studio\5.0\bin\dcc32.exe" dclcrdbx105.dpk

To compile an application that uses TCRSQLConnection components, it is necessary to add the path to TCRSQLConnection sources to the Search path in Project Options or to the Library path in Environment Options:

• in Delphi for Win32 add <Driver_Installation_Directory>\Source;
• in Delphi for .Net add <Driver_Installation_Directory>\Source and <Driver_Installation_Directory>\Source\Net;
• in C++Builder add <Driver_Installation_Directory>\Source to the Library path and to the Include path in Project Options.

Delphi 2006

If you use TCRSQLConnection in applications developed with C++Builder Personality of Delphi 2006 or Turbo C++, you should add the path to the DbxIda folder to Project Options|Library and Project Options|Include.

Linux installation notes

Make the libsqlmda.so.X.X library available for applications by correcting the LD_LIBRARY_PATH environment variable or by copying it to a directory already included in LD_LIBRARY_PATH.

To work with driver from IDE, you need to register driver in dbExpress configuration files.

1. Find dbxdrivers file (path is similar to /home/<user>/.borland)
2. Add or replace following sections:

     [DevartMySQL]
     GetDriverFunc=getSQLDriverMySQL
     LibraryName=libsqlmda.so.X.X
     VendorLib=libmysql.so
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
   
     [DevartMySQLDirect]
     GetDriverFunc=getSQLDriverMySQLDirect
     LibraryName=libsqlmda.so.X.X
     VendorLib=libmysql.so
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
   
     [DevartMySQLEmbedded]
     GetDriverFunc=getSQLDriverMySQLEmbedded
     LibraryName=libsqlmda.so.X.X
     VendorLib=libmysql.so
     BlobSize=-1
     DataBase=
     User_Name=
     Password=
     

3. In section [Installed Drivers] add line

     DevartMySQL=1
     DevartMySQLDirect=1
     DevartMySQLEmbedded=1
     

4. Find dbxconnections file (path is similar to /home/<user>/.borland)
5. Add or replace following lines:

     [Devart MySQL]
     BlobSize=-1
     DataBase=
     DriverName=DevartMySQL
     User_Name=
     Password=
     FetchAll = True
   
     [Devart MySQL Direct]
     BlobSize=-1
     DataBase=
     DriverName=DevartMySQLDirect
     User_Name=
     Password=
     FetchAll = True
   
     [Devart MySQL Embedded]
     BlobSize=-1
     DataBase=
     DriverName=DevartMySQLEmbedded
     User_Name=
     Password=
     FetchAll = True
     

6. Restart Kylix IDE

Using

Usage of DbxMda is the very same as one of standard dbExpress drivers supplied by Borland. For additional information refer to description of the TSQLConnection component.

To setup SQLConnection for using DbxMda just set at design-time ConnectionName property to "Devart MySQL" connection configurations.

At run-time you need to set LibraryName, VendorLib and GetDriverFunc properties. Set connection parameters such as User_Name, Password, HostName and Database to provide login information.

Borland Turbo products and Delphi 2006 introduce dbExpress 3.0 interfaces, CodeGear RAD Studio 2007 and 2009 introduce dbExpress 4.0. They are implemented in dbexpoda30.dll and dbexpoda40.dll correspondingly, and we highly recommend using the correspondent driver.

Example

Connection with client library:

  SQLConnection.DriverName := 'DevartMySQL'; // 'DevartMySQL' - for Delphi 2006 and higher
  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := 'libmysql.dll';
  SQLConnection.GetDriverFunc := 'getSQLDriverMySQL';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=MyName');
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('HostName=MyServer');
  SQLConnection.Params.Add('Database=MyDatabase');
  SQLConnection.Open;

For Direct connection:

  SQLConnection.DriverName := 'DevartMySQL Direct'; // 'DevartMySQL Direct' - for Delphi 2006 and higher
  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := 'not used';
  SQLConnection.GetDriverFunc := 'getSQLDriverMySQLDirect';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=MyName');
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('HostName=MyServer');
  SQLConnection.Params.Add('Database=MyDatabase');
  SQLConnection.Open;

For Embedded server connection:

  SQLConnection.DriverName := 'DevartMySQL Embedded'; // 'DevartMySQL Embedded' - for Delphi 2006 and higher
  SQLConnection.LibraryName := <library name>; // <library name> depends on your IDE version. See the table below.
  SQLConnection.VendorLib := 'libmysqld.dll';
  SQLConnection.GetDriverFunc := 'getSQLDriverMySQLEmbedded';
  SQLConnection.Params.Clear;
  SQLConnection.Params.Add('User_Name=MyName');
  SQLConnection.Params.Add('Password=MyPassword');
  SQLConnection.Params.Add('HostName=MyServer');
  SQLConnection.Params.Add('Database=MyDatabase');
  SQLConnection.Open;

The following table determines how library name depends on the used IDE version:

Library name	IDE version
dbexpmda40.dll	CodeGear RAD Studio 2007, 2009
dbexpmda30.dll	Delphi 2006 and Turbo products
dbexpmda.dll	other IDE versions

Type map

MySQL data types are mapped to TFieldType in the following way:

MySQL type	Field type	Field class
TINYINT	ftSmallint	TSmallintField
TINYINT(1), EnableBoolean = True	ftBoolean	TBooleanField
TINYINT UNSIGNED	ftWord	TWordField
SMALLINT	ftSmallint	TSmallintField
SMALLINT UNSIGNED	ftWord	TWordField
MEDIUMINT	ftInteger	TIntegerField
MEDIUMINT UNSIGNED	ftInteger	TIntegerField
INT	ftInteger	TIntegerField
INT UNSIGNED	ftInteger	TIntegerField
BIGINT	ftFMTBcd	TFMTBCDField
BIGINT UNSIGNED	ftFMTBcd	TFMTBCDField
DOUBLE	ftFloat	TFloatField
FLOAT	ftFloat	TFloatField
DECIMAL	ftBCD	TBCDField
CHAR	ftString	TStringField
CHAR BINARY	ftString	TStringField
VARCHAR	ftString	TStringField
VARCHAR BINARY	ftString	TStringField
DATE	ftDate	TDateField
TIME	ftTime	TTimeField
TIMESTAMP	ftTimeStamp	TSQLTimeStampField
DATETIME	ftTimeStamp	TSQLTimeStampField
YEAR	ftWord	TWordField
TINYBLOB	ftBlob	TBlobField
BLOB	ftBlob	TBlobField
MEDIUMBLOB	ftBlob	TBlobField
LONGBLOB	ftBlob	TBlobField
TINYTEXT	ftMemo	TMemoField
TEXT	ftMemo	TMemoField
MEDIUMTEXT	ftMemo	TMemoField
LONGTEXT	ftMemo	TMemoField
ENUM	ftString	TStringField
SET	ftString	TStringField

Extended driver options

DbxMda provides several extended options that expand functionality of the driver. In order to overcome restrictions of dbExpress on several IDEs, DbxMda provides the TCRSQLConnection component. It simplifies working with extended driver options.

The dbExpress technology version 4 in CodeGear RAD Studio 2007 and 2009 does not have such restrictions, so the TCRSQLConnection component has no sense for these IDEs.

Starting with CodeGear RAD Studio 2007 you should assign parameter values to TSQLConnection and TCRSQLConnection at run time in this way:

SQLConnection.Params.Values['Option Name'] := 'Option Value';

In other IDE versions it is better to use TCRSQLConnection instead of standard TSQLConnection. Extended options are available with TSQLConnection only at run time.

To setup these options at design time, you should use the Params property of TCRSQLConnection.

To use an option with TSQLConnection in Delphi 2006 and lower IDE versions, you should define constants and set appropriate option values using event handlers like:

TSQLConnection.AfterConnect
TSQLQuery.BeforeOpen

Below is the example that demonstrates setting extended driver options in Delphi 2006 and lower IDE versions:

const
  coFetchAll = TSQLConnectionOption(301);

procedure TForm1.SQLQuery1BeforeOpen(DataSet: TDataSet);
begin
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));
end;

If you compile the application for CLR, you should use

  ISQLConnection_SetOption(SQLConnection1, ...)

instead of

  SQLConnection1.SQLConnection.SetOption(...)

Here is the description of the options and how to use them.

Charset

Use this option to set character set used by the client. Actually if this property is enabled then on establishing a connection "SET NAMES <Charset>" query is executed. If UseUnicode option is set, Charset option will be ignored.

const
  coCharset = TSQLConnectionOption(204); //string
. . .
  SQLConnection1.SQLConnection.SetOption(coCharset, Integer(PChar('latin1')));

Custom String

With Delphi 7 and higher IDE versions you can use the "Custom String" option to provide additional connection settings:

• Compress - enables compression for the connection. Acceptable values: True, False. Default value is False.
• Protocol - lets you specifies, which protocol to use when connecting to server. Acceptable values:
  • Default - Similar to TCP, except that cases then you connect to a local server and the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.
  • TCP - Use TCP/IP to connect to the server.
  • Socket - Use sockets to connect to the server. Can be used with Direct set to False and libmysql.dll 4.1 and higher.
  • Pipe - Use NamedPipes to connect to the server.
  • Memory - To connect to the server using SharedMem. Can be used with Direct set to False and libmysql.dll 4.1 and higher.

Sample code looks like this.

  SQLConnection.Params.Add('Custom String=Compress=True;Protocol=TCP');

EnableBoolean

Specifies a method of representation of TINYINT(1) fields. If set to True these fields will be represented as TBooleanFiled; otherwise, as TSmallintField.

Default value is False.

const
  coEnableBoolean = TSQLConnectionOption(304); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coEnableBoolean, Integer(True));

FetchAll

Set FetchAll to True to request all records of the query from database server when the dataset is opening. When set to False, records are retrieved when a data-aware component or a program requests it. If a query can return a lot of records, set this property to False if initial response time is important.

Note: in some cases FetchAll = False may conflict with transaction control, working with temporary tables and may cause deadlocks on editing statements with ORDER BY clause.

Default value is True.

const
  coFetchAll = TSQLConnectionOption(301);
. . .
  SQLConnection1.SQLConnection.SetOption(coFetchAll, Integer(True));

Prepared

Set Prepared to True to execute query preparation on the server that allows to increase performance on multiple execution of the queries.

Note: in contrast to setting TCustomSQLDataSet.Prepared to True that prepares a query only on client side, setting SetOption(coPrepared, Integer(True)) prepares a query on the server.

Default value is False.

const
  coPrepared = TSQLConnectionOption(302); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coPrepared, Integer(True));

TrimFixedChar

Specifies whether to discard all trailing spaces in string fields of the dataset.

const
  coTrimFixedChar = TSQLConnectionOption(103); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coTrimFixedChar, Integer(True));

UseQuoteChar

This boolean option when turned on makes the driver quote all names of objects. Setting it to True makes sense only for using reserved words as object names.

Default value is False.

const
  coUseQuoteChar = TSQLConnectionOption(202); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUseQuoteChar, Integer(True));

UseUnicode

Enables or disables Unicode support. Affects character data fetched from the server. When set to True all character data is stored as WideStrings and TStringField is replaced with TWideStringFiled.

This option is available for Delphi 2006 and higher IDE versions.

Default value of this option is True for Delphi 2009 and higher IDE versions, and False for Delphi 2006 and 2007.

const
  coUseUnicode = TSQLConnectionOption(209); // boolean
. . .
  SQLConnection1.SQLConnection.SetOption(coUseUnicode, Integer(True));

TCRSQLConnection

TCRSQLConnection component serves to support additional options of dbExpress drivers for Oracle (DbxOda), SQL Server (DbxSda) and MySQL (DbxMda). Option configuration can be made in dbExpress configuration files (dbxconnections.ini) or in Params list of TCRSQLConnection.

You can use following options:

• Charset
• EnableBoolean
• FetchAll
• Prepared
• TrimFixedChar
• UseQuoteChar
• UseUnicode

For example

var
  SQLConnection1: TCRSQLConnection;
...
  SQLConnection1.Params.Values['TrimFixedChar'] := 'False';
  SQLConnection1.Connected := True;

Note: for quick replacement of TSQLConnection to TCRSQLConnection at design-time it's enough to use context menu of the component.

Using DBMonitor

To extend monitoring capabilities of dbExpress applications with DbxMda driver, you can use an additional freeware tool called DBMonitor. It is provided as an addition to a monitoring procedure with using SQLMonitor component.

DBMonitor is easy-to-use tool to provide visual monitoring of your database applications.

DBMonitor has the following features:

• Multiple client processes tracing;
• SQL event filtering (by sender objects);
• SQL parameter and error tracing.

DBMonitor is intended to hamper application being monitored as less as possible.

To trace your application with DBMonitor you should follow these steps:

• drop TSQLMonitor component onto the form;
• start DB Monitor before running your program.

Note: if you are Delphi Pro version user, then you don't have TSQLMonitor component installed on the palette, but it is included in SQLExpr.pas unit and you need to install it on the components palette manually.

Deployment

The following additional files are required on the target computer:

File	When required	Where to find
dbexpmda.dll (dbexpmda30.dll for Delphi 2006 or Turbo products users, dbexpmda40.dll for CodeGear RAD Studio 2007, 2009 users)	Required for any application	DbxMda installation directory
dbxintf.dll	Required for any CLR application	IDE installation directory
Devart.DbxMda.DriverLoader.dll (should be placed in GAC)	Required for CLR applications built with CodeGear RAD Studio 2007	DbxMda installation directory

Linux applications require the libsqlmda.so.X.X library.

Users of dbExpress driver for MySQL with Source Code can embed the driver into the application directly. For information on how to do this refer to Borland documentation.

Trial limitations

Windows DbxMda trial version can be used within 30 days starting from the moment of installation.

Linux Trial version of the driver is limited by number of the returned table columns. DbxMda trial version returns cursors with maximum 6 columns. Extra select-list columns are truncated.

Notes

Database object names that contain spaces or national language characters should be always quoted. For example,

  SQLTable.TableName := '`Table name`';  

When calling stored procedures that return several ResultSets you should take into account the following issues:

• you can use FetchAll = False mode only
• if you execute stored procedure several times before calling Open method you need perform following sequence:

        SQLStoredProc.Close;
        SQLStoredProc.FieldDefs.Updated := False;
        SQLStoredProc.Prepared := False;
      

• If you use Delphi 6 then you need to unset FieldDefs.Updated before calling NextRecordSet

        SQLStoredProc.FieldDefs.Updated := False;
        SQLStoredProc.NextRecordSet;
        SQLStoredProc.FieldDefs.Updated := False;
        SQLStoredProc.NextRecordSet;
      

Known problems

• If on modification or deletion of a record the list of fields in WHERE clause has FLOAT fields, and TSQLClientDataSet.UpdateMode = upWhereAll, the query can refresh no records. The cause of the problem is that FLOAT fields are stored on the server with low precision and couldn't be correctly compared with passed value.

• dbExpress for Delphi 6, C++Builder 6 does not support complex object names. For example, the following assignments are not valid:

      SQLTable.TableName := '`Table name`';  
      SQLTable.TableName := 'BaseName.`Table name`';  
      SQLTable.TableName := 'BaseName.TableName';
    

• If on modification or deletion of a record the list of fields of WHERE clause has TIMESTAMP fields with null values, and TSQLClientDataSet.UpdateMode = upWhereAll, the query doesn't refresh any records. The problem is that null values of TIMESTAMP fields couldn't be compared with NULL (see MySQL manual).

• dbExpress for Delphi 2007 does not support TSQLStoredProc.NextRecordSet.

Using Embedded server

Since version 4.0 MySQL server supports Embedded server. Embedded server is an easy at installation server to be used by applications that do not require multi-user work with MySQL server. As an example Embedded server can be used for money access machines, automatic cash desks, different electronic facilities and so on. Please refer to MySQL web site for more details about features and using of Embedded server. Also you can find an information about licensing Embedded server at MySQL web site.

Windows Configuration and Distribution

To install application that uses Embedded server at the end-user machine you should:

• Copy libmysqld.dll file to the folder available for executable file of the application. Please see detailed description of accessible paths at LoadLibrary description;
• Create my.ini file at the root Windows folder and fill its sections:
  • [Server] - common settings for MySQL usual and Embedded servers.
  • [Embedded] - settings specific to Embedded server.
  • [<Application exe-file name (with extension)>] - settings specific to particular application.

  Details about list of settings you can find at MySQL Reference Manual.

• Create folder to store data (by default, c:\mysql\data) and specify path to this folder at my.ini:

  datadir=c:\mysql\data

  or

  datadir=.

  
  At the last case files with data will be stored at the folder with your executable program.

Linux Configuration and Distribution

• Copy libmysqld.so.14.0.0 file to /usr/lib folder
• At /usr/lib folder execute following commands to create links: ln libmysqld.so.14.0.0 libmysqld.so ln libmysqld.so.14.0.0 libmysqld.so.14
• Create my.cnf file at /etc folder and fill the following sections:
  • [Server] - common settings for MySQL usual and Embedded servers.
  • [Embedded] - settings specific to Embedded server.
  • [<Application exe-file name (with extension)>] - settings specific to particular application.

  Details about list of settings you can find at MySQL Reference Manual.

• Create folder to store data (by default, /var/lib/mysql) and specify path to this folder at my.cnf:

  datadir=/var/lib/mysql

  or

  datadir=.

  
  At the last case files with data will be stored at the folder with your executable program.

Limitations

Simultaneous access to the same data from several instances of MySQL server (for example, to MySQL server and Embedded server) can be a reason of data loss.

Demo projects

Following demo projects accompany DbxMda and provide a good bootstrap to start working with it. They are located inside DbxMda\Demos folder.

Sample	Description
BlobPictures	This sample project demonstrates how to work with MySQL BLOB type to store binary images and display in TDBImage
ClientDataSet	This sample project demonstrates working with TSQLClientDataSet in Delphi 6
Embedded	This sample project demonstrates working with MySQL Embedded Server
Query	This sample project allows to get result record set using TSQLQuery component
SimpleDataSet	This sample project demonstrates working with TSimpleDataSet in Delphi 7

 

 

---

© 2001-2009 Devart. All rights reserved.