source download sqlite3.lsp
Module: sqlite3.lsp
SQLite3 database interface routines
Version: 1.6 - comments redone for automatic documentation
Version: 1.7 - D.C. fixed getting types when null values are present
Version: 1.8 - D.C. made 64-Bit integers work
Version: 1.9 - new library detection routine
Version: 2.0 - added documentation for close
Version: 2.1 - use default functor for query
Version: 2.2 - detection for NEWLISP64, lib path for OpenBSD, tested for 64-bit
Version: 2.3 - C.H. added parameter binding for safer SQL (guard against SQL-injection)
Version: 2.4 - doc changes
Version: 2.5 - changed sqlite3_bind_blob to sqlite3_bind_text in function bind-parameter
Version: 2.61 - added function sql3:colnames.
Version: 2.7 - changed deprecated name to term, inc to ++
Version: 2.71 - minor doc changes
Version: 2.72 - add support for CentOS 6 Linux 64Bit
Version: 2.73 - doc additions
Version: 2.83- added sqlite3 library path for UBUNTU 12.04/10 64-bit and others
Author: Lutz Mueller 2004-2013, Dmitri Cherniak 2007, Clemens Hintze 2009
Module for SQLite3 database bindings
To use this module include the following load or module statement at the beginning of the program file:(load "/usr/share/newlisp/modules/sqlite3.lsp") ; or shorter (module "sqlite3.lsp")Test the module:(test-sqlite3)This function, located at the and of the module file, exercises most of the functions.
SQLite version 3.0 introduced a new database format and is incompatible whith the previous 2.1 to 2.8 format. Old SQLite 2.x based databases can be converted using the old and new sqlite client application:
sqlite OLD.DB .dump | sqlite3 NEW.DB
While in sqlite 2.8 all returned fields where of string type, SQLite3 returns, text, integer or float. Blobs are returned as text and NULLs are returned as nil.
See also the documentation at sqlite.org
Requirements:
One of the libraries sqlite3.dll for Win32 or libsqlite3.so for UNIX like operating systems is required from http://www.sqlite.org.
SQLite is an in-process database. The library contains the whole database system. An extra database server is not required. SQLite also has limited mutiuser capabilities for accessing a common database from several programs at the same time. See the documentation at sqlite.org for details.
The following is a short example how to use SQLite3:
Example:Function calls returning nil signal that an error has occurred. The function sql3:error can then be used to get details about the error as a text string.(sql3:open "MYDB") ; opens/creates a database returns a handle (ignore) ; or 'nil' on failure (sql3:sql "select * from mytable;") ; make a SQL query, return result (sql3 "select * from mytable;") ; use default functor as alias (sql3:error) ; return error text (sql3:close) ; close the database
At the bottom of the source file sqlite3.lsp a test routine called test-sqlite3 can be found to test for correct installation of SQLite.§
sql3:open
syntax: (sql3:open str-db-name)
parameter: str-db-name - The name of the database.
return: A database handle (discard), or nil on failure.
Opens or creates a database. If the database does exist it gets opened, else a new database with the name given is created. If trying to open a database that already has been opened nil is returned and an error text can be retrieved using sql3:error.§
sql3:close
syntax: (sql3:close)
return: Returns true on success;
Closes the currently open database.§
sql3:sql
syntax: (sql3:sql str-sql [sql-args])
parameter: str-sql - The SQL statement.
parameter: sql-args - Parameters for the SQL statement's host variables
Executes the SQL statement in str-sql. For select statements a table of the result set is returned or () for the empty set. For other statements true is returned for a successful outcome. On failure nil is returened and sql3:error can be used to retrieve the error text.
If the parameter sql-args is given, it has either to be a list of values (if the SQL statement use the '?' type of host variables) or an association list whose every association is formed like (varname value). The varname is the name of the host variable used in the SQL statement e.g. :name or ?123.
Strings are bound to host variables as BLOBs. That mean the data will be passed as is, without any further modification.
Using host variables is much safer than passing those values via string composition as no SQL quoting problem can occur (SQL injection attack). For example:
Example:; traditional usage (sql3:sql "select * from persons where age > 18;") ; safer usage using SQLite parameter binding (sql3:sql "select * from persons where age > ?;" '(18)) ; bind parameters from association lists (sql3:sql "select * from persons where name like :name;" '((":name" "Do%"))) (sql3:sql "select * from persons where age > :a and name like :n;" '((":n" "Do%") (":a" 18)))§
sql3:colnames
syntax: (sql3:colnames)
return: A list of column header names.
Returns a list of column header names for the last query. This is a function wrapper around the internal variable sql3:col-names.§
sql3:rowid
syntax: (sql3:rowid)
return: The last row id from last insert.
Gets the id of the last row inserted.§
sql3:tables
syntax: (sql3:tables)
return: A list of tables in the database.
§
sql3:columns
syntax: (sql3:columns str-tabel-name)
return: A list of column names for a table.
§
sql3:changes
syntax: (sql3:changes)
return: The Number of rows changed/affected by the last SQL statement.
§
sql3:timeout
syntax: (sql3:timeout num-milli-seconds)
return: true on success or nil on failure.
Sets busy timeout in milliseconds.§
sql3:error
syntax: (sql3:error)
return: The error text of the last error occured in sql3:sql.
- ∂ -
generated with newLISP and newLISPdoc