plain-odbc documentation

Preface

This is the version of plain-odbc which I am using right now. The API could stand some improvements, currently it is rather simple. Hopefully it suffices for many uses. I thought I make the current version available before I make my mind up about another API.

For Information about ODBC-API, I recommend the ODBC documentation from Microsoft.

Platforms, not up to date

Plain-odbc uses CFFI to interface to the odbc libaries. I have tested it on Windows LispWorks Personal Edition (4.4) and CLISP 2.38. I am too lazy to download the new version of an "Allegro CL Trial Edition", but I am confident that this should also work. On Windows ODBC connections to Oracle and SQL-Server work. On Linux plain-odbc has been tested with CLISP and MySQL.

Using plain-odbc, Examples

You must load plain-odbc into lisp. plain-odbc is an asdf module, so you need asdf. Make sure that asdf is able to find CFFI and plain-odbc.

(asdf:oos 'asdf:load-op :plain-odbc)

Then you have to create a connection to a database. The simplest way is to use the function connect. connect takes 3 arguments,

the DSN of the datasource
the userid
the password for the userid

Example, ltrav is a DSN for an oracle database, with the well known scott schema:

[4]> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger"))

#<ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott">
[5]> (unless (zerop 
         (first (first (exec-query *con* "
                   select count(*) from user_tables where table_name = ? " 
                                   "TEST1"))))
  (exec-command *con* "drop table test1"))

[6]> (exec-command *con* "create table test1(x integer,y varchar2(2000))")

[7]> (exec-update *con* "insert into test1(x,y) values(?,?)" 1 "text1")

1

exec-update returns the number of records affected by a DML-statement.

[8]> (exec-query *con* "select * from test1 where x = ?" 1)

((1.0d0 "text1")) ;
("X" "Y")

exec-query returns for each returned resultset two values, a list of rows (each row is the list of column values) and a list of the column
Note that the column x is retrieved as double, this is a problem with Oracle. Oracle does not really have integer columns, integer is just a short hand for number(37,0), and in a query this is returned as decimal. And plain-odbc converts decimals to doubles.

[9]> (exec-update *con* "update test1 set y=? where x=?" "text2" 1)
 
1

There is no autocommit mode, every transaction has to commited explicitly. If a connection is closed, it is rolled back. So we have to commit the changes now:

[10]> (commit *con*)

And at last we close the connection

[11]> (plain-odbc:close-connection *con*)

Any further opertaions with the connection will fail. It is not possible to reconnect the connection. For Oracle, SQL-Server and MS-Access there are special functions to connect to a database without creating a DSN first. Currently this works only under windows.

working with lobs ...

[5]> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger"))


#<ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott">
[6]> (unless (zerop 
  (first (first (exec-query *con* "
     select count(*) from user_tables where table_name = ? " 
     "TEST2"))))
      (exec-command *con* "drop table test2"))

[7]> (exec-command *con* "create table test2(id integer,text clob)")

[8]> (setf stm (prepare-statement *con* "insert into test2 (id,text) values(?,?)" 
        '(:integer :in) '(:clob :in)))

clob parameters have no size restriction ...

#<PLAIN-ODBC::PREPARED-STATEMENT #x19F38F01>
[9]> (exec-prepared-update stm  1 "text1")

1
[10]> (exec-prepared-update stm 2 (make-string 1000000 :initial-element #\a))

1
[11]> (commit *con*)

NIL

and clob columnns can be selected like normal varchar coloumns ...

[12]> (subseq (caar (exec-query *con* "select text from test2 where id = 2")) (- 1000000 10) 1000000)

"aaaaaaaaaa"
[13]> (close-connection *con*)

NIL

Classes

odbc-connection - this is an object which represents a connection to a database. It can only be created by connecting to a database.
prepared-statement - this is a prepared query object. It is created with prepare-statement. Prepared queries can be executed with the exec-prepared... functions.

Currently there are no sub classes of odbc-connection and prepared-statement. User code should not subclass these classes or overwrite methods.

Handling of Date Datatype

In plain-odbc dates are encoded as universal time, i.e. dates retrieved from or transfered to the ODBC library are done by converting universal time to the c struct SQL_C_TYPE_TIMESTAMP. The conversion to an explicit date datatype is done by the values of the variables *universal-time-to-date-dataype*, *date-datatype-to-universal-time*. The default values of these variables is the function identity. The value of the variable *date-type-predicate* is used to determine if an object is a date, its default value is (lambda (x) (declare (ignore x)) nil)). This is needed when a the correct ODBC type for a parameter has to infered. In order for this to work, the date objects must not be lists, vectors or strings. It should be a separate data type.
Example, lets use Oracle:

[62]> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger"))

#&ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott">
[63]> (setf date (exec-query *con* "
     select to_date('12.3.2005 13:56:34','dd.mm.yyyy hh24:mi:ss') as date_,
            to_char(?,'dd.mm.yyyy hh24:mi:ss') as string from dual" 
                       (list (encode-universal-time 10 30 13 31 12 2004) :date )))

((3319620994 "31.12.2004 13:30:10"))
[64]> (decode-universal-time (caar date))

34 ;
56 ;
13 ;
12 ;
3 ;
2005 ;
5 ;
NIL ;
-1
[65]> (defun universal-time-list (time)
  (reverse (subseq (multiple-value-list (decode-universal-time time)) 0 6  )))

UNIVERSAL-TIME-LIST
[66]> (defun list-universal-time (list)
  (apply 'encode-universal-time (reverse list)))

LIST-UNIVERSAL-TIME
[67]> (let ((*universal-time-to-date-dataype* 'universal-time-list)
      (*date-datatype-to-universal-time* 'list-universal-time))
  (exec-query *con* "
     select to_date('12.3.2005 13:56:34','dd.mm.yyyy hh24:mi:ss') as date_,
            to_char(?,'dd.mm.yyyy hh24:mi:ss') as string from dual" 
              (list (list 2004 12 31 13 30 10) :date)))

(((2005 3 12 13 56 34) "31.12.2004 13:30:10")) ;
("DATE_" "STRING")

Database NULL

The database NULL value is mapped to NIL.

Parameters

Plain-odbc supports the prepared statements of ODBC. In order to create a prepared statement, the SQL text and the formal parameters must be provided. Later on, the prepared statement can be called by supplying the actual parameters only.
It is also possible to supply parameters when calling the functions exec-query, exec-update and exec-command. In this case the actual parameter and the parameter specification are combined in a list or the paramter specification is derived from the actual parameter.

The function prepare-statement is called with the parameters

a connection,
an SQL text
a &rest list of ODBC parameters.

An element of this parameter list is a list, the first elment is a keyword, the second element is the direction of the parameter, one of :in, :out and :inout. The rest of the list are additional parameters for this parameter type. Currently the only parameter is the maximal length parameter for the string and binary (= Oracle's raw) datatypes. Instead of a list for a parameter description only the name of the parameter type can be given. This is equivalent to a list with two elements: the symbol and the direction :in. The following parameter types ares supported:

Symbol	Explanation	ODBC Type	Parameters
`:string`	a string, not very long	SQL_VARCHAR	length, integer optional
`:unicode-string`	a string, not very long	SQL_WVARCHAR	length, integer optional
`:integer`	an integer	SQL_INTEGER
`:double`	a double float	SQL_DOUBLE
`:date`	a point in time, date + time of day	SQL_TIMESTAMP
`:binary`	an array of (unsigned-byte 8), not very long	SQL_VARBINARY	length, integer optional
`:clob`	string, a LOB datatype	SQL_LONGVARCHAR
`:uclob`	unicode string, a LOB datatype	SQL_LONGWVARCHAR
`:blob`	an array of (unsigned-byte 8), a LOB datatype	SQL_LONGVARBINARY

Example:


[16]> (setf *stm* (prepare-statement *con* 
        "insert into emp (empno,deptno,ename,job,sal) values(?,?,?,?,?)"  
       '((:integer :in) (:integer :in) (:string :in 200) (:string :in 20) 
         (:double :in))))

#<PLAIN-ODBC::ODBC-QUERY #x19ED07C5>
[17]> (exec-prepared-update *stm* (list 7999 20 "SMITH" "BOSS" 2335.96d0))

1

The parameter types :blob and :clob can not be used as out and inout parameters. But one can select columns with datatype clob and blob (the name of the type depends on the database).

Mapping of Lisp Values to Parameter Types

A statement parameter passed to one of the functions which execute a statement directly is either a list whose car is the actual value of the parameter and the cdr is a parameter description, or it is for example ("this is a string" :string :in 100) or (#(1 2 3 4) :binary :in 20). Otherwise it is assumed that this is the actual parameter and a statement specification is computed based on this value.

NIL is passed as parameter :string.
A string is passed as :string.
A integer is passed as :integer.
A float is passed as :double.
A array of dimension 1 is passed as :binary, of course all elements of this array must be non negative integers smaller than 256.
If the execution of the value of *date-type-predicate* with the value returns true then the value is passed as :date. Of course *date-datatype-to-universal-time* must be able to convert the value universal time.

Functions / Methods

Currently most functions are implemented as methods, but you should not depend on it.

Connecting

[Function]
connect dsn user password
connect connects to an odbc datasource, dsn is the dsn of the datasource, user the user and password the password. The return value is an odbc-connection.

[Function]
close-connection connection
Close the odbc connection connection, any pending transaction is rolled back. After a connection is closed, it can not be used again.

[Function]
connect-generic &rest args
The function connect-generic expects that the rest parameter is alternating list of keywords and strings. From this list a connection string is build and used to connect to a datasource. Each pair of keyword and string becomes an attribute in the connection string, example: (connect-generic :dsn "ltrav1" :uid "scott" :pwd "tiger") will create the connection string "dsn=ltrav1;uid=scott;pwd=tiger" and use this connection string to connect with the function SQLDriverConnect of the ODBC-API. The user is not prompted for missing information. Maybe this function should be called driver-connect?

The following functions create connections to specific kinds of databases. These functions need a template odbc datasource to the specific kind of database which is usually called default-databasetype-dsn. This default dsn determines then the used driver and some further properties. For example, connect-oracle has the parameters tns-name, user password. And the functions connect-access only parameter is the name of the .mdb file.

[Function]
connect-sql-server server database &optional user password
Connect to sql server named server, the initial database is database. If user and password are supplied the connection is made with sql server authentication, if the parameters are not supplied then the connection is made with integrated security. The name of the template odbc datasource for this function is default-sql-server-dsn.

[Function]
connect-oracle server user password
Connect to oracle database server, connecting as user with password password. The name of the template odbc datasource for this function is default-oracle-dsn.

[Function]
connect-access filename
Connect to the access database (a .mdb file) with name filename. The name of the template odbc datasource for this function is default-access-dsn.

Working with Connections

[Function]
exec-query connection sqlstring &rest parameters
Execute a query, a command which returns a resultset (or several resultsets). connection is the odbc connection on which the command is executed. sqlstring is the command text. parameters is a list of parameter descriptions as described in the section Mapping of Lisp Values to Parameter Types. This functions returns for each resultset that is returned by the command two values. The first is the result set as a list of lists, i.e. a row is a list and the result set is a list of rows. The second value is a list of the column names.

Note: If a column is a LOB (BLOB or CLOB) then the data is retrieved with the ODBC function SQLGetData. This has the consequence that thr following columns must also be retrieved with SQLGetData, otherwise an error is raised by the driver. Retrieving the following columns via SQLGetData is not done automatically. Therefore if a LOB is selected it should be the last column.

Note: The result set data is converted to lisp data automatically. The ODBC datatypes SQL_NUMERIC and SQL_DECIMAL are retrieved as doubles. If you need the full precision and decimal rounding of the datatypes, you should convert them to a string in the select statement. But be carefull with the conversion format (1.23 vs. 1,23).

[Function]
exec-update connection sqlstring &rest parameters
Executes an update on connection with sql sqlstring and returns the number of affected records. parameters is a list of parameter descriptions.

[Function]
exec-command connection sqlstring &rest parameters
Execute an command on connection with sql sqlstring. parameters is a list of parameter descriptions. Returns the list of out and in-out parameters.

NOTE
The main difference exec-command, exec-update and exec-query is what is returned by these functions. One can execute a select statement with exec-update and exec-command. But the command for exec-query muts return a resultset.

[Function]
commit connection
Commit a pending transaction for connection connection.

[Function]
rollback connection
Roll back a pending transaction for connection connection.

[Function]
prepare-statement connection statement &rest parameters
Creates a prepared statement for connection connection and statement statement. The parameter parameters is a list of the parameter descriptions. A parameter description is a list (parametertype direction further-args ...). parametertype determines the type of parameter is described in the section parameters.

[Function]
free-statement statement
Frees the prepared statement statement. A freed statement can not be used again.

[Function]
exec-prepared-query query &rest parameters
Executes a previoulsy prepared statement query using the parameters in parameters. The return value is the same as for exec-query, a list of records in the resultset and as second value the names of the columns. The meta data of the result set is internally cached. If the meta data of the result set changes with the invocations yo must use a new prepared statement. This should not happen often, but it is possible.

[Function]
exec-prepared-update query &rest parameters
Executes an update with the previoulsy created prepared statement query using the parameters in parameters. The return value is the number of affected records.

[Function]
exec-prepared-command query &rest parameters
Executes a command (stored procedure) with previously prepared statement query. For each in or in/out parameter there must be a value in the parameters. The return value is the list values for the out and in/out parameters.

Utilities

[Macro]
with-prepared-statement (stm con string &rest params) &body body
Execute body with stm bound to (preparse-statement con string params ...). After that, the prepared statement stm is freed.