 |
Qore sybase and freetds Modules 1.2
|
|
Qore sybase and freetds Modules 1.2
|
Both the sybase and freetds modules are built from the same sources; this documentation will describe both modules. They share most of their functionality, but there are differences, both depending on the client libraries used, and according to the database server connected.
The sybase and freetds modules provide drivers to Qore's DBI system, allowing Qore programs to access Sybase (both modules) databases and Microsoft SQL Server (the freetds module) through the Datasource, DatasourcePool, and SQLStatement classes.
Both modules are released under a choice of two licenses:
Both modules are tagged as such in the modules' headers (meaning they can be loaded unconditionally regardless of how the Qore library was initialized).
Example of creating a connection with the sybase driver:
Example of creating a connection with the freetds driver:
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_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 DBI_CAP_AUTORECONNECT The driver employs efficient binary bindings for all non-text data types.
The Datasource::getServerVersion() and Datasource::getClientVersion() methods are implemented for both drivers. Datasource::getServerVersion() returns a string giving server information similar to the following:
Adaptive Server Enterprise/15.0.1/EBF 13827/P/x86_64/Enterprise Linux/ase1501/2379/64-bit/OPT/Mon Aug 14 22:15:25 2006
Or for freetds with MS SQL Server:
Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64) May 27 2016 15:06:08 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )
The Datasource::getClientVersion() returns a string giving version information. For example, for freetds:
freetds v1.0 (threadsafe, default tds version=auto)
Or for Sybase:
Sybase Client-Library/15.0/P-EBF13571 ESD #4/DRV.15.0.1/Linux x86_64/Linux 2.4.21-27.ELsmp x86_64/BUILD1500-073/OPT/Mon May 15 03:18:58 2006
This driver allows all types of statements to be executed through Datasource::select(), Datasource::selectRows(), and Datasource::exec() (and related methods), however rules about Datasource locking or DatasourcePool allocations still apply as usual. If a stored procedure is executed with Datasource::selectRows(), then any query results returned will be returned in either a hash format (if only one row was returned), or as a list of hashes (for multiple rows).
The TDS protocol only supports a single active query on a connection at one time, therefore there can be only one SQLStatement object active at any one time on a single server connection; if additional SQL commands or a new SQLStatement is executed while another SQLStatement is active, the existing statement will be canceled before starting the new statement.
Unlike many other Qore DBI drivers, this driver allows and can return results from multiple select statements issued with a single call. Additionally, it's possible for a stored procedure to return output paramters as well as one or more result sets from select statements. The output format for commands like this differs from standard Qore DBI formats as follows:
| Query Type | Result Format |
| Multiple Selects | A hash is returned with a key giving the results from each select statement. The key names have the format query#, where # is an increasing integer starting with 0. |
| Return Parameters Only | A hash is returned with keys giving the output parameter values, as long as the placeholder format (ex: rowcount key. |
| Return Parameters and Result Set(s) | As above, but with an addition key: query, giving the result set. If multiple result sets are returned, then the query key will be a hash with query# keys giving the results for each query in order. |
The sybase and freetds drivers 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 timezone."optimized-date-binds": when set, date/time values are bound with full resolution including microseconds, however this will cause any operations with date/time values with microseconds bound for DATETIME columns to fail; if this is not set, then date/time values are bound with an approach that works for all columns but gives a maximum of 1/300 second resolutionOptions can be set in the Qore::SQL::Datasource or Qore::SQL::DatasourcePool constructors as in the following examples:
Options are stored separately for each connection.
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.
NUMERIC or DECIMAL output values:
"optimal-numbers": return numeric values as an integer if possible, if not as an arbitrary-precision number"string-numbers": return numeric values as strings (for backwards-compatibility)"numeric-numbers": return numeric values as arbitrary-precision number valuesTransaction management is supported by enabling chained transaction mode on each connection. In autocommit mode in the Qore::SQL::Datasource class, Qore executes a commit after every request to the server.
The transaction isolation level is left at the default, 1, "read committed", which is the default for all Qore drivers as well.
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 sybase and freetds 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.
Note that binding by placeholder is not required or supported by this driver as values are returned directly; Qore DBI placeholder buffer specifications are ignored when the sybase and freetds drivers are used.
When retrieving data, data types are converted to Qore types as per Database to Qore Type Mappings.
DATETIME and TIME data types have a resolution of 1/300th of a second. Qore uses floating-point operations to convert to or from microseconds when binding and retrieving data and rounds to the nearest integer to minimize conversion errors.
The following table gives data mapping when binding qore types by value with the sybase driver.
| QoreType | sybase Type | Description |
Type::Int | CS_BIGINT_TYPE | Sybase's integer type is only 32 bits, integers greater than 2147483647 bound to an INT column will only have their lower 32 bits saved to Sybase. |
Type::Float | CS_FLOAT_TYPE | direct conversion |
Type::Boolean | CS_BIT_TYPE | True = 1, False = 0 |
Type::String | CS_CHAR_TYPE | direct conversion |
Type::Date | CS_DATETIME_TYPE | milliseconds are rounded to 1/300 second values |
Type::Binary | CS_BINARY_TYPE | direct conversion |
The following table gives data mapping when binding qore types by value with the freetds driver.
| QoreType | freetds Type | Description |
Type::Int | CS_BIGINT_TYPE, CS_INT_TYPE or CS_FLOAT_TYPE | Integer type is only 32-bits, integers greater than 2147483647 bound to an INT column will only have their lower 32 bits saved to the database. If the ~FreeTDS version used to compile this driver does not support CS_BIGINT_TYPE, then integers 32 bits or smaller will be bound as CS_INT_TYPE, and larger integers will be bound as CS_FLOAT_TYPE. |
Type::Float | CS_FLOAT_TYPE | direct conversion |
Type::Boolean | CS_BIT_TYPE | True = 1, False = 0 |
Type::String | CS_CHAR_TYPE | direct conversion |
Type::Date | CS_DATETIME_TYPE | milliseconds are rounded to 1/300 second values |
Type::Binary | CS_BINARY_TYPE | direct conversion |
| Data Type | Qore Type | Driver | Notes |
TINYINT | Qore::Type::Int | both | direct conversion |
SMALLINT | Qore::Type::Int | both | direct conversion |
INT | Qore::Type::Int | both | direct conversion |
BIGINT | Qore::Type::Int | both | direct conversion |
DECIMAL, NUMERIC | depends on options | both | see Number Options |
FLOAT | Qore::Type::Float | both | direct conversion |
REAL | Qore::Type::Float | both | direct conversion |
MONEY | Qore::Type::Float | both | direct conversion |
SMALLMONEY | Qore::Type::Float | both | direct conversion |
DATETIME | Qore::Type::Date | both | 1/300 second values are converted and rounded to the nearest microsecond |
SMALLDATETIME | Qore::Type::Date | both | direct conversion |
DATE | Qore::Type::Date | both | direct conversion |
TIME | Qore::Type::Date | sybase | Date portion is set to '1970-01-01'; 1/300 second values are converted and rounded to the nearest microsecond |
TIME | Qore::Type::Date | freetds | currently returned as a string with MS SQL Server DBs |
CHAR | Qore::Type::String | both | trailing blanks are removed |
VARCHAR | Qore::Type::String | both | direct conversion |
UNICHAR | Qore::Type::String | sybase | trailing blanks are removed |
UNIVARCHAR | Qore::Type::String | sybase | trailing blanks are removed |
TEXT | Qore::Type::String | both | direct conversion |
UNITEXT | Qore::Type::Binary | both | this data is returned as type IMAGE from the dataserver, so a binary object is returned by the driver |
BINARY | Qore::Type::Binary | both | direct conversion |
VARBINARY | Qore::Type::Binary | both | direct conversion |
IMAGE | Qore::Type::Binary | both | direct conversion |
Stored procedure execution is supported, the following is an example of a stored procedure call with output parameters:
create procedure get_values @string varchar(80) output, @int int output as select @string = 'hello there' select @int = 150 commit -- to maintain transaction count
Qore code to call this procedure:
freetds driver is not able to retrieve output parameter values when executing queries on MS SQL Server 2000 or above (tds version 5.0 or above), due to the fact that newer MS SQL Server dataservers no longer return output parameter information through the TDS protocol as with Sybase and previous versions of SQL Server. A future version of this driver should offer an alternative Datasource method allowing explicit stored procedure execution through ct-lib RPC functions, which will allow output parameters to be returned.DATETIME2 column data (issue 4401)"optimized-date-binds" optionBIGDATETIME, BIGDATE, and BIGTIME columns (issue 3951)FREETDS environment variable was not respected as documented (issue 2017)tds version (issue 2689)New Features and Bug Fixes
New Features and Bug Fixes
"optimal-numbers": return NUMERIC or DECIMAL types as an integer if possible, if not as an arbitrary-precision number"string-numbers": return NUMERIC or DECIMAL types as strings (for backwards-compatibility)"numeric-numbers": return NUMERIC or DECIMAL 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."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"optimal-numbers", meaning that NUMERIC or DECIMAL values are retrieved as integers if possible, otherwise an arbitrary-precision number type is returnedvarchar handling with freetds; freetds processes bind arguments differently than Sybase's ctlib and strings were sent with an extra space on the end that was stripped from the string when the value was returned from the DB (issue 563)New Features and Bug Fixes