You can edit this page using wiki syntax for markup.

Article contents:== Outdated egg! This is an egg for CHICKEN 4, the unsupported old release. You're almost certainly looking for [[/eggref/5/freetds|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 [[https://wiki.call-cc.org/chicken-projects/egg-index-5.html|egg index]]. Otherwise, please consider porting this egg to the current version of CHICKEN. [[tags: egg]] == FreeTDS [[toc:]] === Description Bindings for the [[http://freetds.schemamania.org/|FreeTDS]] library (specifically the CTLib API). === Author Written for [[http://www.responsegenetics.com/|Response Genetics, Inc.]] by [[/users/peter-danenberg|Peter Danenberg]], with contributions by [[/users/peter-bex|Peter Bex]]. === Repository The FreeTDS egg is maintained in a [[https://github.com/klutometis/freetds|Github repository]]. === Requirements * [[foreigners]] * [[sql-null]] * [[numbers]] * [[srfi-19]] * [[setup-helper]] You will also need to have [[http://freetds.schemamania.org/software.html|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 [[/chicken-projects/egg-index-4.html#db|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])</procedure> 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)</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)</procedure> Closes the {{CONNECTION}} and disconnects from the server. <procedure>(connection-open? CONNECTION)</procedure> Returns {{#t}} when the {{CONNECTION}} is still open, {{#f}} if it was closed. <procedure>(connection-reset! CONNECTION)</procedure> 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)</procedure> Returns true if OBJECT is a FreeTDS connection-object. ==== Query procedures <procedure>(send-query CONN QUERY . PARAMS)</procedure> 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). <enscript highlight="scheme"> (use freetds) (let ((conn (make-connection "localhost" "user" "pass"))) (result-values (send-query conn "SELECT ?, 2" "hello"))) => (("hello" 2)) </enscript> <procedure>(send-query* CONN QUERY PARAMS)</procedure> 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. <enscript highlight="scheme"> (use freetds) (let ((conn (make-connection "localhost" "user" "pass"))) (result-values (send-query conn "SELECT ?, 2" '("hello")))) => (("hello" 2)) </enscript> <procedure>(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. <procedure>(row-fold KONS KNIL RESULT)</procedure><br> <procedure>(row-fold* KONS KNIL RESULT)</procedure><br> 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. <enscript highlight="scheme"> (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" </enscript> <procedure>(column-fold KONS KNIL RESULT)</procedure><br> <procedure>(column-fold* KONS KNIL RESULT)</procedure><br> 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. <enscript highlight="scheme"> (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 </enscript> <procedure>(row-fold-right KONS KNIL RESULT)</procedure><br> <procedure>(row-fold-right* KONS KNIL RESULT)</procedure><br> 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><br> <procedure>(column-fold-right* KONS KNIL RESULT)</procedure><br> Column variants of {{row-fold-right}}/{{row-fold-right*}}. <procedure>(row-for-each PROC RESULT)</procedure><br> <procedure>(row-for-each* PROC RESULT)</procedure><br> 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><br> <procedure>(column-for-each* PROC RESULT)</procedure><br> Column variants of {{row-for-each}}/{{row-for-each*}}. <procedure>(row-map PROC RESULT)</procedure><br> <procedure>(row-map* PROC RESULT)</procedure><br> 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. <enscript highlight="scheme"> (use freetds) (let ((conn (make-connection "localhost" "user" "pass"))) (row-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200"))) => (101 202) </enscript> <procedure>(column-map PROC RESULT)</procedure><br> <procedure>(column-map* PROC RESULT)</procedure><br> Column variants of {{row-map}}/{{row-map*}}. <enscript highlight="scheme"> (use freetds) (let ((conn (make-connection "localhost" "user" "pass"))) (column-map* + (query conn "SELECT 1, 100 UNION SELECT 2, 200"))) => (3 300) </enscript> ==== Low-level result API This API allows you to inspect result objects on the individual row and column level. <procedure>(result? OBJ)</procedure> Returns {{#t}} when {{OBJ}} is a result object, {{#f}} otherwise. <procedure>(result-cleanup! RES)</procedure> 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)</procedure> 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)</procedure> 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]])</procedure> 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])</procedure> 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])</procedure> 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])</procedure> 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)</procedure> 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)</procedure> 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.9 Change deprecated {{include}} of setup-helper to {{use}}. * 0.1.8 Disabled tests; they kept failing on Salmonella. * 0.1.7 Fix issue #1 (remove deprecated use of null-pointer). Thanks to "ptcek" for verifying the fix. * 0.1.6 Fix numerical syntax for +inf.0, so that it works with CHICKEN 4.9.0. * 0.1.5 Fix multiline comment syntax so it compiles again. * 0.1.4 Fix egg category name so it ends up under "Databases" in the egg index. * 0.1.3 Fix connection procedure so it doesn't fail when a database-name is passed. * 0.1.2 Make extension-version a string. * 0.1.1 Remove some documentation files and old test stuff. * 0.1 Initial release === License [[http://www.gnu.org/copyleft/lesser.html|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/>. Description of your changes:

I would like to authenticate

Spam control

What do you get when you add 9 to 23?