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