Mysql

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¶

TUniSQL¶

TUniQuery, TUniTable, TUniStoredProc¶

TUniLoader¶

TUniDump¶

Data Type Mapping¶

The following table lists the constants for mapping MySQL data types to Delphi data types. See Data Type Mapping for more information.

Network Tunneling¶

Usually when a client needs to connect to server it is assumed that direct connection can be established. Nowadays though, due to security reasons or network topology, it is often necessary to use a proxy or bypass a firewall. This article describes different ways to connect to MySQL server with UniDAC.

• Direct connection
• Connection through HTTP tunnel
  • Connection through proxy and HTTP tunnel
• Additional information

Direct connection¶

Direct connection to server means that server host is accessible from client without extra routing and forwarding. This is the simplest case. The only network setting you need is the host name and port number. This is also the fastest and most reliable way of communicating with server. Use it whenever possible.

The following code illustrates the simplicity:

UniConnection := TUniConnection.Create(self);
UniConnection.ProviderName := 'MySQL';
UniConnection.Server := 'localhost';
UniConnection.Port := 3306;
UniConnection.Username := 'root';
UniConnection.Password := 'root';
UniConnection.Connect;

Connection through HTTP tunnel¶

Sometimes client machines are shielded by a firewall that does not allow you to connect to server directly at the specified port. If the firewall allows HTTP connections, you can use UniDAC together with HTTP tunneling software to connect to MySQL server.

UniDAC supports HTTP tunneling based on the PHP script.

An example of the web script tunneling usage can be the following: you have a remote website, and access to its database through the port of the database server is forbidden. Only access through HTTP port 80 is allowed, and you need to access the database from a remote computer, like when using usual direct connection.

You need to deploy the tunnel.php script, which is included into the provider package on the web server. It allows access to the database server to use HTTP tunneling. The script must be available through the HTTP protocol. You can verify if it is accessible with a web browser. The script can be found in the HTTP subfolder of the installed provider folder, e. g. %Program Files%\Devart\UniDac for Delphi X\HTTP\tunnel.php. The only requirement to the server is PHP 5 support.

To connect to the database, you should set TUniConnection parameters for usual direct connection, which will be established from the web server side, the Protocol specific MySQL option to mpHttp, and set the following parameters, specific for the HTTP tunneling:

Connection through proxy and HTTP tunnel¶

Consider the previous case with one more complication.

HTTP tunneling server is not directly accessible from client machine. For example, client address is 10.0.0.2, server address is 192.168.0.10, and the MySQL server listens on port 3307. The client and server reside in different networks, so the client can reach it only through proxy at address 10.0.0.1, which listens on port 808. In this case in addition to the Http specific options you have to setup the Proxy specific options as follows:

UniConnection := TUniConnection.Create(self);
UniConnection.ProviderName := 'MySQL';
UniConnection.Server := '192.168.0.10';
UniConnection.Port := 3307;
UniConnection.Username := 'root';
UniConnection.Password := 'root';
UniConnection.SpecificOptions.Values['Protocol'] := 'mpHttp';
UniConnection.SpecificOptions.Values['HttpUrl'] := 'http://server/tunnel.php';
UniConnection.SpecificOptions.Values['ProxyHostname'] := '10.0.0.1';
UniConnection.SpecificOptions.Values['ProxyPort'] := '808';
UniConnection.SpecificOptions.Values['ProxyUsername'] := 'ProxyUser';
UniConnection.SpecificOptions.Values['ProxyPassword'] := 'ProxyPassword';
UniConnection.Connect;

Note that setting the Proxy specific options automatically enables proxy server usage.

Additional information¶

Keep in mind that traffic tunneling or encryption always increase CPU usage and network load. It is recommended that you use direct connection whenever possible.