clisp-sqlite

clisp-sqlite is a simple interface to the sqlite library written by Scott Williams. It offers a raw interface directly to the C functions in the package SQLITE.RAW, as well as a more civilized and lispy interface in SQLITE.

Download ASDF package from http://clisp-sqlite.googlecode.com/files/clisp-sqlite_1.0.tar.gz

clisp-sqlite dictionary

SQLITE.RAW package

These functions call sqlite directly, so they don't provide any fancy error handling, or argument conversion.

function sqlite_open database-name-string mode => (values database-pointer nil)
attempt to open a database.
function sqlite_close database-pointer => (values)
Close a database. Pass a database pointer returned from sqlite_open, and don't use it again.
function sqlite_exec database-pointer sql-string callback nil => (values error-code error-message)
Run a query, generating a callback to callback for each returned row. Look at the sqlite docs for more information about callback (nb: callback should return 0 unless you want trouble!).
function sqlite_last_insert_rowid database-pointer => last-inserted-row-id
Return the id of the last inserted row.
function sqlite_changes database-pointer => rows-modified-or-deleted-or-inserted
Returns the number of rows inserted, deleted, or modified by the last sqlite_exec.

SQLITE package

This package provides a more convenient interface to sqlite, complete with rudimentary condition handling. Database pointers are wrapped in a sqlite::database object. You can extract the wrapped database pointer with sqlite::database-address if you really want it, but it's best not to mix calls between functions from the sqlite and sqlite.raw packages.

function sqlite-open filename => open-db-object
Attempt to open filename as a database object. If it can't be opened, a condition is raised. Question: would it be better to create a special condition to raise instead?.
function sqlite-close db-object => closed-db-object
Close a database connection created by sqlite-open.
macro with-open-db (db-var filename) &body body
Bind the db-var variable to an open database named by filename for the body of the macro, makes certain using unwind-protect that the database is closed.
function sqlite-exec db-object query-string callback &optional argument => t
Executes query-string á la sqlite_exec, except that t is returned in normal cases. In exceptional cases, an error is raised instead.
function sql db-object sql-query => sql-result
Execute an sql query, returning a list of rows, where each row is a list of columns in the plain-old string format returned by sqlite. Not a particularly efficient function.
macro with-query (db-object (arg-0...arg-n) sql-query-format-template &rest query-format-template-args) &body body
A thoroughly useful macro. The sql query is produced by applying format to the format template and the template args, that way your query can be computed dynamically. As each row is returned, each column is bound to the corresponding variable named in arg-*. Then the body is evaluated. It could probably stand some optimization. Here's an example:
```
(with-open-db (db #"foo.db")
  (with-query (db (id name email) "select id, name, email from student where grade=~s" 12)
    (format t "~s ~s ~s~%" id name email)))
```

Examples

CLISP SQL

This page is linked from: SQLite Lisp interface

CLiki pages can be edited by anyone at any time. Imagine a fearsomely comprehensive disclaimer of liability. Now fear, comprehensively