Plain-odbc is a simple, yet powerful lisp ODBC interface. It is possible to use parameterized queries with in and out parameters and call stored procedures.
The primary development platform is CLISP on win32, but plain-odbc is also usable with CLISP on Linux with unixODBC.
Currently there are problems with Clisp on Windows XP and oarcle and mysql. Connecting to these databases causes a segmentation fault. On the other hand connecting to SQL-Server works, also I have no problems with Oracle on Windows Server 2003. Also there are no problems with a clisp which I compiled myself with debugging support. I surmise this is a Mingw problem.
High level features such as a special reader syntax for sql or mapping of CLOS objects to database tables are not part of plain-odbc.
The low level parts of the system consist of code from Paul Meurers sqlodbc module. Paul Meurer used for his code a license of his own design. The system uses now CFFI to interface to the ODBC C functions.
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, MySQL 5.0 and PostgreSQL works (but see warning above). 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 on these platforms/systems.
David Owen made the necessary changes to the code to make it run also on 64-bit Linux CMUCL and MySQL.
The plain-odbc system depends on CFFI, but CFFI itself depends on the libraries alexandria, babel and trivial-features. Since I do not want to deal with changing versions of these libraries I included the libraries. Plain-odbc is build against the included versions of the libraries. If there are newer versions they will be included, but not at once.
Questions, feature requests, and bug-reports are welcome at roland dot averkamp dot 0 at googlemail dot com. Since I got a lot of spam on the default project mailing lists I asked the administrators to terminate the mailing lists.
You can download a version of Paul Meurers sqlodbc module and of the currently working version of plain-odbc from the the list of downloads .
I moved to subversion, but I was to lazy to import the development history. The URL of the subversion respository is: svn://common-lisp.net/project/plain-odbc/svn/plain-odbc, but you can also browse it with WebSVN. You can also browse the old CVS repository .
There is documentation and a compilation of problems and useful tricks.
The main things one needs to access a database are working: connecting, update, query and calling stored procedures. SQL statements can be executed with parameters. You should you use them if you want to avoid SQL-injection. Using them is simpler than creating the SQL statement with the lisp function format. For insert, update statements parameters of type lob (binary lobs, character lobs and unicode lobs) are supported. Parameters of type lob for stored procedures will most likely not work, but this depends on the ODBC driver. When calling Oracle stored procedures it is possible to retrieve out ref cursors (10 g, Oracle ODBC driver, see notes). Also multiple results sets are supported. To see what is possible you should look at the files for testing (directory src/test).
Basically it should be possible to access every database which can be accessed via ODBC. Most databases which run under Windows (even Excel!) support ODBC, the driver manager is part of the operating system. For Linux/Unix, MySQL and PostgreSQL support ODBC, as a driver manager one can use the one from unixODBC.
An example, connecting to an Oracle database (ltrav is the DSN for an Oracle database):
[1]> (asdf:oos 'asdf:load-op :plain-odbc)
; loading system definition from a:/lisp/plain-odbc/plain-odbc.asd into #<PACKAGE
; ASDF413>
.............
;; Loading file A:\lisp\plain-odbc\src\odbc\odbc-utilities.fas ...
;; Loaded file A:\lisp\plain-odbc\src\odbc\odbc-utilities.fas
0 errors, 0 warnings
NIL
[2]> (use-package :plain-odbc)
T
[3]> (setf *con* (connect "ltrav1" "scott" "tiger"))
#<ODBC-CONNECTION SERVER="LTRAV1" DBMS="Oracle" USER="scott">
[4]> (exec-command *con* "create table test1(x integer,y varchar2(2000))")
NIL
[5]> (exec-update *con* "insert into test1(x,y) values(1,'text1')")
1
[6]> (commit *con*)
0
[7]> (exec-query *con* "select * from test1")
((1.0d0 "text1")) ;
("X" "Y")
[8]> (close-connection *con*)
NIL
Back to Common-lisp.net.