Under development — the help is being filled in plugin by plugin.
Help/Plugin reference/sqlite
Plugin reference

sqlite

Embedded SQL via SQLite — prepared statements, transactions, queries.

47 functionsnamespace sqlitesource plugins/sqlite/SqlitePlugin.c

Written from a line-by-line source review; every example output is from a real run.

Introduction

The sqlite plugin is a thin, 1:1 wrapper over the SQLite C-API: every script-level call maps to exactly one sqlite3_* function. You can open SQLite databases (on a file or in memory), run SQL statements, bind parameters and read result rows with prepared statements, and set up nine different event handlers (hooks). The plugin uses the system libsqlite3 library.

A handle-based model

The plugin is handle-based: Open / OpenEx return an integer connection handle, and Prepare returns a statement handle. Every further operation takes these. Close closes the connection, Finalize releases the prepared statement. Close automatically finalizes any still-open statements belonging to that connection. When the plugin shuts down, every open statement is finalized and every connection is closed.

Return codes (rc) and types

Most functions return the SQLite result code (rc) as an integer. The most common are: 0 = SQLITE_OK (success), 100 = SQLITE_ROW (Step produced a result row), 101 = SQLITE_DONE (Step finished). The column types: 1 = INTEGER, 2 = FLOAT, 3 = TEXT, 4 = BLOB, 5 = NULL. The OpenEx flags: 1 = READONLY, 2 = READWRITE, 4 = CREATE (these can be combined).

Loading the plugin

plugin "../plugins/print/PrintPlugin";
plugin "../plugins/sqlite/SqlitePlugin";

A typical flow — the full lifecycle

i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(id INTEGER PRIMARY KEY, name
TEXT)");
$st = sqlite.Prepare($db, "INSERT INTO t(name) VALUES(?)");
sqlite.BindText($st, 1, "Ada");
sqlite.Step($st); // 101 = DONE
sqlite.Finalize($st);
sqlite.Close($db);

What to know about every function (the basics)

  • Strict error handling: an invalid connection or statement handle, and bad SQL in Prepare, raise a runtime error and the script stops. Exec, however, returns SQLite's rc code for bad SQL (it does not fail), and the error text comes from ErrMsg/ErrCode.

  • The column-read functions (Column*) are only valid after Step returned SQLITE_ROW (100). The plugin makes a fresh copy of the returned strings and blobs, so they remain safe to use after the next Step/Reset/Finalize.

  • Parameter indexes are 1-based (as SQLite expects), but column indexes are 0-based.

  • The in-memory database (the “:memory:” path) is a fast, volatile store — ideal for testing and transient work; it disappears at the end of the process.

  • The plugin loads libsqlite3 with dlopen on first use; if the library is not installed, loading still succeeds, but the sqlite.* calls fail.

  • NULL byte in text: SQLite allows it, but a DominScript string is truncated at the first 0 byte. For such data, use blob binding/reading (BindBlob/ColumnBlob).

How to read the signatures

$conn is the connection handle, $stmt the prepared statement, $idx a column or parameter index. The type after the -> arrow is the return type; rc denotes an integer result code. For example, sqlite.BindText($stmt, $idx, $val) -> rc takes three arguments and returns a result code.

Connection

Opening and closing a database, running SQL, and querying the version.

sqlite.Open

sqlite.Open(path) -> conn

Opens (or creates) a database at the given path and returns the connection handle.

Parameters
Parameter Type Description
path string The path of the database file; “:memory:” opens a volatile in-memory database.
Return value

The connection handle (a positive integer). A failed open causes a runtime error.

Example
i32 $db;
$db = sqlite.Open(":memory:");
printf("%d\n", $db);
sqlite.Close($db);
Output after running
1
When to use

The simplest open: if the file does not exist, it is created (in read-write mode). For finer control (read-only, no creation) use OpenEx. Always call Close at the end.

sqlite.OpenEx

sqlite.OpenEx(path, flags) -> conn

Like Open, but you control the open mode with flags.

Parameters
Parameter Type Description
path string The path of the database file.
flags int Open flags: 1=READONLY, 2=READWRITE, 4=CREATE (combine by adding).
Return value

The connection handle. A failed open causes a runtime error.

Example
i32 $db;
// open read-only (the file must exist)
$db = sqlite.OpenEx("data.db", 1);
sqlite.Close($db);
Output after running
(depends on the file)
When to use

When you want to control access: for example read-only (READONLY) on an existing database, or with creation allowed (READWRITE + CREATE = 6) for a new one.

sqlite.Close

sqlite.Close(conn) -> rc

Closes the connection, automatically finalizing its open statements first.

Parameters
Parameter Type Description
conn int The handle of the connection to close.
Return value

SQLite's result code (0 = OK). An invalid handle causes a runtime error.

Example
i32 $db;
$db = sqlite.Open(":memory:");
printf("%d\n", sqlite.Close($db));
Output after running
0
When to use

Always call it when you are done, so the connection and all resources are released. You need not finalize every statement separately — Close does that.

sqlite.Exec

sqlite.Exec(conn, sql) -> rc

Runs one or more SQL statements in a single step, without returning result rows.

Parameters
Parameter Type Description
conn int The connection handle.
sql string The SQL to run (can be several statements separated by semicolons).
Return value

SQLite's result code (0 = OK; for bad SQL the relevant error code, NOT a runtime error). The error text is available via ErrMsg.

Example
i32 $db;
$db = sqlite.Open(":memory:");
printf("%d\n", sqlite.Exec($db, "CREATE TABLE t(id INTEGER)"));
// bad SQL: rc is not 0
printf("%d\n", sqlite.Exec($db, "SELECT * FROM none"));
sqlite.Close($db);
Output after running
0
1
When to use

For schema operations (CREATE/DROP), simple INSERT/UPDATE/DELETE, transaction control (BEGIN/COMMIT), where you do not need to read result rows. To read results, use Prepare + Step + Column. For parameterized/repeated statements use Prepare (safer and faster).

sqlite.Version

sqlite.Version() -> string

Returns the version string of the loaded SQLite library.

Parameters

This function takes no arguments.

Return value

The version string (for example “3.45.1”).

Example
printf("%s\n", sqlite.Version());
Output after running
<version>
When to use

For diagnostics and compatibility checks: it tells you which libsqlite3 was loaded on the system.

Errors and info

Querying the most recent error and database-state information.

sqlite.ErrMsg

sqlite.ErrMsg(conn) -> string

Returns the text description of the connection's most recent error.

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The error message (an empty string if there is no error).

Example
i32 $db;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "SELECT * FROM none");
printf("%s\n", sqlite.ErrMsg($db));
sqlite.Close($db);
Output after running
no such table: none
When to use

For details after an Exec (or other operation) error. Since Exec returns only an rc code, this gives you the actual error reason.

sqlite.ErrCode

sqlite.ErrCode(conn) -> int

Returns the primary error code of the connection's most recent operation.

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The SQLite primary error code (0 = no error).

Example
i32 $db;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "SELECT * FROM none");
printf("%d\n", sqlite.ErrCode($db));
sqlite.Close($db);
Output after running
1
When to use

For programmatic error handling, when you want to branch on the kind of error (not its text). For finer distinction, use ExtendedErrCode.

sqlite.ExtendedErrCode

sqlite.ExtendedErrCode(conn) -> int

Returns the extended (more detailed) error code of the connection's most recent operation.

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The SQLite extended error code.

Example
i32 $db;
$db = sqlite.Open(":memory:");
printf("%d\n", sqlite.ExtendedErrCode($db));
sqlite.Close($db);
Output after running
0
When to use

When the primary code is not precise enough: the extended code also tells you the sub-case (for example which constraint was violated).

sqlite.Changes

sqlite.Changes(conn) -> int

Returns the number of rows modified by the MOST RECENT statement (INSERT/UPDATE/DELETE).

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The number of rows affected by the most recent statement.

Example
i32 $db;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(x)");
sqlite.Exec($db, "INSERT INTO t VALUES(1)");
printf("%d\n", sqlite.Changes($db));
sqlite.Close($db);
Output after running
1
When to use

To check how many rows an UPDATE/DELETE affected — for example whether the update actually found a matching row.

sqlite.TotalChanges

sqlite.TotalChanges(conn) -> int

Returns the CUMULATIVE number of rows modified since the connection was opened.

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The cumulative number of rows modified over the connection's lifetime.

Example
i32 $db;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(x)");
sqlite.Exec($db, "INSERT INTO t VALUES(1)");
sqlite.Exec($db, "INSERT INTO t VALUES(2)");
printf("%d\n", sqlite.TotalChanges($db));
sqlite.Close($db);
Output after running
2
When to use

For a cumulative modification count over the connection's whole life — for example for simple statistics or progress reporting.

sqlite.LastInsertRowId

sqlite.LastInsertRowId(conn) -> int

Returns the identifier (ROWID) of the most recently inserted row.

Parameters
Parameter Type Description
conn int The connection handle.
Return value

The row id of the most recent INSERT.

Example
i32 $db;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(id INTEGER PRIMARY KEY, n
TEXT)");
sqlite.Exec($db, "INSERT INTO t(n) VALUES('a')");
printf("%d\n", sqlite.LastInsertRowId($db));
sqlite.Close($db);
Output after running
1
When to use

To get the auto-generated primary key (INTEGER PRIMARY KEY) right after an insert — for example to refer to the just-created record.

Prepared statements

Precompiling SQL, running it step by step, re-running it. The lifecycle is: Prepare → (Bind*) → Step* → Finalize.

sqlite.Prepare

sqlite.Prepare(conn, sql) -> stmt

Precompiles an SQL statement and returns the statement handle.

Parameters
Parameter Type Description
conn int The connection handle.
sql string The SQL to precompile (with parameter placeholders, for example ? or :name).
Return value

The statement handle. Bad SQL causes a runtime error (the detail in ErrMsg).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(id INTEGER)");
$st = sqlite.Prepare($db, "INSERT INTO t VALUES(?)");
printf("%d\n", sqlite.BindParameterCount($st));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
1
When to use

For any statement to which you bind a parameter, from which you read result rows, or which you run repeatedly. You fill the ? placeholders with the Bind* functions.

sqlite.Finalize

sqlite.Finalize(stmt) -> rc

Releases the prepared statement.

Parameters
Parameter Type Description
stmt int The handle of the statement to release.
Return value

SQLite's result code (0 = OK). An invalid handle causes a runtime error.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 1");
printf("%d\n", sqlite.Finalize($st));
sqlite.Close($db);
Output after running
0
When to use

Close every Prepare with exactly one Finalize when you are done with it. (Close automatically finalizes a connection's open statements.)

sqlite.Reset

sqlite.Reset(stmt) -> rc

Resets the statement for re-running; the bound parameters are kept.

Parameters
Parameter Type Description
stmt int The statement handle.
Return value

SQLite's result code.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(x)");
$st = sqlite.Prepare($db, "INSERT INTO t VALUES(?)");
sqlite.BindInt($st, 1, 5);
sqlite.Step($st);
printf("%d\n", sqlite.Reset($st));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

To re-run the same precompiled statement in a loop: after Step, Reset, then new Bind* and another Step. Much faster than calling Prepare again each round.

sqlite.ClearBindings

sqlite.ClearBindings(stmt) -> rc

Clears all parameter values bound to the statement (sets them to NULL).

Parameters
Parameter Type Description
stmt int The statement handle.
Return value

SQLite's result code.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
sqlite.BindInt($st, 1, 9);
printf("%d\n", sqlite.ClearBindings($st));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

After Reset, when you want to start the next run with a clean slate and not carry over the old bindings. (Reset alone keeps the bindings.)

sqlite.Step

sqlite.Step(stmt) -> rc

Performs one step on the statement: advances to the next result row, or finishes the run.

Parameters
Parameter Type Description
stmt int The statement handle.
Return value

100 = SQLITE_ROW (a result row is available, the columns can be read), 101 = SQLITE_DONE (the run finished), or another error code.

Example
i32 $db;
i32 $st;
i32 $rc;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(x)");
sqlite.Exec($db, "INSERT INTO t VALUES(1)");
$st = sqlite.Prepare($db, "SELECT x FROM t");
$rc = sqlite.Step($st);
printf("%d\n", $rc); // 100 = ROW
printf("%d\n", sqlite.Step($st)); // 101 = DONE
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
100
101
When to use

To iterate a SELECT's result: repeat Step while it returns 100 (ROW), reading the columns each round with the Column* functions; 101 (DONE) marks the end. For INSERT/UPDATE/DELETE a single Step suffices and returns 101.

Parameter binding

Binding values to the statement's ? / :name placeholders. Parameter indexes are 1-based.

sqlite.BindParameterCount

sqlite.BindParameterCount(stmt) -> int

Gives the number of parameter placeholders in the statement.

Parameters
Parameter Type Description
stmt int The statement handle.
Return value

The number of parameters.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?, ?");
printf("%d\n", sqlite.BindParameterCount($st));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
2
When to use

To check how many values you must bind, or to iterate over the parameters in a loop.

sqlite.BindParameterIndex

sqlite.BindParameterIndex(stmt, name) -> int

Gives the 1-based index of a named parameter (for example :name).

Parameters
Parameter Type Description
stmt int The statement handle.
name string The parameter name including its prefix (for example “:name”).
Return value

The parameter's 1-based index, or 0 if there is no parameter with that name.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT :a, :b");
printf("%d\n", sqlite.BindParameterIndex($st, ":b"));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
2
When to use

For named parameters: resolve the name to an index once, then bind by index with the Bind* functions.

sqlite.BindParameterName

sqlite.BindParameterName(stmt, idx) -> string

Gives the name of the parameter at the given 1-based index.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
Return value

The parameter's name (an empty string if the parameter is anonymous, i.e. ?).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT :a");
printf("%s\n", sqlite.BindParameterName($st, 1));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
:a
When to use

For diagnostics or generic binding, when you want to learn a parameter's name from its index.

sqlite.BindInt

sqlite.BindInt(stmt, idx, val) -> rc

Binds an integer value to the parameter at the given 1-based index.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
val int The integer value to bind.
Return value

SQLite's result code (0 = OK).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindInt($st, 1, 42));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For binding an integer parameter value. For large (64-bit) numbers, use BindInt64.

sqlite.BindInt64

sqlite.BindInt64(stmt, idx, val) -> rc

Binds a 64-bit integer value to the parameter.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
val int The 64-bit integer to bind.
Return value

SQLite's result code (0 = OK).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindInt64($st, 1, 9000000000));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For large integers (a timestamp in milliseconds, a large identifier) that do not fit in 32 bits.

sqlite.BindDouble

sqlite.BindDouble(stmt, idx, val) -> rc

Binds a floating-point value to the parameter.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
val double The floating-point value to bind.
Return value

SQLite's result code (0 = OK).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindDouble($st, 1, 9.5));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For binding fractional values (a price, a ratio, a measurement) to a REAL column.

sqlite.BindText

sqlite.BindText(stmt, idx, val) -> rc

Binds a text value to the parameter (SQLite makes a safe copy of it).

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
val string The text to bind.
Return value

SQLite's result code (0 = OK).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindText($st, 1, "Ada"));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For safely inserting user text — this protects against SQL injection, unlike concatenating into the SQL. The plugin copies at bind time, so the source string's scope does not matter.

sqlite.BindBlob

sqlite.BindBlob(stmt, idx, val) -> rc

Binds binary data (a blob) to the parameter.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
val blob The binary data to bind.
Return value

SQLite's result code (0 = OK).

Example
blob $B[8];
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$B[0]=1; $B[1]=2; $B.Length=2;
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindBlob($st, 1, $B));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For storing raw bytes (an image, a hash, serialized data) in a BLOB column, where the 0 byte must be preserved. ColumnBlob reads it back.

sqlite.BindNull

sqlite.BindNull(stmt, idx) -> rc

Binds a NULL value to the parameter.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The parameter's 1-based index.
Return value

SQLite's result code (0 = OK).

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT ?");
printf("%d\n", sqlite.BindNull($st, 1));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
0
When to use

For inserting an explicit NULL (to signal a missing or unknown value) at a parameter position.

Column access

Reading the columns of a result row. Valid only after Step returned SQLITE_ROW (100). Column indexes are 0-based.

sqlite.ColumnCount

sqlite.ColumnCount(stmt) -> int

Gives the number of columns in the result row.

Parameters
Parameter Type Description
stmt int The statement handle.
Return value

The number of columns.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 1, 2, 3");
printf("%d\n", sqlite.ColumnCount($st));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
3
When to use

To iterate over the columns, or to check the result's shape in a generic display.

sqlite.ColumnType

sqlite.ColumnType(stmt, idx) -> int

Gives the type of the given column's current value.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The type code: 1=INTEGER, 2=FLOAT, 3=TEXT, 4=BLOB, 5=NULL.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 'hello'");
sqlite.Step($st);
printf("%d\n", sqlite.ColumnType($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
3
When to use

When a column's type varies (or may be NULL) and you want to pick the matching Column* reader. Also useful to detect NULL (5).

sqlite.ColumnName

sqlite.ColumnName(stmt, idx) -> string

Gives the name of the given column.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's name.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 1 AS apple");
printf("%s\n", sqlite.ColumnName($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
apple
When to use

For printing a header or for name-based processing. ColumnName can be queried even before Step (the schema is known).

sqlite.ColumnInt

sqlite.ColumnInt(stmt, idx) -> int

Returns the given column's value as an integer.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's integer value.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 42");
sqlite.Step($st);
printf("%d\n", sqlite.ColumnInt($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
42
When to use

For reading integer columns (an identifier, a count) from a result row. For large values, ColumnInt64.

sqlite.ColumnInt64

sqlite.ColumnInt64(stmt, idx) -> int

Returns the given column's value as a 64-bit integer.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's 64-bit integer value.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 9000000000");
sqlite.Step($st);
printf("%d\n", sqlite.ColumnInt64($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
9000000000
When to use

For large integers (a timestamp, a large identifier) that do not fit in 32 bits.

sqlite.ColumnDouble

sqlite.ColumnDouble(stmt, idx) -> double

Returns the given column's value as a floating-point number.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's floating-point value.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 9.5");
sqlite.Step($st);
printf("%f\n", sqlite.ColumnDouble($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
9.500000
When to use

For reading fractional values (a price, a ratio, a measurement) from a REAL column.

sqlite.ColumnText

sqlite.ColumnText(stmt, idx) -> string

Returns the given column's value as text (the plugin makes a fresh copy).

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's text value.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 'Ada'");
sqlite.Step($st);
printf("%s\n", sqlite.ColumnText($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
Ada
When to use

For reading text columns. The returned string is an independent copy, so it is usable after the next Step/Finalize. For data containing 0 bytes, ColumnBlob is correct.

sqlite.ColumnBlob

sqlite.ColumnBlob(stmt, idx) -> blob

Returns the given column's value as a raw blob (a fresh copy).

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The column's binary value as a blob.

Example
blob $B[8];
blob $Out[16];
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
sqlite.Exec($db, "CREATE TABLE t(b BLOB)");
$B[0]=1; $B[1]=2; $B[2]=3; $B.Length=3;
$st = sqlite.Prepare($db, "INSERT INTO t VALUES(?)");
sqlite.BindBlob($st, 1, $B);
sqlite.Step($st);
sqlite.Finalize($st);
$st = sqlite.Prepare($db, "SELECT b FROM t");
sqlite.Step($st);
$Out = sqlite.ColumnBlob($st, 0);
printf("%d\n", $Out.Length);
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
3
When to use

For reading binary data (an image, a hash, serialized content) from a BLOB column, where the 0 byte must be preserved.

sqlite.ColumnBytes

sqlite.ColumnBytes(stmt, idx) -> int

Gives the size in bytes of the given column's text/binary value.

Parameters
Parameter Type Description
stmt int The statement handle.
idx int The column's 0-based index.
Return value

The value's length in bytes.

Example
i32 $db;
i32 $st;
$db = sqlite.Open(":memory:");
$st = sqlite.Prepare($db, "SELECT 'hello'");
sqlite.Step($st);
printf("%d\n", sqlite.ColumnBytes($st, 0));
sqlite.Finalize($st);
sqlite.Close($db);
Output after running
5
When to use

To query a TEXT/BLOB column's size in advance (for example for buffer sizing or to check the data's length).

Event handlers (hooks)

Watching database events: the function takes the name of a script-level callback that runs when the event occurs. Each returns 0 when set. Exec/Prepare cannot be called from the callback on the same connection.

sqlite.SetUpdateHook

sqlite.SetUpdateHook(conn, callbackName) -> int

Sets a callback that runs after every row-level INSERT/UPDATE/DELETE.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the script-level function to call.
Return value

0 (the success of setting it).

Example
// the callback receives an UpdateEvent payload
// (op, rowid, dbName, tableName); the return is ignored
sqlite.SetUpdateHook($db, "onRowChanged");
Output after running
(fires when the hook callback runs)
When to use

For logging data changes, cache invalidation, or tracking modified rows (audit). The payload carries the operation, the rowid, and the table name.

sqlite.SetCommitHook

sqlite.SetCommitHook(conn, callbackName) -> int

Sets a callback that runs before every transaction commit.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0.

Example
sqlite.SetCommitHook($db, "onCommit");
Output after running
(runs at commit)
When to use

For commit-time checks. A non-zero return from the callback VETOES the commit (rolls back the transaction) — so you can conditionally reject a save.

sqlite.SetRollbackHook

sqlite.SetRollbackHook(conn, callbackName) -> int

Sets a callback that runs on every transaction rollback.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0.

Example
sqlite.SetRollbackHook($db, "onRollback");
Output after running
(runs at rollback)
When to use

To detect a rollback: for state restoration, logging, or notifying the user that a transaction was aborted.

sqlite.SetBusyHandler

sqlite.SetBusyHandler(conn, callbackName) -> int

Sets a callback that decides whether to retry when the database is locked (busy).

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0.

Example
sqlite.SetBusyHandler($db, "onBusy");
Output after running
(runs when locked)
When to use

For concurrent access: the callback receives the number of attempts so far; a non-zero return means retry, 0 means give up. So you can build a flexible waiting strategy.

sqlite.SetProgressHandler

sqlite.SetProgressHandler(conn, opsBetween, callbackName) ->
int

Sets a callback that runs periodically during a long query.

Parameters
Parameter Type Description
conn int The connection handle.
opsBetween int How many virtual-machine instructions between callback invocations.
callbackName string The name of the function to call.
Return value

0.

Example
// call onProgress roughly every 1000 operations
sqlite.SetProgressHandler($db, 1000, "onProgress");
Output after running
(runs during a long query)
When to use

To interrupt long operations or report progress: a non-zero return from the callback interrupts the running query (SQLITE_INTERRUPT). So you can keep a slow query from running forever.

sqlite.SetAuthorizer

sqlite.SetAuthorizer(conn, callbackName) -> int

Sets a callback that, during each precompilation (Prepare), allows or denies individual operations.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0.

Example
sqlite.SetAuthorizer($db, "onAuth");
Output after running
(runs during Prepare)
When to use

For access control: the callback receives the intended operation (and the table/column) and answers with 0=allow, 1=deny (SQLITE_DENY), 2=hide-field (SQLITE_IGNORE). Useful for restricting untrusted SQL.

sqlite.SetWalHook

sqlite.SetWalHook(conn, callbackName) -> int

Sets a callback that runs after a WAL (write-ahead log) commit.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0.

Example
sqlite.SetWalHook($db, "onWal");
Output after running
(runs at a WAL commit)
When to use

In WAL mode, to watch the log file's growth (the callback receives the number of log pages) — for example to schedule a checkpoint.

sqlite.SetTraceHook

sqlite.SetTraceHook(conn, mask, callbackName) -> int

Sets a callback to trace SQL execution events.

Parameters
Parameter Type Description
conn int The connection handle.
mask int The bitmask of events (for example the statement event).
callbackName string The name of the function to call.
Return value

0.

Example
// statement-level tracing (mask 1)
sqlite.SetTraceHook($db, 1, "onTrace");
Output after running
(runs when SQL executes)
When to use

For debugging and profiling: the callback receives the text of the executed SQL, so you can log or measure the actual queries.

sqlite.SetPreUpdateHook

sqlite.SetPreUpdateHook(conn, callbackName) -> int

Sets a callback that runs directly BEFORE a row is modified, with access to the old/new values.

Parameters
Parameter Type Description
conn int The connection handle.
callbackName string The name of the function to call.
Return value

0. (Requires libsqlite3's SQLITE_ENABLE_PREUPDATE_HOOK build flag; without it, this call raises a runtime error.)

Example
sqlite.SetPreUpdateHook($db, "onPreUpdate");
Output after running
(runs before a modification)
When to use

For detailed audit or validation, when you need both the value before AND after the modification. In the callback, read the old and new cells with the PreUpdateOld/New/Count/Depth functions.

PreUpdate values

Valid only inside the PreUpdate hook's callback: they read the cell values before and after the modification. Called elsewhere, they raise a runtime error.

sqlite.PreUpdateCount

sqlite.PreUpdateCount() -> int

Gives the number of columns in the affected row of the preupdate event.

Parameters

This function takes no arguments.

Return value

The number of columns. Callable only in the preupdate callback.

Example
// inside the SetPreUpdateHook callback:
// i32 $n;
// $n = sqlite.PreUpdateCount();
// ... per-column processing ...
Output after running
(valid inside the callback)
When to use

In the preupdate callback, to iterate over the columns: you call PreUpdateOld/New this many times.

sqlite.PreUpdateDepth

sqlite.PreUpdateDepth() -> int

Gives the trigger nesting depth of the preupdate event.

Parameters

This function takes no arguments.

Return value

The trigger depth (0 = a direct operation). Callable only in the preupdate callback.

Example
// inside the callback:
// if (sqlite.PreUpdateDepth() == 0) { ... direct ... }
Output after running
(valid inside the callback)
When to use

To tell a direct (user) modification from one triggered by a trigger — for example to avoid double-logging trigger chains.

sqlite.PreUpdateOld

sqlite.PreUpdateOld(colIdx) -> any

Returns a column's value BEFORE the modification. Valid only for UPDATE/DELETE (an INSERT has no old value).

Parameters
Parameter Type Description
colIdx int The column's 0-based index.
Return value

The old cell value according to its type (integer, floating-point, string, blob, or null). Callable only in the preupdate callback.

Example
// inside the callback, for UPDATE/DELETE:
// string $old;
// $old = sqlite.PreUpdateOld(1);
Output after running
(valid inside the callback)
When to use

For an audit log: you can record what the value was before the modification. The type conversion is automatic per the cell's SQLite type.

sqlite.PreUpdateNew

sqlite.PreUpdateNew(colIdx) -> any

Returns a column's value AFTER the modification. Valid only for INSERT/UPDATE (a DELETE has no new value).

Parameters
Parameter Type Description
colIdx int The column's 0-based index.
Return value

The new cell value according to its type. Callable only in the preupdate callback.

Example
// inside the callback, for INSERT/UPDATE:
// string $new;
// $new = sqlite.PreUpdateNew(1);
Output after running
(valid inside the callback)
When to use

To check or log the post-modification value — together with PreUpdateOld, to assemble the full “before/after” picture in an audit system.

Practical notes

What the sqlite plugin is good for

  • Embedded, file- or memory-based database handling without an external server (configuration, a cache, a local data store).

  • Storing and querying structured data with SQL, with transactional guarantees.

  • Safe, parameterized queries (prepared statements) to avoid SQL injection and to speed up repeated statements.

  • Watching database events (hooks): logging, validation, statistics, interrupting long operations.

Exec vs Prepare

Exec is fast and simple for schema and one-off statements, but it does not return result rows, and you would have to concatenate inserted values into the SQL as text yourself (which is risky). To read results, to safely insert user data, or to run the same statement repeatedly (with different parameters), use Prepare: prepare once, then Bind* + Step + (with Reset) again. This is at once safer (parameter binding) and faster (single compilation).

Indexing conventions

Mind the two different indexings: parameter indexes (Bind*) are 1-based, but column indexes (Column*) are 0-based. This directly follows the SQLite C-API convention.

Hooks

The nine event handlers take the name of a script-level callback that runs, on the same thread, when the given event occurs. The callback receives an event payload (the types are documented in the source), and its return value is controlling for some hooks (for example a non-zero return from the Commit hook rolls back the transaction, and from the Progress hook interrupts the operation). Important: Exec/Prepare CANNOT be called from a hook callback on the SAME connection (the plugin prevents this with a runtime error to avoid a deadlock). The PreUpdate hook and its value accessors require libsqlite3's SQLITE_ENABLE_PREUPDATE_HOOK build flag; without it only those drop out, the other eight hooks work.

Common pitfalls

  • Column* before/after Step's ROW: the column readers are valid only after a SQLITE_ROW (100) return. After DONE (101) there is no current row.

  • Parameter vs column index: Bind* indexes from 1, Column* from 0.

  • Exec's error is not a runtime error: Exec returns an rc code for bad SQL — you must check it (ErrCode/ErrMsg) yourself, or the error goes unnoticed.

  • Statement leak: close every Prepare with Finalize (or leave it to Close). Many open, unfinalized statements can exhaust the statement registry.

  • NULL byte in text: a TEXT column may contain 0 bytes, but reading it as a string truncates — there ColumnBlob is correct.

Error handling

A wrong argument count or type, an invalid connection or statement handle, bad SQL in Prepare, and an Exec/Prepare started from a hook callback on the same connection are reported by the runtime as a runtime error, and the script stops. Exec, by contrast, signals bad SQL not as a runtime error but with SQLite's rc code — it is worth checking this return value and, on error, querying the details with ErrMsg/ErrCode.

The web web-utility plugin

HTML, URL, Base64, HTTP request and response — complete function reference