 |
Qore odbc Module 1.2.0
|
|
Qore odbc Module 1.2.0
|
Contents of this documentation:
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:
Example of creating an ODBC Datasource using DSN connection:
The ODBC connection string can be given as the conn option; if the datasource has a username, it is added as both USER and UID in the connection string, whereas any password is added as both PASSWORD and PWD in the connection string. To avoid this, use datasource connection parameters without a username or password and add the required connection parameters directly to the conn option separated by semicolons (";") as in the above examples.
If a database name if provided in the datasource connection arguments, it is added as the DSN option in the ODBC connection string.
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.
The odbc driver supports the following DBI options:
"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"conn": the ODBC connection string (ex: "driver=xyz;server=abc" etc)"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)"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"numeric-numbers": return received SQL_NUMERIC and SQL_DECIMAL values as arbitrary-precision numbers (Qore number values)"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"preserve-case": the case of column names will be preserved; by default this is disabled and column names are converted to lower-case"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"string-numbers": return received SQL_NUMERIC and SQL_DECIMAL values as strings (for backwards- compatibility)Options can be set in the Datasource or DatasourcePool constructors as in the following examples:
Options are stored separately for each connection.
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.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.
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".
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 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.
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.
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:
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.
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.
The odbc module uses the datasource encoding to transfer strings to and from databases in all cases. Conversions are made to the datasource encoding for strings inbound to the DB, if necessary, and all string data retrieved from the DB is tagged with the datasource's encoding as well.
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.
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 UTF-8 if necessary and bound as SQL_CHAR values |
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 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 | direct conversion as UTF-8-encoded strings |
SQL_VARCHAR | string | direct conversion as UTF-8-encoded strings |
SQL_LONGVARCHAR | string | direct conversion as UTF-8-encoded strings |
SQL_WCHAR | string | direct conversion as UTF-8-encoded strings |
SQL_WVARCHAR | string | direct conversion as UTF-8-encoded strings |
SQL_WLONGVARCHAR | string | direct conversion as UTF-8-encoded strings |
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 procedure execution is supported; the following is an example of a stored procedure call:
Example PostgreSQL PLPG/SQL function:
Example Qore code to call this function:
Resulting in:
hash: (1 member) int_test : 22
Datasource::getDriverRealName() (issue 4690)VARCHAR values (issue 4681)qpp from QoreSQLStatement class (issue 4616)conn option would be used to build the connection, but the value was not returned as an option value (issue 4613)CHAR column values (issue 4508)