Driver Guide

Author : Ashley G Truter
Updated: 16-Oct-2011
Purpose: Describes the design and operation of the SQLite Driver.

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.

SQLITE_OKSuccessful result.
SQLITE_BUSYThe database file is locked.
SQLITE_ROWsqlite_step has another row ready.
SQLITE_DONEsqlite_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 RoutineC/C++ API

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.

Open a SQLite database
create - Create database if non-existent
flat - Do not return rows as blocks
direct - Do not mold/load REBOL values
timeout - Specify alternate retry limit (default is 5)
format - Format output
info - Obtain column names and widths
log - Log all SQL statements
Database tasks
analyze - Gather statistics on indexes
vacuum - Reclaim unused space
check - Perform an integrity check
Information about a database object
index - Describes an index
indexes - Indexes on table
fkeys - Foreign keys that reference table
Close database connection
Explain an SQL statement
Export result table to a CSV file
Import CSV file into a table
no-header - Use generic column names
List all indexes
Return row count
Prepare and execute an SQL statement
direct - Do not mold/load REBOL values
List all tables


Seven directives, all of them set via CONNECT refinements, control various aspects of the driver's behaviour.

retryNumber of 1 second intervals to try if SQLITE_BUSY
flat?Don't return rows as blocks
direct?Bypass mold/load conversions
log?SQL statement logging
format?Format output
col-info?column names and widths

While it is rare that you need to change directives from the console, it can be quite useful when debugging to toggle the format? directive as follows:

>> SQLite/format?: true
>> SQL "select * from my-table"
>> SQLite/format?: false

Using the Driver

SQL Statements

The SQL function supports statements in one of two forms:

Examples of some string statements are:

CONNECT/create %test.db
SQL "create table t (col_1, col_2, col_3)"
SQL {insert into t values (1, '1-Jan-2000', '"A string"')}
SQL "select * from t where col_1 = 1"
SQL "select * from t where col_2 = '1-Jan-2000'"
SQL {select * from t where col_3 = '"A string"'}

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 ["insert into t values (?, ?, ?)" 1 1-Jan-2000 "A string"]
SQL ["select * from t where col_1 = ?" 1]
SQL ["select * from t where col_2 = ?" 1-Jan-2000]
SQL ["select * from t where col_3 = ?" "A string"]

As can be seen from these examples, value binding implicitly quotes REBOL values so you don't have to construct cumbersome statement strings yourself!

Column Names

Sometimes you may need to know the column names (and widths) used in the last SELECT statement. These are available in the SQLite/columns block and can be used as follows:

>> SQLite/col-info?: true
== true
>> SQL "select * from t"
== []
>> SQLite/columns
== ["c1" "c2" "c3"]
>> SQL "select count(*) from t"
== [[0]]
>> SQLite/columns
== ["count(*)"]
>> SQLite/col-info?: false
== false

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:


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:

repeat i 1000 [
    SQL reduce [
        "insert into t values (?, ?, ?)" i now/date + i join "String " i

Not only will this take a long time, but it will cause significant disk thrashing.

Fortunately, SQLite lets you turn auto-commit off and on with the begin and commit statements. Doing the following:

SQL "begin"
repeat i 1000 [
    SQL reduce [
        "insert into t values (?, ?, ?)" i now/date + i join "String " i
SQL "commit"

will dramatically improve the speed of this operation.

Don't forget to commit as the database file will be locked until auto-commit is turned back on.