chickadee » freetds

Outdated egg!

This is an egg for CHICKEN 4, the unsupported old release. You're almost certainly looking for the CHICKEN 5 version of this egg, if it exists.

If it does not exist, there may be equivalent functionality provided by another egg; have a look at the egg index. Otherwise, please consider porting this egg to the current version of CHICKEN.

FreeTDS

Description

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.

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

make-connection HOST USERNAME PASSWORD #!optional DATABASE-NAMEprocedure

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.

(call-with-connection HOST USERNAME PASSWORD [DATABASE-NAME] PROCEDURE)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).

connection-close CONNECTIONprocedure

Closes the CONNECTION and disconnects from the server.

connection-open? CONNECTIONprocedure

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

connection-reset! CONNECTIONprocedure

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

connection? OBJECTprocedure

Returns true if OBJECT is a FreeTDS connection-object.

Query procedures

send-query CONN QUERY #!rest PARAMSprocedure

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))
send-query* CONN QUERY PARAMSprocedure

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))
(call-with-result-set CONN QUERY [PARAM0 [PARAM1 ... ]] PROCEDURE)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.

row-fold KONS KNIL RESULTprocedure
row-fold* KONS KNIL RESULTprocedure

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"
column-fold KONS KNIL RESULTprocedure
column-fold* KONS KNIL RESULTprocedure

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
row-fold-right KONS KNIL RESULTprocedure
row-fold-right* KONS KNIL RESULTprocedure

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

column-fold-right KONS KNIL RESULTprocedure
column-fold-right* KONS KNIL RESULTprocedure

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

row-for-each PROC RESULTprocedure
row-for-each* PROC RESULTprocedure

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

column-for-each PROC RESULTprocedure
column-for-each* PROC RESULTprocedure

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

row-map PROC RESULTprocedure
row-map* PROC RESULTprocedure

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)
column-map PROC RESULTprocedure
column-map* PROC RESULTprocedure

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.

result? OBJprocedure

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

result-cleanup! RESprocedure

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.

result-values RESprocedure

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

result-values/alist RESprocedure

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.

result-value RES #!optional COLUMN ROWprocedure

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.

result-row RES #!optional ROWprocedure

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

result-column RES #!optional COLUMNprocedure

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

result-row/alist RES #!optional ROWprocedure

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.

column-name RES INDEXprocedure

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.

column-names RESprocedure

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:

The following types are handled in result sets:

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

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

Contents »