Qore mysql Module

Table of Contents

• Introduction to the mysql Module
• Driver Options
  • Number Options
• Transaction Management
• Binding and Types
  • Time Zone Support
  • MySQL to Qore Type Mappings
  • Binding By Value
• Stored Procedures
• Release Notes
  • mysql Driver Version 2.0.2
  • mysql Driver Version 2.0.1
  • mysql Driver Version 2.0

Contents of this documentation:

• Introduction to the mysql Module
• Driver Options
• Transaction Management
• Binding and Types
• Stored Procedures
• Release Notes

Introduction 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:

• "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.

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.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

New Features and Bug Fixes

• implemented support for the selectRow DBI method
• implemented support for the prepared statement API; the driver uses MySQL's prepared statement API internall to bind to Qore's DBI prepared statement API
• implemented support for binding and retrieving "number" types
• implemented support for the following DBI options:
  • "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.
• the default for the number options (see Number Options) is now "optimal-numbers" - meaning that NUMERIC or DECIMAL values are retrieved as int if possible, otherwise a number type is returned.
  For backwards-compatibility, set the "string-numbers" option to return NUMERIC or DECIMAL values as strings.