CLSQL now includes the CommonSQL API interface. CLSQL's manual is online at http://clsql.kpe.io/documentation.html.
CLSQL uses the UFFI interface so it works on:
CLSQL supports a number of database interfaces:
- PostgreSQL C library API
- PostgreSQL Socket API
- SQLite 2
- SQLite 3
- ODBC (On all platforms)
- AODBC (Allegro's ODBC database interface)
Not all features are supported on all DBMSes. For example, CLSQL's OODDL does not support :auto-increment on sqlite3.
There are CLSQL packages in the Debian and Gentoo distributions. CLSQL is covered by the LGPL license.
CLSQL can be downloaded from its web site or ASDF-install package (obsolete) http://files.kpe.io/clsql/clsql-latest.tar.gz
Bill Clementson has written two entries about CLSQL, in his web log:
- CL-SQL - Part 1 - discussing some of the advantages of CLSQL and how to install it.
- CL-SQL - Part 2 - an interactive, annotated session using CLSQL, illustrating some of the characteristics of working with CLSQL
To quote his Part 1:
If you would like to see some more examples of how to use CLSQL, the CLSQL tutorial is quite useful. Also, the tremendous CommonSQL tutorial that Nick Levine gave at ILC2002 is an excellent introduction to CLSQL. Even though his tutorial is targetted towards LispWorks CommonSQL, the examples work equally well in CLSQL (with the occasional minor tweak).
Looping over query result
You may get an error claiming that CLSQL can't find "foreign libraries." Find the directory where those files are located, and use clsql:push-library-path. They will likely be wherever CLSQL was installed, for example:
(clsql:push-library-path #p"/home/myusername/.sbcl/site/clsql-3.5.6/db-mysql/") ;installed here by asdf-install
This may also occur with OpenMCL; same solution.
This is not obvious from the documentation, but just using CONNECT gives you a single database connection - if you try to use that connection in multiple threads, you will get nasty race conditions. One convenient way to use CLSQL with multiple threads is to use the WITH-DATABASE macro around any code that talks to the database. Using connection pools, this is quite efficient, and thread-safe.