Documentation

Documentation for eusqlite
Introduction
This guide will give you a quick introduction on how to use euSQLite in
your programs.
There is some information in the base SQLite docs found in the sqldocs\
directory which you need to read as well. You should take the time to
read at least the FAQ.htm, Query.htm and sqlite_util.htm.
Basic structure
This is the skeleton of a typical euSQLite program.
include "eusqlite.ew"
atom db
sequence data
db = sqlite_open("{filename_of_your_database",0)
... do some processing / user input etc
data = sqlite_get_table(db, "{SQL statements go here}")
if sqlite_last_err_no != SQLITE_OK then
... do some error processing
end if
... do some more processing / user input etc
sqlite_close(db)
It basically comes down to:
 Include "euSQLite.ew"
 Open your database - sqlite_open()
 Execute SQL statements - sqlite_get_table()
 Close your database - sqlite_close()
Global constants
:SQLITE_OK = 0 Successful result
:SQLITE_ERROR = 1 SQL error or missing database
:SQLITE_INTERNAL = 2 An internal logic error in SQLite
:SQLITE_PERM = 3 Access permission denied
:SQLITE_ABORT = 4 Callback routine requested an abort
:SQLITE_BUSY = 5 The database file is locked
:SQLITE_LOCKED = 6 A table in the database is locked
:SQLITE_NOMEM = 7 A malloc() failed
:SQLITE_READONLY = 8 Attempt to write a readonly database
:SQLITE_INTERRUPT = 9 Operation terminated by sqlite_interrupt()
:SQLITE_IOERR = 10 Some kind of disk I/O error occurred
:SQLITE_CORRUPT = 11 The database disk image is malformed
:SQLITE_NOTFOUND = 12 (Internal Only) Table or record not found
:SQLITE_FULL = 13 Insertion failed because database is full
:SQLITE_CANTOPEN = 14 Unable to open the database file
:SQLITE_PROTOCOL = 15 Database lock protocol error
:SQLITE_EMPTY = 16 (Internal Only) Database table is empty
:SQLITE_SCHEMA = 17 The database schema changed
:SQLITE_TOOBIG = 18 Too much data for one row of a table
:SQLITE_CONSTRAINT = 19 Abort due to constraint violation
:SQLITE_MISMATCH = 20 Data type mismatch
:SQLITE_MISUSE = 21 Library used incorrectly
:SQLITE_NOLFS = 22 Uses OS features not supported on host
:SQLITE_AUTH = 23 Authorization denied
:SQLITE_ROW = 100 sqlite_step() has another row ready
:SQLITE_DONE = 101 sqlite_step() has finished executing
:SQLITE_STATIC = 0,
:SQLITE_TRANSIENT = -1
Global variables
integer sqlite_last_error_number
sequence sqlite_last_error_description
integer SQLITE_MAX_FIELD_LENGTH
integer SQLITE_MAX_ERROR_LENGTH
integer SQLITE_MAX_VERSION_LENGTH
< Function list >
Original routines, Ray Smith :
procedure sqlite_free(atom address)
function sqlite_open(sequence filename, integer mode)
procedure sqlite_close(atom db_handle)
function sqlite_exec(atom db_handle, sequence sql_command)
procedure sqlite_free_table(atom data_address)
function sqlite_get_table(atom db_handle, sequence sql_command)
function sqlite_libversion()
The following routines courtesy of Tone Skoda :
function sqlite_prepare(atom db_handle, sequence sql_command)
function sqlite_step(atom db_handle, atom stmt)
procedure sqlite_reset(atom db_handle, atom stmt)
function sqlite_finalize(atom db_handle, atom stmt)
procedure sqlite_bind_int(atom db, atom stmt, integer param_index, integer val)
procedure sqlite_bind_double(atom db, atom stmt, integer param_index, atom val)
procedure sqlite_bind_text(atom db, atom stmt, integer param_index, sequence val)
procedure sqlite_bind_blob(atom db, atom stmt, integer param_index, object val)
function sqlite_column_int(atom db, atom stmt, integer column_num)
function sqlite_column_bytes(atom db, atom stmt, integer column_num)
function sqlite_column_text(atom db, atom stmt, integer column_num)
function sqlite_column_blob(atom db, atom stmt, integer column_num)
function compress(sequence thing)
function decompress(sequence thing)
Th following routines Chris Burch :
function sqlite_query_database(sequence database, sequence cmd)
< procedure sqlite_free(atom address) >
Used to free memory obtained from sqlite3_mprintf() or sqlite3_vmprintf().
These functions are implemented internally in sqlite3 error reporting, so this is used to clear memory
allocated to error character strings.
example :
err_addr = peek4u(err_ptr_addr)
if err_addr > 0 then
sqlite_last_err_desc = peek_sequence(err_addr, SQLITE_MAX_ERR_LENGTH)
sqlite_free(err_addr)
end if
< function sqlite_open(sequence filename, integer mode) >
Opens a database for access. If the database does not exist then it is created.
Returns the database handle.
example :
sequence data
atom sql_db
sql_db = sqlite_open("Database_to_open", 0)
if sql_db <= 0 then
--can't open database
end if
< procedure sqlite_close(atom db_handle) >
Closes a database associated with db_handle, previously opend with sqlite_open()
function sqlite_exec(atom db_handle, sequence sql_command)
Executes an sql command. Note this a very much simplified version of the C function.
Returns SQLITE_OK, SQLITE_ABORT or SQLITE_BUSY
example :
if (sqlite_exec(db, "BEGIN TRANSACTION") != SQLITE_OK) then
etc
end if
< procedure sqlite_free_table(atom address) >
Frees the memory allocated to a set of results created by sqlite_get_table.
Called internally from sqlite_get_table() - no requirement to call externally.
< function sqlite_get_table(atom db_handle, sequence sql_command) >
This is a wrapper for sqlite_exec()
Returns data associated with a query, in the form of a sequence.
Operates on a previously opened database.
example :
global function query_database(sequence database, sequence cmd)
--a universal get info from a database function
sequence data
atom sql_db
sql_db = sqlite_open(database, 0)
if sql_db <= 0 then
--handle errors
end if
data = sqlite_get_table(sql_db, cmd)
if sqlite_last_err_no != SQLITE_OK then
--handle your errors here
end if
sqlite_close(sql_db)
If the sql query results in no data then a sequence of length 0 is returned.
If data is found, then a sequence of the form
data[row][column]
is returned.
The first row of data is always the column headers, subsequent rows are the returned records
< function sqlite_libversion() >
Returns a sequence with the SQLite library version
example :
printf(1, "SQLite Library Version %s\n", {sqlite_libversion()})
< function sqlite_prepare(atom db_handle, sequence sql_command) >
Prepares an sql command for execution.
Sql commands prepared with this can be 're-used' to allowing insertion of new data using the 'bind'
commands. This is much faster than repeatedly creating a new sql command.
Returns a handle to the sql_command
example :
atom stmt
stmt = sqlite_prepare(db, "insert into table_name values(?,?,?,?)")
Note the variable positions are delineated by the '?' - you insert variables in here.
(See sqlite_insert.exw)
< function sqlite_step(atom db_handle, atom stmt) >
Executes the sql stmt prepared with sqlite_prepare, with values inserted by one of the 'bind' family
example :
Void = sqlite_step(db, stmt)
(See sqlite_insert.exw)
< procedure sqlite_reset(atom db_handle, atom stmt) >
Resets the prepared stmt, ready to accept its next set of values
example :
sqlite_reset(db, stmt)
(See sqlite_insert.exw)
< function sqlite_finalize(atom db_handle, atom stmt) >
Removes the sql stmt from memory.
Can return SQL_ABORT, usually just VOID
example :
Void = sqlite_finalize(db, stmt)
(See sqlite_insert.exw)
< procedure sqlite_bind_int(atom db, atom stmt, integer param_index, integer val) >
< procedure sqlite_bind_double(atom db, atom stmt, integer param_index, atom val) >
< procedure sqlite_bind_text(atom db, atom stmt, integer param_index, sequence val) >
< procedure sqlite_bind_blob(atom db, atom stmt, integer param_index, object val) >
Binds a value to a a prepared stmt, ready for execution.
(See sqlite_insert.exw)
< function sqlite_column_int(atom db, atom stmt, integer column_num) >
< function sqlite_column_bytes(atom db, atom stmt, integer column_num) >
< function sqlite_column_text(atom db, atom stmt, integer column_num) >
< function sqlite_column_blob(atom db, atom stmt, integer column_num) >
These routines return information about the information in a single column of the current result row of a query.
Can't really say much more than that atm, because I don't really understand them myself.
Here's an article.
http://interactive.linuxjournal.com/article/7803
< function sqlite_query_database(sequence database, sequence cmd) >
Opens, queries, and closes a database. One line access to databases.
Returns the dataset, or a sequence of length 0 if no data.
example :
data = sqlite_query_database("newdb.sql", "create table blah (id INTEGER PRIMARY KEY, fruit TEXT)")
will create a new database, create a table within that database, then close the database.
Note - this is inherently slower than opening the database, doing several get_tables, then closing
the database, and is much much slower than preparing atatements, and binding values. Suggest you use
this in speed critical applications where you only have an occasional query.
Also, some poeple like to open the database at the beginning of their application, or at least while
large chunks of the program are running. In that case, don't use this, as this repeatedly opens and
closes the database.
< function compress(sequence thing) >
< function decompress(sequence thing) >
Comress and decompresses sequence.
Returns the compressed or decompressed sequence.
Usefull for compressi