Introducion to the mysql Module

The mysql module provides a MySQL driver to Qore's DBI system, allowing Qore programs to access MySQL databases through the Datasource, DatasourcePool, and SQLStatement classes.

This module is released under the GPL 2 and is tagged as such in the module's header (meaning it can only be loaded if the Qore library was initialized from a GPL program as well).

Example of creating a MySQL Datasource:

Datasource db(SQL::DSMySQL, user, pass, db, encoding, hostname, port);

This driver supports the following DBI capabilities when compiled against MySQL 5 or better:

DBI_CAP_TRANSACTION_MANAGEMENT
DBI_CAP_CHARSET_SUPPORT
DBI_CAP_LOB_SUPPORT
DBI_CAP_STORED_PROCEDURES
DBI_CAP_BIND_BY_VALUE
DBI_CAP_HAS_EXECRAW
DBI_CAP_HAS_STATEMENT
DBI_CAP_HAS_SELECT_ROW
DBI_CAP_HAS_NUMBER_SUPPORT
DBI_CAP_HAS_OPTION_SUPPORT
DBI_CAP_SERVER_TIME_ZONE

The driver uses MySQL's prepared statement interface when it is available (must be compiled with MySQL 4 or better).

If a connection times out while not in a transaction, the connection will be automatically reconnected.

As with all Qore DBI drivers, column names are manually converted to lower case if necessary before being returned to the user.

The Datasource::getServerVersion() and Datasource::getClientVersion() methods are implemented for this driver and return an integer giving the major, minor, and sub version numbers in a single integer according to the following formula:

major_version * 10000 + minor_version * 100 + sub_version

(For example: 50027 = 5.0.27).

Like all Qore components, the MySQL DBI driver is thread-safe.

Driver Options

When compiled again Qore 0.8.6+ the mysql driver support the following DBI options:

"collation": sets the client-side per-connection collation for string values
"optimal-numbers": return numeric types as an integer if possible, if not as an arbitrary-precision number
"string-numbers": return numeric types as strings (for backwards-compatibility)
"numeric-numbers": return numeric types as arbitrary-precision number values
"timezone": accepts a string argument that can be either a region name (ex: "Europe/Prague") or a UTC offset (ex: "+01:00") to set the server's time zone rules; this is useful if connecting to a database server in a different time zone. If this option is not set then the server's time zone is assumed to be the same as the client's time zone; see Time Zone Support.

Note: if the "collation" option is set, the collation is set automatically for each connection to the given value

Options can be set in the Datasource or DatasourcePool constructors as in the following examples:

Datasource ds("mysql:user/pass@db{numeric-numbers,timezone=Europe/Vienna}");

DatasourcePool dsp("mysql:user/pass@db%host.internal:3306{optimal-numbers}");

Options are stored separately for each connection.

Number Options

The number options ("optimal-numbers", "string-numbers", and "numeric-numbers") are all mutually-exclusive; setting one automatically disables the others. These options also ignore their arguments; the last one set takes effect (also note that setting it with any value sets the option, even False).

The default if no other option is explicitly set is "optimal-numbers". Note that this represents a change from previous versions where NUMERIC or DECIMAL values were returned as strings in order to avoid the loss of information. To set the old behavior, set the "string-numbers" option when creating the Datasource or DatasourcePool object.

Transaction Management

This driver sets new connections to use transaction isolation level read committed> explicitly for every new connection to conform to Qore's default transaction management style.

The transaction level can be changed manually, however, using the Datasource::exec() method.

Aditionally the CLIENT_FOUND_ROWS option is set for each connection, so Datasource::exec() will return the number of rows affected by insert/update queries, etc in a manner consistent with other Qore DBI drivers (if this option is not set and a single row is updated with the same values, 0 would be returned instead of 1 which would not confirm to the behavior of other Qore DBI drivers).

Binding and Types

See MySQL to Qore Type Mappings for information on how MySQL types are converted to Qore data types, and Binding By Value for information on how Qore data types are converted to MySQL types when binding by value.

Note that is is not necessary to give buffer types when binding placeholder values; the mysql driver will determine the needed type in advance and create the placeholder buffer automatically.

Qore placeholder buffer specifications can still be used to retrieve output variables as follows however:

date now = db.selectRow("select current_timestamp into :time").time;

Time Zone Support

The driver now sets the server's time zone rules when the connection is established; this is taken from the current time zone settings of the calling Program object and can also be overridden/changed by setting the "timezone" driver option (see Driver Options).

All date/time values bound by value are converted to the server's time zone before binding to ensure that date/time values are stored correctly in the server.

When selecting date/time values, the values returned are tagged with the server's time zone.

Note that the above rules are applied when the current Program's time zone settings are different than the connection's time zone settings at the time when the write operation (bind) read operation (select) is performed. This is meant to provide consistent support to connecting to a database server in a different time zone.

MySQL to Qore Type Mappings

MySQL Type	Qore Type	Notes
`TINYINT`	`Type::Int`	direct conversion (note that `BOOL` and `BOOLEAN` are synonyms for this type in ~MySQL)
`SMALLINT`	`Type::Int`	direct conversion
`MEDIUMINT`	`Type::Int`	direct conversion
`INT`	`Type::Int`	direct conversion
`BIGINT`	`Type::Int`	direct conversion
`YEAR`	`Type::Int`	direct conversion
`DECIMAL`	`Type::Int`, `Type::String`, or `Type::Number`	depends on driver options
`FLOAT`	`Type::Float`	direct conversion
`DOUBLE`	`Type::Float`	direct conversion
`DATETIME`	`Type::Date`	direct conversion
`DATE`	`Type::Date`	direct conversion
`TIME`	`Type::Date`	direct conversion; the date portion will be set to January 1, 1970 (start of Qore's 64-bit epoch)
`TIMESTAMP`	`Type::Date`	direct conversion
`BLOB`	`Type::Binary`	direct conversion
`TINYBLOB`	`Type::Binary`	direct conversion
`MEDIUMBLOB`	`Type::Binary`	direct conversion
`BINARY`	`Type::Binary`	direct conversion
`VARBINARY`	`Type::Binary`	direct conversion

Binding By Value

|QoreType

MySQL Type	Description
`Type::String`	`MYSQL_TYPE_STRING`	string data is mapped directly after any character encoding conversions (if necessary)
`Type::Date`	`MYSQL_TYPE_DATETIME`	direct mapping of data
`Type::Binary`	`MYSQL_TYPE_BLOB`	direct mapping of data
`Type::Boolean`	`MYSQL_TYPE_LONG`	True=1, False=0
`Type::Int`	`MYSQL_TYPE_LONGLONG`	direct mapping of data
`Type::Float`	`MYSQL_TYPE_DOUBLE`	direct mapping of data
`Type::Number`	`MYSQL_TYPE_STRING`	direct conversion to a string (this is the only input type MySQL provides for numeric/decimal values)

Stored Procedures

With appropriate versions of MySQL (version 5+), stored procedure execution is supported; the following is an example of a stored procedure call:

Example MySQL function:

delimiter //
create procedure get_family (in id int, out fname varchar(32))
begin
   select name into fname from family where family_id = id;
end;
//

Qore code to call this function:

hash result = db.exec("call get_family(%v, :name)", 1);

printf("%N\n", result);

Resulting in:

hash: (1 member)

name : "Smith"

Release Notes

mysql Driver Version 2.1

updated build to use the latest Qore APIs

mysql Driver Version 2.0.2.1

New Features and Bug Fixes

fixed bugs selecting date/time values with sub-second precision (issue 3949)
implemented the "collation" option to allow the client-side per-connection collation value to be set (issue 3753)
updated SQLStatement::fetchColumns() to return an empty hash when no data is available (issue 1241)
fixed a bug where DBI-SELECT-ROW-ERROR exceptions were being raised as MYSQL-SELECT-ROW-ERROR exceptions (issue 2542)
fixed selectRow() to properly report errors in fetching data (issue 3746)

mysql Driver Version 2.0.2

fixed formatting bugs in invalid value specification error messages
updated configure to find and compile with MariaDB and Percona includes and libs
placeholders and quotes in SQL comments are ignored
fixed time column retrieval; the date component is now set to 1970-01-01
fixed binary and varbinary column handling
fixed the "optimal-numbers" option with decimal values with decimal points (issue 542)
fixed returning result sets with duplicate column names (issue 832)

mysql Driver Version 2.0.1

Bug Fix

fixed a bug in returning numeric values where numbers with a decimal component were being returned as integers when the default option "optimal-numbers" was enabled

mysql Driver Version 2.0