Qore sybase and freetds Modules  1.0.4.2
Qore sybase Module

Table of Contents

Introduction to the sybase and freetds Modules

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:

  • LGPL 2.1
  • MIT (see COPYING.MIT in the source distribution for more information)

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:

Datasource db("sybase:user/pass@dbname(utf8):host:4100");

Example of creating a connection with the freetds driver:

Datasource db("freetds:user/pass@dbname(utf8):host:1433");

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

SQL Statement Execution

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: :name) was used for the output parameters, otherwise the key names in the output will be an increasing integer starting with 0 (i.e. 0, 1, etc). If a count of affected rows is available, it's returned in the 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.

sybase and freetds Driver Options

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.

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

Dataource ds("sybase:user/pass@db{numeric-numbers,timezone=Europe/Vienna}");
DataourcePool dsp("freetds:user/pass@db%host.internal:1433{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.

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 values

Transaction Management

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

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

Binding and Types

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.

Binding by Value

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.

Binding by Value with the sybase Driver

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

Binding by Value with the freetds Driver

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

Database to Qore Type Mappings

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 sybase direct conversion
DATE Qore::Type::Date freetds currently returned as a string with MS SQL Server DBs
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 Procedures

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:

# note that if the output parameters are declared as @string and @int, the driver cannot
# return the parameter result hash with key names corresponding to the param names.
# ":string" and ":int" is qore placeholder syntax, consistent with other Qore DBI
# driver placeholder syntax
hash result = db.exec("declare @string varchar(40), @int int
exec get_values :string output, :int output");
Note
FreeTDS Limitations
The 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.

There are other issues with data types, character encoding, and more when using this driver; please see http://www.freetds.org for more information.

Release Notes

sybase Driver Version 1.0.4.2

  • fixed a build issue where the FREETDS environment variable was not respected as documented (issue 2017)
  • fixed a memory error that could result in a crash when a freetds connection is made with the incorrect tds version (issue 2689)
  • added support for the selectRow() method (issue 2690)

sybase Driver Version 1.0.4.1

New Features and Bug Fixes

  • updated SQLStatement::fetchColumns() to return an empty hash when no data is available (issue 1241)
  • fixed bugs handling SQLStatements used with this driver; the TDS protocol only allows one active query at a time, so any active statements have to be canceled before executing new SQL on the connection (issue 1248)
  • fixed a bug where reconnections did not actually check for a working connection (issue 1251)

sybase Driver Version 1.0.4

New Features and Bug Fixes

  • implemented support for the prepared statement API
  • implemented support for binding and retrieving "number" types
  • implemented support for the following DBI options:
    • "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.
  • fixed auto-reconnect functionality (issue 131)
  • removed trailing colon from error messages (issue 312)
  • fixed returning decimal values; they were returned with too-high precision numbers which caused comparisons with the original number to fail (issue 512))
  • 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
  • the default for the number options is now "optimal-numbers", meaning that NUMERIC or DECIMAL values are retrieved as integers if possible, otherwise an arbitrary-precision number type is returned
  • fixed varchar 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)
  • fixed renaming duplicate columns according to the standard xxx_n format (issue 830)

sybase Driver Version 1.0.3

New Features and Bug Fixes

  • implemented support for specifying the hostname and port in the datasource in order to override the interfaces file