 |
Qore FreetdsSqlUtil Module Reference
1.4.2
|
|
Qore FreetdsSqlUtil Module Reference
1.4.2
|
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
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.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).
offset and limit were not properly supported with MS SQL Server DBs (issue 4408)VARCHAR column to be created without a size, assume a default size (issue 4399)VARBINARY columns instead of VARBINARY(MAX) (issue 1852)