This is the version of plain-odbc which I am using right now. The API could stand some improvements, currently it is rather simple. But I think it is good enough for many uses. A higher level API is not a goal.
For Information about ODBC-API, I recommend the ODBC documentation from Microsoft.
Plain-odbc uses CFFI to interface to the ODBC libaries. The development platform ist clisp on Windows XP. On this system accessing Microsoft SQL-Server, Oracle 10g and MySQL 5.0 works. I do not expect big problems with other databases which have an ODBC driver. If there are problems they can also originate from the implementation of the ODBC driver, e.g. MyODBC does not support out parameters. At one time plain-odbc worked on Windows with LispWorks Personal Edition (4.4) and Allegro. On Linux it worked with CMUCL and MySQL. Since the interface to the ODBC library is done with CFFI, plain-odbc should still work on these platforms/systems.
David Owen made the necessary changes to the code to make it run on 64-bit Linux CMUCL and MySQL.
(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
connecttakes 3 arguments,
> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger")) #<ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott"> > (unless (zerop (first (first (exec-query *con* " select count(*) from user_tables where table_name = ? " "TEST1")))) (exec-command *con* "drop table test1")) > (exec-command *con* "create table test1(x integer,y varchar2(2000))") > (exec-update *con* "insert into test1(x,y) values(?,?)" 1 "text1") 1exec-update returns the number of records affected by a DML-statement.
> (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
> (exec-update *con* "update test1 set y=? where x=?" "text2" 1) 1There 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:
> (commit *con*)And at last we close the connection
> (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 ...
> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger")) #<ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott"> > (unless (zerop (first (first (exec-query *con* " select count(*) from user_tables where table_name = ? " "TEST2")))) (exec-command *con* "drop table test2")) > (exec-command *con* "create table test2(id integer,text clob)") > (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> > (exec-prepared-update stm 1 "text1") 1 > (exec-prepared-update stm 2 (make-string 1000000 :initial-element #\a)) 1 > (commit *con*) NILand clob columnns can be selected like normal varchar coloumns ...
> (subseq (caar (exec-query *con* "select text from test2 where id = 2")) (- 1000000 10) 1000000) "aaaaaaaaaa" > (close-connection *con*) NIL
> (setf *con* (plain-odbc:connect "ltrav1" "scott" "tiger")) #&ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott"> > (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")) > (decode-universal-time (caar date)) 34 ; 56 ; 13 ; 12 ; 3 ; 2005 ; 5 ; NIL ; -1 > (defun universal-time-list (time) (reverse (subseq (multiple-value-list (decode-universal-time time)) 0 6 ))) UNIVERSAL-TIME-LIST > (defun list-universal-time (list) (apply 'encode-universal-time (reverse list))) LIST-UNIVERSAL-TIME > (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")
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.
Note: MySql does not support out and in/out parameters. There are no return parameters. A citation from the documentation of MySql:
For programs written in a language that provides a MySQL interface, there is no
native method for directly retrieving the results of OUT or INOUT parameters
from CALL statements. To get the parameter values, pass user-defined variables
to the procedure in the CALL statement and then execute a SELECT statement
to produce a result set containing the variable values.
The following example illustrates the technique (without error checking) for
a stored procedure p1 that has two OUT parameters.
mysql_query(mysql, "CALL p1(@param1, @param2)");
mysql_query(mysql, "SELECT @param1, @param2");
result = mysql_store_result(mysql);
row = mysql_fetch_row(result);
The function prepare-statement is called with the parameters
|:string||a string, not very long||SQL_VARCHAR||length, integer optional|
|:unicode-string||a string, not very long||SQL_WVARCHAR||length, integer optional|
|: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|
> (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> > (exec-prepared-update *stm* (list 7999 20 "SMITH" "BOSS" 2335.96d0)) 1The 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).
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.
Close the odbc connection connection, any pending transaction is rolled back. After a connection is closed, it can not be used again.
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.
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.
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.
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.
connect-mysql server database user password
Connect to the MySql server on server connecting as user user with password password. The default database is database. If database is NIL no database is chosen. The name of the template odbc datasource for this function is default-mysql-dsn.
Open the Sqlite database with filename databasefile. default-sqlite-dsn. The databasefile must already exist. The name of the template odbc datasource for this function is default-sqlite-dsn.
use-bind-column connection setf (use-bind-column connection) boolean
With ODBC it is possible to retrieve data from a query in two ways: bind a column directly or use the function SQLGetData. This property determines which method is used. The default value is t. It can be changed anytime, this affects only queries that will be executed in the future. Usually the default value is fine. But SQLite (or its ODBC driver) has some quirks. For more information see the ODBC documentation on SQLGetData.
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 the 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).
exec-query*connection sqlstring parameters
The same as exec-query, but the ODBC parameters are passed as a list.
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.
exec-update* connection sqlstring parameters
The same as exec-update, but the ODBC parameters are passed as a list.
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.
exec-command* connection sqlstring parameters
The same as exec-command, but the ODBC parameters are passed as a list.
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 must return a resultset. The following functions offers the functionality of all three functions:
exec-sql* connection sqlstring parameters
The same as exec-sql, but the ODBC parameters are passed as a list.
Commit a pending transaction for connection connection.
Roll back a pending transaction for connection connection.
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.
Frees the prepared statement statement. A freed statement can not be used again.
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. Currently only the first resultset is returned!
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.
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.
get-primary-keys(con catalog schema table) This is a wrapper for SQLPrimaryKeys.
get-tables(con catalog schema table table-type) This is a wrapper for SQLTables.
get-columns(con catalog schema table column) This is a wrapper for SQLColumns.
get-foreign-keys(con catalog1 schema1 table1 catalog2 schema2 table2) This is a wrapper for SQLForeignKeys.