Qore ODBC Module

Table of Contents

• Introduction to the ODBC Module
• Driver Options
  • Number Options
  • Bigint Options
  • Fractional Precision Option
  • Login Timeout and Connection Timeout Options
• Transaction Management
• Binding and Types
  • Timezone Support
  • Character Encoding Support
  • Oracle Support
  • Binding By Value
  • ODBC to Qore Type Mappings
• Stored Procedures
• Release Notes
  • odbc Driver Version 1.0

Contents of this documentation:

• Introduction to the ODBC Module
• Driver Options
  • Number Options
  • Bigint Options
  • Fractional Precision Option
  • Login Timeout and Connection Timeout Options
• Transaction Management
• Binding and Types
  • Timezone Support
  • Character Encoding Support
  • Oracle Support
  • Binding By Value
  • ODBC to Qore Type Mappings
• Stored Procedures
• Release Notes

Introduction to the ODBC Module

The odbc module provides an ODBC driver to Qore's DBI system, allowing Qore programs to access many different databases using the ODBC interface through the Datasource, DatasourcePool, and SQLStatement classes.

This module is released under the MIT license (see LICENSE.txt in the source distribution for more information). The module is tagged as such in the module's header (meaning it can be loaded unconditionally regardless of how the Qore library was initialized).

Example of creating an ODBC Datasource using DSN-less connection:

# MySQL
Datasource db = new Datasource(SQL::DSODBC, user, pass, NOTHING, "UTF8", NOTHING,
    NOTHING, {"DRIVER": "MySQL ODBC 5.3 Unicode Driver", "Server": "192.168.15.1", "Database": "omquser"});

# PostgreSQL
db = new Datasource(SQL::DSODBC, user, pass, NOTHING, "UTF8", NOTHING,
    NOTHING, {"DRIVER": "PostgreSQL Unicode", "Server": "192.168.15.1", "Database": "omquser", "fractional-precision": 6});

# Oracle
db = new Datasource(SQL::DSODBC, user, pass, NOTHING, NOTHING, NOTHING,
    NOTHING, {"DRIVER": "Oracle 12c ODBC driver", "Dbq": "192.168.15.1:1521/lorem", "bigint-string": True});

# MS-SQL
db = new Datasource(SQL::DSODBC, user, pass, NOTHING, NOTHING, NOTHING,
    NOTHING, {"DRIVER": "ODBC Driver 11 for SQL Server", "Server": "192.168.15.1", "Database": "omquser"});

Example of creating an ODBC Datasource using DSN connection:

Datasource db = new Datasource(SQL::DSODBC, user, pass, dsn, "UTF8");

This driver supports the following DBI capabilities:

• DBI_CAP_TIME_ZONE_SUPPORT
• DBI_CAP_TRANSACTION_MANAGEMENT
• DBI_CAP_CHARSET_SUPPORT
• DBI_CAP_LOB_SUPPORT
• DBI_CAP_STORED_PROCEDURES
• DBI_CAP_BIND_BY_VALUE
• DBI_CAP_HAS_ARRAY_BIND
• DBI_CAP_HAS_EXECRAW
• DBI_CAP_HAS_STATEMENT
• DBI_CAP_HAS_DESCRIBE
• DBI_CAP_HAS_SELECT_ROW
• DBI_CAP_HAS_NUMBER_SUPPORT
• DBI_CAP_HAS_OPTION_SUPPORT
• DBI_CAP_SERVER_TIME_ZONE

The driver employs efficient binary bindings for all non-text data types and fully supports multidimensional arrays when selecting and binding by value.

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

major_version * 1000000 + minor_version * 10000 + sub_version

For example: 5030025 => version 5.3.25

The Datasource::getClientVersion() method is implemented the same way as Datasource::getServerVersion() with the same return value format, and returns the version of the specific ODBC driver used for a connection.

For example: 3010672 => version 3.1.672

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

Driver Options

The odbc driver supports the following DBI options:

• "optimal-numbers": return received SQL_NUMERIC and SQL_DECIMAL values as integers if possible, if not return them as an arbitrary-precision numbers; this is the default
• "string-numbers": return received SQL_NUMERIC and SQL_DECIMAL values as strings (for backwards-compatibility)
• "numeric-numbers": return received SQL_NUMERIC and SQL_DECIMAL values as arbitrary-precision numbers (Qore number values)
• "bigint-native": bind Qore int values as native SQL_C_SBIGINT values to ODBC statements; this is the default
• "bigint-string": bind Qore int values as SQL_C_CHAR values (strings) to ODBC statements
• "fractional-precision": precision to use for the fractional seconds part of date, time, timestamp and interval values; in the range 1-9 (default is 3)
• "qore-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 timezone rules; this is useful if connecting to a database server in a different timezone; if this option is not set then the server's timezone is assumed to be the same as the client's timezone
• "login-timeout": timeout value in seconds used for logging in to the connection (connecting); possible values are integers from 0 up (default is 60); setting the timeout to 0 means to wait indefinitely
• "connection-timeout": timeout value in seconds used for the connection; possible values are integers from 0 up (default is 60); setting the timeout to 0 means to wait indefinitely

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

Datasource ds("odbc:user/pass@dsn{numeric-numbers}");
DatasourcePool dsp("odbc:user/pass@{optimal-numbers,DRIVER=MySQL ODBC 5.3 Unicode Driver,Server=192.168.20.1,Database=omquser}");

Options are stored separately for each connection.

Warning

In order to use int parameters in commands for the Oracle database, you either need to set the "bigint-string" option so that Qore int values are sent as strings instead of as 64bit int values (SQL_C_SBIGINT), or you have to bind integer values using odbc_bind() and the odbc_bind() constants ODBCT_SLONG, ODBCT_ULONG, ODBCT_SSHORT, ODBCT_USHORT, ODBCT_STINYINT or ODBCT_UTINYINT, and make sure that the integers fit the limits imposed by these types.

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.

Bigint Options

The bigint options ("bigint-native" and "bigint-string") are mutually-exclusive; setting one automatically disables the other. 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 "bigint-native".

Fractional Precision Option

This option sets how big fractional seconds precision will be used when binding any date, time, timestamp and/or interval parameter with seconds part. The default value is 3. Possible values are 1 to 9.

Login Timeout and Connection Timeout Options

Login timeout option is used to set for how long the ODBC driver should wait during the initial connection phase. Connection timeout option is used to set the timeout for when the connection is open and it stops responding.

Both of the options have a default value of 60 and can be set to numbers ranging from 0 up. A value of 0 means to wait indefinitely (never time out). Both Qore int values and integers in the form of strings can be used to set these options.

Transaction Management

When using ODBC, all the transactions are started implicitly by executing any command, therefore it is not necessary to do this manually.

An exception in a Datasource::exec() method as the first statement of a transaction will cause an automatic implicit "rollback" to be executed to maintain compatibility with other Qore DBI drivers.

Binding and Types

Apart from simply passing values for binding you can also use the odbc_bind() function to specify the type to bind. See Binding By Value for more information about how types are bound when binding by value with this driver.

For NUMERIC columns, the d specification in the query string can be used to substitute a numeric value or a literal null directly in the query string, providing the same effect as a bind by value in a very convenient form for the programmer. For example:

# the %d will be substituted with the value of \c id directly in the query text
# or with a "null" in case \c id has no value (or is NULL)
*hash results = db.select("select * from table where id = %d", id);

Note that binding by placeholder is not required or supported by this driver; Qore DBI placeholder buffer specifications are ignored when the ODBC driver is used.

When binding arrays, all data types in the Qore list must be identical.

When retrieving ODBC data, ODBC types are converted to Qore types as per ODBC to Qore Type Mappings.

Timezone 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 "qore-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.

Character Encoding Support

ODBC module supports using all of Qore's charsets to transfer data between Qore and the databases.

If no encoding is set when creating an ODBC datasource, the module converts all outbound text data to UTF-16 strings and binds them as SQL_C_WCHAR datatype. Incoming SQL_WCHAR data is received as UTF-16, while incoming SQL_CHAR data is assumed to be in Qore's default encoding.

If encoding is set when creating an ODBC datasource, the module converts all outbound text data to it and binds it as either SQL_C_CHAR or SQL_C_WCHAR datatypes depending on the specific encoding. Incoming SQL_WCHAR data is received as UTF-16, while incoming SQL_CHAR data is assumed to be of the set encoding.

Oracle Support

User of this module can use it to connect to Oracle databases. There is one issue however which is connected to this. This module binds Qore int values as SQL_C_SBIGINT datatype by default. This does not work with Oracle databases though. To overcome this problem, there are two options:

1) Use the "bigint-string" option when opening an ODBC connection. This causes all Qore integer values to be bound and sent to the DB as strings. It is less efficient than binding as bigints but works.

2) Make sure that the integer values fit the respective numerical limits and use odbc_bind() to bind the integer values as one of the following ODBC datatypes: SQL_C_SLONG, SQL_C_ULONG, SQL_C_SSHORT, SQL_C_USHORT, SQL_C_STINYINT or SQL_C_UTINYINT.

See also

Type Constants for odbc_bind()

Binding By Value

The following table gives data mapping when binding qore types by value.

QoreType	ODBC C Type	ODBC SQL Type	Description
int	SQL_C_SBIGINT, SQL_C_CHAR	SQL_BIGINT	Converting from a BIGINT type to smaller integer types is done automatically by the specific ODBC DB driver. Qore ints can be bound either as SQL_C_SBIGINT values (default) or as character strings (needed for Oracle database).
float	SQL_C_DOUBLE	SQL_DOUBLE	Qore float data is converted directly to ODBC DOUBLE data.
number	SQL_C_CHAR	SQL_CHAR	Qore number data is converted to strings before sending.
string	SQL_C_CHAR, SQL_C_WCHAR	SQL_CHAR, SQL_WCHAR	The character encoding is converted to the encoding specified for the connection if necessary and bound as SQL_CHAR values; if no encoding is specified, strings are converted to UTF-16 instead and bound as SQL_WCHAR strings
bool	SQL_C_BIT	SQL_CHAR	Qore boolean values are sourced as BIT values and sent as CHAR values.
date	SQL_C_INTERVAL_DAY_TO_SECOND, SQL_C_TYPE_TIMESTAMP	SQL_INTERVAL_DAY_TO_SECOND, SQL_TYPE_TIMESTAMP	Relative date/time values are bound as interval data, absolute date/time values are bound as timestamp data
binary	SQL_C_BINARY	SQL_BINARY	Qore binary data is bound directly as binary data.

ODBC to Qore Type Mappings

ODBC Type	Qore Type	Notes
SQL_BIT	bool	direct conversion
SQL_BINARY	binary	direct conversion
SQL_VARBINARY	binary	direct conversion
SQL_LONGVARBINARY	binary	direct conversion
SQL_CHAR	string	if character encoding is specified, the strings are received and set as Qore strings with the specified encoding; if no encoding is specified, the strings are received as SQL_WCHAR strings instead
SQL_VARCHAR	string	direct conversion
SQL_LONGVARCHAR	string	direct conversion
SQL_WCHAR	string	direct conversion
SQL_WVARCHAR	string	direct conversion
SQL_WLONGVARCHAR	string	direct conversion
SQL_INTEGER	int	direct conversion
SQL_BIGINT	int	direct conversion
SQL_SMALLINT	int	direct conversion
SQL_TINYINT	int	direct conversion
SQL_FLOAT	float	direct conversion
SQL_DOUBLE	float	direct conversion
SQL_REAL	float	direct conversion
SQL_NUMERIC	int, string or number	depends on driver options
SQL_DECIMAL	int, string or number	depends on driver options
SQL_TYPE_DATE	date (absolute)	direct conversion
SQL_TYPE_TIME	date (relative)	direct conversion
SQL_TYPE_TIMESTAMP	date (absolute)	direct conversion
SQL_TYPE_UTCDATETIME		not supported
SQL_TYPE_UTCTIME		not supported
SQL_INTERVAL_MONTH	date(relative)	direct conversion
SQL_INTERVAL_YEAR	date(relative)	direct conversion
SQL_INTERVAL_YEAR_TO_MONTH	date(relative)	direct conversion
SQL_INTERVAL_DAY	date(relative)	direct conversion
SQL_INTERVAL_HOUR	date(relative)	direct conversion
SQL_INTERVAL_MINUTE	date(relative)	direct conversion
SQL_INTERVAL_SECOND	date(relative)	direct conversion
SQL_INTERVAL_DAY_TO_HOUR	date(relative)	direct conversion
SQL_INTERVAL_DAY_TO_MINUTE	date(relative)	direct conversion
SQL_INTERVAL_DAY_TO_SECOND	date(relative)	direct conversion
SQL_INTERVAL_HOUR_TO_MINUTE	date(relative)	direct conversion
SQL_INTERVAL_HOUR_TO_SECOND	date(relative)	direct conversion
SQL_INTERVAL_MINUTE_TO_SECOND	date(relative)	direct conversion
SQL_GUID	string	format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (canonical UUID form)

Stored Procedures

Stored procedure execution is supported; the following is an example of a stored procedure call:

Example PostgreSQL PLPG/SQL function:

create or replace function int_test(val int4) returns int4 as $$
begin
    return val * 2;
end;
$$ language plodbc;

Example Qore code to call this function:

any result = db.exec("select int_test(%v)", 11);
printf("%N\n", result);

Resulting in:

hash: (1 member)
  int_test : 22

Release Notes

odbc Driver Version 1.0

• initial release