Qore FreetdsSqlUtil Module Reference  1.4.2

FreetdsSqlUtil Module Introduction

The FreetdsSqlUtil module provides a high level API for working with database objects through the freetds driver.

Usually this module is loaded on demand from the SqlUtil module, however to use this module directly, use "%requires FreetdsSqlUtil" in your code.

All the public symbols in the module are defined in the FreetdsSqlUtil namespace

Note
This module requires the freetds binary module for communication with MS SQL Server and Sybase databases

SQL Operations with the FreeTDS Driver

Queries are limited in the number of arguments in the "in" operator depending on the database server; use a join with a temporary table to select a large number of values known at runtime instead of using the "in" operator (see SqlUtil::op_in()).

FreetdsSqlUtil supports the following additional select option when working with MS SQL Server databases:

  • "tablehint": A single string or a list of strings with the following possible values:
    • "index(...)": Specifies the name or ID of the indexes to be used by SQL Server when processing the statement. Only one index hint per table can be specified. If a clustered index exists, "index(0)" forces a clustered index scan and "index(1)" forces a clustered index scan or seek. If no clustered index exists, "index(0)" forces a table scan and "index(1)" is interpreted as an error. See SQL Server docs for more information.
    • "fastfirstrow": Equivalent to SQL Server option "(FAST 1)"
    • "holdlock": Makes shared locks more restrictive by holding them until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The scan is performed with the same semantics as a transaction running at the "SERIALIZABLE" isolation level. equivalent to "serializable"
    • "nolock": Equivalent to "readuncommitted"
    • "paglock": Takes shared page locks where a single shared table lock is normally taken
    • "readcommitted": Specifies that a scan is performed with the same locking semantics as a transaction running at "READ COMMITTED" isolation level
    • "readpast": Specifies that locked rows are skipped (read past)
    • "readuncommitted": Specifies that dirty reads are allowed
    • "repeatableread": Specifies that a scan is performed with the same locking semantics as a transaction running at "REPEATABLE READ" isolation level
    • "rowlock": Specifies that a shared row lock is taken when a single shared page or table lock is normally taken
    • "serializable": Makes shared locks more restrictive by holding them until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The scan is performed with the same semantics as a transaction running at the "SERIALIZABLE" isolation level. Equivalent to "holdlock".
    • "tablock": Specifies that a shared lock is taken on the table held until the end-of-statement. If "holdlock" is also specified, the shared table lock is held until the end of the transaction.
    • "tablockx": Specifies that an exclusive lock is taken on the table held until the end-of-statement or end-of-transaction.
    • "updlock": Specifies that update locks instead of shared locks are taken while reading the table, and that they are held until the end-of-statement or end-of-transaction.
    • "xlock": Specifies that exclusive locks should be taken and held until the end of transaction on all data processed by the statement. If specified with "paglock" or "tablock", the exclusive locks apply to the appropriate level of granularity.
Note
  • From the SQL Server docs: The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.
  • For more details on the above hints as well as information on which hints are supported for your particular version of SQL Server, see the SQL Server docs

Schema Management with the FreeTDS Driver

Type Mapping

Column types are mapped from Qore types as follows:

FreeTDS Column Type Mappings

Generic Type Name FreeTDS Type Used
float float
integer bigint
number numeric
string varchar
date datetime
binary varbinary
bool tinyint
SqlUtil::BLOB varbinary
SqlUtil::CHAR char
SqlUtil::CLOB text
SqlUtil::NUMERIC numeric
SqlUtil::VARCHAR varchar

To use other types, use the "native_type" column description option with the native FreeTDS type name instead (under the "driver" and "freetds" keys for schemas supporting multiple databases).

FreetdsSqlUtil v1.4.2

  • fixed a bug where offset and limit were not properly supported with MS SQL Server DBs (issue 4408)
  • fixed a bug where date/time values in other time zones were not bound correctly (issue 4403)
  • fixed a bug handling transaction savepoints (issue 4402)
  • allow a VARCHAR column to be created without a size, assume a default size (issue 4399)

FreetdsSqlUtil v1.4.2

  • added APIs to enable duplicate record creation errors to be detected (issue 4308)

FreetdsSqlUtil v1.4

FreetdsSqlUtil v1.3.1

  • fixed quoting of reserved words in column names in table alignment (issue 3400)

FreetdsSqlUtil v1.3

FreetdsSqlUtil v1.2.1

  • implemented support for custom column operators (issue 2314)

FreetdsSqlUtil v1.2

  • fixed a bug where SqlUtil::BLOB columns were created as VARBINARY columns instead of VARBINARY(MAX) (issue 1852)
  • implemented cop_trunc_date() operator (issue 2032)

FreetdsSqlUtil v1.1

  • implemented the force option (i.e. cascade) for dropping code objects (issue 1314)

FreetdsSqlUtil v1.0

  • initial release