Driver Guide

The SQLite Driver object

The driver is implemented as a context named SQLite with nine functions exported to the global context. By convention, and to distinguish these “database” words from other words, the function names always appear in upper-case.

Result codes

Of the twenty nine SQLite result codes defined here, only four are used by the driver.

Code

Meaning

SQLITE_OK

Successful result.

SQLITE_BUSY

The database file is locked.

SQLITE_ROW

sqlite_step has another row ready.

SQLITE_DONE

sqlite_step has finished executing.

Library functions

Nineteen SQLite library functions are implemented via matching routine! names, except that the sqlite3_ prefix is replaced with an aster and underscores are replaced with hyphens. All these routines, apart from *open and *close, are used in the SQL function.

REBOL Routine

C/C++ API

bind-blob

sqlite3_bind_blob

bind-double

sqlite3_bind_double

bind-int

sqlite3_bind_int

bind-null

sqlite3_bind_null

bind-text

sqlite3_bind_text

close

sqlite3_close

column-blob

sqlite3_column_blob

column-count

sqlite3_column_count

column-double

sqlite3_column_double

column-integer

sqlite3_column_int

column-name

sqlite3_column_name

column-text

sqlite3_column_text

column-type

sqlite3_column_type

errmsg

sqlite3_errmsg

finalize

sqlite3_finalize

open

sqlite3_open

prepare

sqlite3_prepare

reset

sqlite3_reset

step

sqlite3_step

Database access functions

The nine database access functions are exported to the global context and use the routines described above to open, access and close SQLite database files.

If the database was opened with CONNECT/direct then the last statement would be written as:

SQL "select * from t where col_3 = 'A string'"

Value Binding

The SQLite driver supports value binding which makes it much easier to generate dynamic SQL statements within your code. Value binding works by replacing each unquoted ? within your statement with the next value in the statement block. Using value binding, the examples above would be written as:

SQL Buffer

SQL statements return their result set in a 32Kb value buffer which is returned as a reference. If you need to preserve a copy of these values, because another SQL statement will be executed, then make sure you copy the result set; as in:

data: copy SQL "select * from t"

Unlike most REBOL functions, which return a copy of their result set, the SQLite driver returns a reference for several good reasons:

A large buffer is allocated once at context creation.

Copying a result set doubles the amount of memory used.

It is trivial to make this reference a copy in your code, however the reverse is not true.

Inline SQL statements (and many others) don't require a copy (this is especially true in the case of foreach constructs that iterate over the result set with no further SQL statements.)

Transactions

SQLite is auto-commit by default which means that the changes caused by each statement are written out at the conclusion of the statement. This is good for concurrency (lock duration is minimized) but not so good when you need a set of statements to succeed or fail together (i.e. a logical "transaction"), or you have an INSERT in a tight loop. Consider the following: