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 connect.
connect takes 3 arguments,
[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
[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
[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")
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);
mysql_free_result(result);
The function prepare-statement is called with the parameters
| 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 |
[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).
[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.
[Function]
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.
[Function]
connect-sqlite databasefile
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.
[Function]
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).
[Function]
exec-query*connection sqlstring parameters
The same as exec-query, but the ODBC parameters are passed as a list.
[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-update* connection sqlstring parameters
The same as exec-update, but the ODBC parameters are passed as a list.
[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.
[Function]
exec-command* connection sqlstring parameters
The same as exec-command, but the ODBC parameters are passed as a list.
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
must return a resultset. The following functions offers the functionality
of all three functions:
[Function]
exec-sql* connection sqlstring parameters
The same as exec-sql, but the ODBC parameters are passed as a list.
[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.
Currently only the first resultset is returned!
[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.
[Function]
get-primary-keys(con catalog schema table)
This is a wrapper for SQLPrimaryKeys.
[Function]
get-tables(con catalog schema table table-type)
This is a wrapper for SQLTables.
[Function]
get-columns(con catalog schema table column)
This is a wrapper for SQLColumns.
[Function]
get-foreign-keys(con catalog1 schema1 table1 catalog2 schema2 table2)
This is a wrapper for SQLForeignKeys.