Contents

Introduction

Erlang client for MySQl server
is a native client. It means the client is directly connecting to the server and using
low level (network socket level) connection protocol. So advantage of the client as a native
is speed and performance. Disadvantage is that client design tightly coupled with MySQL
connection protocol, but MySQL team is conservative enough with it.

Design

The MySQL client is implemented as Erlang/OTP application. Root supervisor of the client
manages a set of datasource processes each of them can be pointed to different MySQl servers
or to the same but differently configured. To create a datasource process (or object)
we can use function from the client API:

Datasource is supervisor in own turn and keeps management under one connection pool process
and a few active connection processes to server. The connection pool stores a reusable
ip/tcp socket and is tracking their life cycles. When new connection process is creating then the
connection pool is trying to provide an existed but idle socket. If set of idle socket is empty
connection pool creates new one. If connection process has closed then assigned socket becomes idle
and goes to set of idle sockets of the pool.

Supported features

The client supports the following client features of MySQL server:

configure client connection using #client_options{} record

compression mode for transfer data through connection

transfer of very long packets as a multi sub-packets

prepared statements execution

transfer a long statement parameters (blob parameters)

fetch cursor on server previously generated by prepared statement

transaction execution

Getting started

Obtain connection

We have already created datasource object above. So now we can obtain connection to our server:

Query returns a tuple that contains two members. First one is metadata record
represented information about table fields and second one is list of records represented
rows of query result. Each row contains values of fields of SELECT statement. In our case
there are 'id' and 'name' fields.

We need to pass a statement's parameter types and values to function that executes prepared statement. Result has to be:

Result: [{rs_row_data, [1,"Alex"]}]

Compressed mode

Compression protocol is supported by the client if given instance of MySQL supports it.
To activate this feature set compress field in #client_options record and pass the record
to datasource definition when create new datasource object.

After that we can send to server long block of data that has size of 1000000 bytes:

my:send_statement_long_parameter(Conn, Handle, 0, <<16#AA:8000000>>),

Third parameter of the function is a position number of given parameter
in prepared statement sentence. We can apply the send_statement_long_parameter/4 a few times
and all chunks will be merged in one huge data block. Now as we complete a sending
of statement parameter value to server we can finally execute the statement:

my:execute_statement(Conn, Handle, [?LONG_BLOB, ?LONG], [null, 1]),

During execution we do not need to send blob parameter value, because it already is in the server.

Cursor fetch

After a statement is prepared we can execute it under two modes. First kind of execution
is default and immediately returns a result set of the query. Second one
does not return a result set but create a cursor on the server side. To retrieve a data from
this cursor we can use fetch_statement command like this:

Command to execute the prepared statement in cursor mode and does not return any result but we need
get a metadata record for the following command. The next line is a fetch command that return 2
first rows from the server side cursor. A fetch command returns only binary packets of result set but
skips field metadata. So we have to pass metadata record as a parameter to fetch command
due to properly parse rows data. Result has to be: