`(sigil sqlite)`

(sigil sqlite) - SQLite Database Bindings

Provides SQLite database access for Sigil applications.

Low-level API (native): (sqlite-open path) - Open database, returns handle (sqlite-close db) - Close database (sqlite-exec db sql) - Execute SQL (DDL, simple statements) (sqlite-prepare db sql) - Prepare a statement (sqlite-bind stmt idx val) - Bind parameter (1-based index) (sqlite-step stmt) - Step through results ('row, 'done, #f) (sqlite-reset stmt) - Reset statement to initial state (sqlite-finalize stmt) - Finalize statement (sqlite-column-count stmt) - Get number of result columns (sqlite-column-name stmt idx) - Get column name (0-based index) (sqlite-column stmt idx) - Get column value (0-based index) (sqlite-last-insert-rowid db) - Get last inserted rowid (sqlite-changes db) - Get rows changed by last statement (sqlite-errmsg db) - Get last error message

High-level API (Scheme): (sqlite-query db sql . params) - Execute query, return list of rows (sqlite-query-row db sql . params) - Execute query, return first row (sqlite-run db sql . params) - Execute statement with params

Exports

sqlite-db?procedure

Check if a value is a SQLite database handle.

(sqlite-db? db)           ; => #t
(sqlite-db? "not a db")  ; => #f

sqlite-stmt?procedure

Check if a value is a SQLite prepared statement.

(sqlite-stmt? stmt)         ; => #t
(sqlite-stmt? "not stmt")   ; => #f

sqlite-openprocedure

Open a SQLite database file.

Returns a database handle on success, or #f on failure. Creates the file if it does not exist. Use ":memory:" for an in-memory database.

(sqlite-open "app.db")      ; => #<sqlite-db>
(sqlite-open ":memory:")    ; => #<sqlite-db>

sqlite-closeprocedure

Close a SQLite database handle.

All prepared statements should be finalized before closing.

(sqlite-close db)

sqlite-execprocedure

Execute a SQL string directly.

Suitable for DDL statements and simple queries that don't need parameter binding. Returns #t on success, #f on error.

(sqlite-exec db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
(sqlite-exec db "PRAGMA journal_mode=WAL")

sqlite-prepareprocedure

Prepare a SQL statement for execution.

Returns a prepared statement handle, or #f on error. Use sqlite-bind to set parameters and sqlite-step to execute.

(define stmt (sqlite-prepare db "SELECT * FROM users WHERE id = ?"))

sqlite-bindprocedure

Bind a value to a parameter in a prepared statement.

Parameter indices are 1-based. Accepts strings, numbers, bytevectors, booleans, and 'null. Returns #t on success.

(sqlite-bind stmt 1 "Alice")
(sqlite-bind stmt 2 42)

sqlite-stepprocedure

Step through a prepared statement.

Returns 'row if a result row is available (use sqlite-column to read values), 'done when finished, or #f on error.

(sqlite-step stmt)  ; => 'row, 'done, or #f

sqlite-resetprocedure

Reset a prepared statement to its initial state.

Allows re-execution with new parameter bindings.

(sqlite-reset stmt)

sqlite-finalizeprocedure

Finalize a prepared statement and release its resources.

Must be called when the statement is no longer needed.

(sqlite-finalize stmt)

sqlite-column-countprocedure

Get the number of columns in a result set.

(sqlite-column-count stmt)  ; => 3

sqlite-column-nameprocedure

Get the name of a result column by index.

Column indices are 0-based.

(sqlite-column-name stmt 0)  ; => "id"
(sqlite-column-name stmt 1)  ; => "name"

sqlite-columnprocedure

Get the value of a result column by index.

Column indices are 0-based. Returns the appropriate Scheme type based on the SQLite column type (integer, real, text, blob, or null).

(sqlite-column stmt 0)  ; => 1
(sqlite-column stmt 1)  ; => "Alice"

sqlite-last-insert-rowidprocedure

Get the rowid of the last inserted row.

(sqlite-run db "INSERT INTO users (name) VALUES (?)" "Bob")
(sqlite-last-insert-rowid db)  ; => 5

sqlite-changesprocedure

Get the number of rows changed by the last statement.

(sqlite-run db "UPDATE users SET name = ? WHERE id = ?" "Robert" 1)
(sqlite-changes db)  ; => 1

sqlite-errmsgprocedure

Get the last error message from the database.

(sqlite-errmsg db)  ; => "not an error"

sqlite-queryprocedure

Execute a SQL query with optional parameters and return all rows. Each row is returned as an alist mapping column names to values.

Examples:

(sqlite-query db "SELECT * FROM users")
(sqlite-query db "SELECT * FROM users WHERE id = ?" 42)
(sqlite-query db "SELECT * FROM users WHERE name = ? AND age > ?" "Alice" 21)

sqlite-query-rowprocedure

Execute a SQL query and return only the first row as an alist. Returns #f if no rows match.

Examples:

(sqlite-query-row db "SELECT * FROM users WHERE id = ?" 1)
; => ((id . 1) (name . "Alice") (email . "alice@example.com"))

sqlite-runprocedure

Execute a SQL statement with parameters (for INSERT, UPDATE, DELETE). Returns #t on success, #f on failure.

Examples:

(sqlite-run db "INSERT INTO users (name, email) VALUES (?, ?)"
            "Bob" "bob@example.com")
(sqlite-run db "UPDATE users SET name = ? WHERE id = ?" "Robert" 1)

call-with-databaseprocedure

Open a database, call proc with the handle, and ensure it's closed.

Examples:

(call-with-database "test.db"
  (lambda (db)
    (sqlite-exec db "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
    (sqlite-run db "INSERT INTO users (name) VALUES (?)" "Alice")
    (sqlite-query db "SELECT * FROM users")))