UniDAC

Using MySQL data access provider with UniDAC in Delphi

This article provides a brief overview of the MySQL data access provider for UniDAC used to establish a connection to MySQL databases from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.

Overview

MySQL data access provider is based on the MySQL Data Access Components (MyDAC) library, which provides direct access to MySQL database servers from Delphi, C++Builder and Lazarus (FPC). The main features of MySQL data access provider are:

The full list of MySQL provider features can be found at the UniDAC product page.

Both Professional and Standard Editions of UniDAC include the MySQL provider. For Express Edition of UniDAC, the MySQL provider can be installed with MyDAC.

Compatibility

To learn about MySQL database server compatibility, refer to the Compatibility section.

Requirements

If you use MySQL provider to connect to MySQL in Direct mode, you do not need to have MySQL client library on your machine or deploy it with your MySQL provider-based application.

If you use MySQL provider to connect to MySQL in Client mode, you need to have access to the MySQL client library. In particular, you will need to make sure that the MySQL client library is installed on the machines your MySQL provider-based application is deployed to. MySQL client library is libmysql.dll file for Windows. Please refer to the description of LoadLibrary() function for detailed information about MySQL client library file location. You may need to deploy the MySQL client library with your application or require that users have it installed.

If you are working with Embedded server, you should have access to Embedded MySQL server library (libmysqld.dll).

Deployment

MySQL provider applications can be built and deployed with or without run-time libraries.

You do not need to deploy any files with MySQL provider-based applications built without run-time packages, provided you are using a registered version of UniDAC. You can set your application to be built with run-time packages. In this case, you will need to deploy dacXX.bpl and mydacXX.bpl files with your Win32 application.

For more information about deployment of UniDAC-based applications, please, refer to the common Deployment topic.

MySQL-specific options

Though UniDAC is components that provide unified interface to work with different database servers, it also lets you tune behaviour for each server individually. For thin setup of a certain database server, UniDAC provides server-specific options. These options can be applied to such components as TUniConnection, TUniQuery, TUniTable, TUniStoredProc,TUniSQL, TUniScript via their SpecificOptions property. SpecificOptions is a sting list. Therefore you can use the following syntax to assign an option value:

UniQuery.SpecificOptions.Values['FieldsAsString'] := 'True';

Below you will find the description of allowed options grouped by components.

TUniConnection

Option name Description
Charset Setups the character set used by the client.
Compress Use compression on transferring data. Setting this property to True is quite effective on transferring big volume data through slow connection. This property is ignored under CLR. The default value is False.
ConnectionTimeout Specifies the amount of time in seconds that can be expired before an attempt to make a connection is considered unsuccessful.
Embedded If True, connects to Embedded MySQL server. If False, connects to MySQL server. The default value is False.
EmbeddedParams Allows to set such parameters of embedded connection as --basedir, --datadir, etc. Parameters should be separated with newline characters (#13#10), for example:
UniConnection.SpecificOptions.Values['MySQL.EmbeddedPrams'] := '--basedir=.'#13#10'--datadir=data';
The default value is ''
HttpTrustServerCertificate This option specifies whether or not the driver should trust the server certificate when connecting to the server. The default value is False – the driver won't trust the server certificate and will verify validity of the server certificate instead. If set to True, the driver will trust the server certificate.
Interactive Determines the inactivity timeout before the server breaks the connection. If true, the server breaks the connection after number of seconds specified in interactive_timeout sever variable, otherwise wait_timeout is used. The default value is false. The interactive_timeout and wait_timeout variables can be set in my.ini file.
IPVersion Use the IPVersion property to specify Internet Protocol Version.

Supported values:

ivIPBoth
Specifies that either Internet Protocol Version 6 (IPv6) or Version 4 (IPv4) will be used.

ivIPv4 (default)
Specifies that Internet Protocol Version 4 (IPv4) will be used.

ivIPv6
Specifies that Internet Protocol Version 6 (IPv6) will be used.

Note: When the TIPVersion property is set to ivIPBoth, a connection attempt will be made via IPv6 if it is enabled on the operating system. If the connection attempt fails, a new connection attempt will be made via IPv4.
NullForZeroDelphiDate Use the NullForZeroDelphiDate property to hide the '30-12-1899' dates. If NullForZeroDelphiDate is set to True, the values of all datetime fields will be changed to Null. If the property is set to False, the '30-12-1899' value will be used as an ordinary date. The default value is false.
OptimizedBigint Setting this option converts all fields with field length less than 11 of TLargeIntField type into TIntegerField. This allows to process fields that are results of numeric function or cast values as usual Integer fields. The default value is False.
Protocol Specifies which protocol to use when connecting to the server:

mpDefault
Similar to mpTCP, except the cases when 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.
mpTCP
Use TCP/IP to connect to the server.
mpSocket
Uses sockets to connect to the server. Can be used with Direct set to False and libmysql.dll 4.1.
mpPipe
Use NamedPipes to connect to the server.
mpMemory
To connect to the server using SharedMem. Can be used with Direct set to False and libmysql.dll 4.1.
mpSSL
Use protected SSL connection with the server.
mpHttp
Uses HTTP Network Tunneling to connect to the server.
HttpUrl Holds the url of the tunneling PHP script.
HttpUsername Holds the user name for HTTP authorization.
HttpPassword Holds the password for HTTP authorization.
ProxyHostname Holds the host name or IP address to connect to proxy server.
ProxyPort Used to specify the port number for TCP/IP connection with proxy server.
ProxyUsername Holds the proxy server account name.
ProxyPassword Holds the password for the proxy server account.
SSLCACert CACert is the pathname to the certificate authority file.
SSLCert Cert is the pathname to the certificate file.
SSLChipherList ChipherList is a list of allowable ciphers to use for SSL encryption.
SSLKey Key is the pathname to the key file.
UseUnicode Informs server that all data between client and server sides will be passed in UTF-8 coding. Setting this option converts all fields of TStringField type into TWideStringField that allows to work correctly with symbols of almost all languages simultaneously. On the other hand, it causes a delay in working. The default value is False.

TUniSQL

Option name Description
CommandTimeout Specifies the amount of time that is expired before an attempt to execute a command is considered unsuccessful. Measured in seconds.
If a command is successfully executed prior to the expiration of the seconds specified, CommandTimeout has no effect. The default value is 0 (infinite).

TUniQuery, TUniTable, TUniStoredProc

Option name Description
BinaryAsString Specifies the method of representation of BINARY and VARBINARY types. If set to True, binary field data will be retrieved as a string and handled by the TStringField class. The default value is True.
CheckRowVersion Determines whether the dataset checks for rows modifications made by another user on automatic generation of SQL statement for update or delete data. If CheckRowVersion is True and DataSet has timestamp field when only this field is added into WHERE clause of the generated SQL statement. If CheckRowVersion is True, but there is no TIMESTAMP field, then to WHERE clause all non-BLOB fields will be added. The default value is False.
CommandTimeout Specifies the amount of time that is expired before an attempt to execute a command is considered unsuccessful. Measured in seconds.
If a command is successfully executed prior to the expiration of the seconds specified, CommandTimeout has no effect. The default value is 0 (infinite).
CreateConnection Specifies if an additional connection to a server should be established to execute an additional query in the FetchAll=False mode. If a DataSet is opened in FetchAll=False, the current connection is blocked until all records have been fetched. If this option is set to True, an additional connection is created to fetch data to avoid blocking of the current connection.
EnableBoolean Specifies the method of representation of TINYINT(1) fields. If set to True, these fields will be represented as TBooleanFiled; otherwise, as TSmallintField. The default value is True.
FetchAll When set to True, all records of the query are requested from the database server when dataset is being opened. 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.
When the FetchAll property is False, the first call to Locate and LocateEx methods may take a lot of time to retrieve additional records to the client side.
FieldsAsString All non-BLOB fields are stored as string (native MySQL format). The default value is False.
NullForZeroDate For datetime fields with invalid values, for example '2002-12-32', MySQL returns on fetch '0000-00-00' value. According to NullForZeroDate option this value will be represented as Null or '0001-01-01' ('0100-01-01' for CLR). The default value is True.

TUniScript

The TUniScript component has no MySQL-specific options.

TUniLoader

Option name Description
LockTable Locks tables while inserting data.
Delayed Uses INSERT DELAYED syntax.
RowsPerQuery Use the RowsPerQuery property to get or set the number of rows that will be send to the server for one time. The default value is 0. In this case rows will be grouped by 16Kb (the default value of net_buffer_length).
DuplicateKeys Use the DuplicateKeys property to specify in what way conflicts with duplicated key values will be resolved.
QuoteNames Use the QuoteNames option to quote all database object names in automatically generated SQL statements, such as UPDATE statements. The default value is False.

TUniDump

Option name Description
AddLock Use the AddLock property to execute LOCK TABLE before data insertion. Used only with doData in P:Devart.MyDac.TMyDump.Objects.
BackupData Use the option to backup the data in a table. The default value is True.
BackupStoredProcs Use the enable backup of stored procedures. The default value is False.
BackupTables Use the option to enable backup of the table structure. The default value is False.
BackupTriggers Use the option to enable backup of triggers. The default value is False.
BackupViews Use the option to enable backup of views. The default value is False.
CommitBatchSize Use the CommitBatchSize option to add COMMIT statement to script after the specified number of strings when dumping table data. The option is useful for recovering large amounts of data. The default value is 0.
DisableKeys Add /*!40000 ALTER TABLE ... DISABLE KEYS */ before inserting data. Used only with doData in P:Devart.MyDac.TMyDump.Objects.
InsertType Specifies how rows will be inserted into a table.

Supported values:

itInsert (default)
New rows will be inserted into an existing table. If a duplicate entry is encountered, an exception will be raised.

itInsertIgnore
The insert operation will fail silently for rows containing an unmatched value, but inserts rows that are matched, without raising an exception.

itReplaceInto
If an old row in a table has the same value as a new row, the old row will be deleted before the new row is inserted.
HexBlob If the HexBlob property is True, the BLOB values are presented in hexdecimal notation.
UseExtSyntax Set the UseExtSyntax propery to use extended syntax of INSERT on data insertion. Used only with doData in P:Devart.MyDac.TMyDump.Objects.
UseDelayedIns Set the UseDelayedIns property to use INSERT DELAYED. Used only with doData in P:Devart.MyDac.TMyDump.Objects.
© 1997-2020 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback