FreeTDS (historical revision 23644) - The CHICKEN Scheme wiki

You are looking at historical revision 23644 of this page. It may differ significantly from its current revision.

Bindings for the FreeTDS library (specifically the CTLib API).

Author

Written for Response Genetics, Inc. by Peter Danenberg, with contributions by Peter Bex.

Repository

The FreeTDS egg is maintained in a Github repository.

This means that while the distributed egg repository system is still being evaluated you can install it by adding a henrietta-cache server entry to your setup.defaults file.

Requirements

You will also need to have FreeTDS installed, including development headers.

This egg has been tested only against FreeTDS, but since it uses nothing freetds-specific (yet), other implementations of ctlib (Sybase's Open Client Library) might also work.

Documentation

This extension allows you to connect to Microsoft SQL or Sybase database servers.

Please consider using this library as an opportunity to break out of your vendor lock-in and convert your data to a truly free RDBMS. There is plenty of choice!

Note: The implementation currently uses the synchronous ctlib API, so it's not recommended to use this in highly concurrent programs as each operation will block all other threads.

Connection management

[procedure] (make-connection HOST USERNAME PASSWORD [DATABASE-NAME])

Opens a connection to the database on the host in the HOSTNAME string. This string may optionally contain a colon followed by a port number if it's not the default (1434?). USERNAME and PASSWORD must be strings which are to be used for logging in.

The string DATABASE-NAME is optional; if it is not supplied it will use the default database. You can easily switch databases later by running a "USE dbname" query.

The return value is a connection-object.

Note: You cannot use the same connection from multiple threads. If you need to talk to the same server from different threads, simply create a second connection.

[procedure] (call-with-connection HOST USERNAME PASSWORD [DATABASE-NAME] PROCEDURE)

A convenience wrapper which opens a connection and invokes PROCEDURE with the connection object as an argument, analogously to call-with-input-file or call-with-output-file.

This closes the connection when control leaves PROCEDURE's dynamic extent and re-opens it when it is re-entered (more importantly, this ensures the connection is closed when an unhandled exception occurs).

[procedure] (connection-close CONNECTION)

Closes the CONNECTION and disconnects from the server.

[procedure] (connection-open? CONNECTION)

Returns #t when the CONNECTION is still open, #f if it was closed.

[procedure] (connection-reset! CONNECTION)

Resets the connection by canceling all currently open commands (queries). You can use this on the REPL when something got stuck (but likely this will only happen if there's a bug in this library).

[procedure] (connection? OBJECT)

Returns true if OBJECT is a FreeTDS connection-object.

Query procedures

[procedure] (send-query CONN QUERY . PARAMS)

Execute QUERY, which is a string containing one SQL statement. CONN indicates the connection on which to execute the query, and PARAMS is an arbitrary number of optional arguments indicating positional parameters represented by ? markers in QUERY. Each marker must have a corresponding PARAM.

This returns a result object (see below).

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
  (result-values (send-query conn "SELECT ?, 2" "hello")))
 => (("hello" 2))

[procedure] (send-query* CONN QUERY PARAMS)

An alternative of the send-query procedure which is not limited in number of params by Chicken's argument count limit because PARAMS is passed in as one argument in the form of a list.

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
  (result-values (send-query conn "SELECT ?, 2" '("hello"))))
 => (("hello" 2))

[procedure] (call-with-result-set CONN QUERY [PARAM0 [PARAM1 ... ]] PROCEDURE)

A convenience wrapper which issues a query and invokes PROCEDURE with the result object as an argument, analogously to call-with-input-file or call-with-output-file.

This cleans up the result set when control leaves PROCEDURE's dynamic extent (more importantly, this ensures the result is cleaned up when an unhandled exception occurs).

Important When the dynamic extent is re-entered, the result object will be invalid because it was cleaned up. The query is not resent.

High-level API

Usually you will use only these procedures to process result sets, but you can fall back to (or even mix with) the low-level API if you need to do custom things. This API is a straight copy of the postgresql high-level API.

[procedure] (row-fold KONS KNIL RESULT)
[procedure] (row-fold* KONS KNIL RESULT)

This is the fundamental result set iterator. It calls (kons row seed) for every row, where row is the list of values in the current row and seed is the accumulated result from previous calls (initially knil), ie its pattern looks like (KONS ROWN ... (KONS ROW2 (KONS ROW1 KNIL))). It returns the final accumulated result.

The starred version works the same, except it calls (kons rowN-col1 rowN-col2 ... seed) instead of (kons rowN seed), so the procedure must know how many columns you have in the result set.

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
   (row-fold (lambda (row sum) (+ (car row) sum))
             0
             (query conn "SELECT 1 UNION SELECT 2")))
 => 3

(let ((conn (make-connection "localhost" "user" "pass")))
   (row-fold* (lambda (value str) (string-append str value))
              ""
              (query conn "SELECT 'hello, ' UNION SELECT 'world'")))
 => "hello, world"

[procedure] (column-fold KONS KNIL RESULT)
[procedure] (column-fold* KONS KNIL RESULT)

As row-fold/row-fold*, except this iterates sideways through the columns instead of lengthways through the columns, calling KONS with all values in all the rows of the current column, from left to right.

The starred version is much less useful here since you often don't know the number of returned columns, but it is provided for consistency.

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
  (column-fold (lambda (col sum) (+ (car col) sum))
               0
               (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => 101

[procedure] (row-fold-right KONS KNIL RESULT)
[procedure] (row-fold-right* KONS KNIL RESULT)

The fundamental result set recursion operator; Calls (KONS COL1 (KONS COL2 (KONS ... KNIL))) instead of (KONS COLN ... (KONS COL2 (KONS COL1 KNIL))).

[procedure] (column-fold-right KONS KNIL RESULT)
[procedure] (column-fold-right* KONS KNIL RESULT)

Column variants of row-fold-right/row-fold-right*.

[procedure] (row-for-each PROC RESULT)
[procedure] (row-for-each* PROC RESULT)

Simple for-each, calling the (PROC row) on each row, in turn, only for the purpose of its side-effects. The starred version calls (PROC col1 col2 ...).

[procedure] (column-for-each PROC RESULT)
[procedure] (column-for-each* PROC RESULT)

Column variants of row-for-each/row-for-each*.

[procedure] (row-map PROC RESULT)
[procedure] (row-map* PROC RESULT)

Maps rows to lists by applying PROC to every row and using its result in the result list on the position corresponding to that of the row. This procedure is not guaranteed to walk the result set in any particular order, so do not rely on the order PROC will be called.

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
   (row-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => (101 202)

[procedure] (column-map PROC RESULT)
[procedure] (column-map* PROC RESULT)

Column variants of row-map/row-map*.

(use freetds)

(let ((conn (make-connection "localhost" "user" "pass")))
   (column-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200")))
 => (3 300)

Low-level result API

This API allows you to inspect result objects on the individual row and column level.

[procedure] (result? OBJ)

Returns #t when OBJ is a result object, #f otherwise.

[procedure] (result-cleanup! RES)

Directly clean up all memory used by the result object. This is normally deferred until garbage collection, but it's made available for when you want more control over when results are released.

[procedure] (result-values RES)

Returns a list of all the rows in the result set RES, with each row represented as a list of field values.

[procedure] (result-values/alist RES)

Similar to result-values, except each row is now represented as an alist which maps column names (symbols) to values, so this procedure returns a list of alists.

[procedure] (result-value RES [COLUMN [ROW]])

Returns the value at the specified COLUMN and ROW.

If ROW or COLUMN are not specified, they default to zero. This makes for more convenient syntax if you're just reading out a result of a query which always has one row or even one value.

[procedure] (result-row RES [ROW])

Returns a list of all the columns' values at the given ROW number. If ROW is omitted, it defaults to zero.

[procedure] (result-column RES [COLUMN])

Returns a list of all the rows' values at the given COLUMN number. If COLUMN is omitted, it defaults to zero.

[procedure] (result-row/alist RES [ROW])

Returns an alist of the values at the given ROW number. The keys of the alist are made up by the matching column names, as symbols.

[procedure] (column-name RES INDEX)

Returns the name of the column (a symbol) at the position in the result set specified by INDEX. This is its aliased name in the result set.

[procedure] (column-names RES)

Returns a list of all the column names (symbols) in the result set. The position in the list reflects the position of the column in the result set.

Type conversion

Currently, the following Scheme types are accepted as parameter values for queries:

sql-null objects are (obviously) treated as SQL NULL values.
Strings are converted to CHAR. This means they can be no longer than 255 characters!
Fixnums are converted to INT.
Flonums are converted to FLOAT.
SRFI-19 date objects are converted to DATETIME

The following types are handled in result sets:

BINARY, LONGBINARY and VARBINARY types are translated to SRFI-4 u8vectors.
BIT is translated to a Scheme boolean (#t for nonzero, #f for zero).
CHAR, LONGCHAR, VARCHAR and TEXT are all translated to Scheme strings.
DATETIME and SMALLDATETIME are translated to SRFI-19 date objects.
TINYINT, SMALLINT, INT, BIGINT, NUMERIC, DECIMAL, FLOAT and REAL are all translated to the corresponding types of Scheme numbers. For integer numbers, bignums are used when neccessary.
MONEY and SMALLMONEY are translated to flonums.
IMAGE is translated to a Scheme string.

Error handling

 condition: freetds

A condition of kind (exn freetds) is signaled whenever an error occurs. The freetds component of this condition currently contains one property: retcode. Its value is the ctlib C API return code of the failed procedure.

Changelog

0.1 Initial release

License

GNU Lesser General Public License:

 Copyright 2011 Response Genetics, Inc.

 The FreeTDS egg is free software: you can redistribute it and/or
 modify it under the terms of the GNU Lesser Public License as
 published by the Free Software Foundation, either version 3 of the
 License, or (at your option) any later version.
 
 The FreeTDS egg is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 Lesser Public License for more details.

 You should have received a copy of the GNU Lesser Public License along
 with the FreeTDS egg.  If not, see <http://www.gnu.org/licenses/>.

FreeTDS

Description