Statement[S @ {*SqlValue, **SqlValue}]
A compiled SQL statement that can be run repeatedly with different parameters.
Returned by Connection.prepare.
Parameter binding
A statement is prepared from a template, and a template says two different things about the values in it.
An interpolation -- $name or ${...} -- carries a value the program
already has. It is bound when the statement is prepared and stays bound for
the life of the statement.
A parameter -- ${#name} or ${#0} -- is a hole left for each call to
fill. A named parameter is filled by a keyword argument and a numbered one
by the positional argument in that place; a hole used twice is one
parameter, filled once.
let cutoff = "2023-01-01"
conn.prepare t"UPDATE users SET status = ${#status} WHERE created < $cutoff"
do |stmt|
stmt.execute status: "archived"
Filling is exhaustive: every parameter must be supplied on every call, and an argument naming no parameter raises. To fix some values ahead of time, build the template in a function that interpolates them.
Neither form ever becomes SQL text -- only the template's literal text does -- so an interpolated value cannot alter the statement it appears in, whatever it contains.
Rejected at prepare
| Condition | Why |
|---|---|
The SQL is a Str |
A Str cannot say which of its text is data |
A :name or ? in the template's own text |
The binder cannot see it, so it would step as NULL |
A quoted interpolation, as in '$name' |
Quoting buries the value in a literal, binding none |
A specification, as in ${#0:>10} |
A bound value is never rendered, so it takes none |
The quoting case is worth naming, because it is the habit a plain string
teaches: t"... WHERE name = '$name'" needs no quotes, since $name is
bound rather than pasted. Written with them, it raises rather than doing the
wrong thing quietly.
Accepted value types
| Do type | SQLite type | Example |
|---|---|---|
nil |
NULL | stmt.execute value: nil |
Bool |
INTEGER | stmt.execute active: true |
Int |
INTEGER | stmt.execute id: 42 |
Float |
REAL | stmt.execute price: 19.99 |
Str |
TEXT | stmt.execute name: "Alice" |
Bin |
BLOB | stmt.execute data: b"\x01\x02\x03" |
A Bool is stored as 0 or 1. The same types are accepted for an
interpolated value.
Concurrent use
Only one query can be active on a statement at a time. Starting another
query, or executing the statement, invalidates the iterator the previous
query returned, which then raises
ConcurrencyError on use.
open "mydb.sqlite" do |conn|
conn.prepare t"SELECT * FROM users" do |stmt|
let rows = stmt.query()
let rows2 = stmt.query()
# rows has been invalidated at this point
Methods
close()
Closes the statement and releases its resources.
execute ...args -> Int
Runs the statement and returns the number of rows it affected.
Outside a transaction, busy errors are retried according to the connection's retry settings.
Parameters
| Name | Type | Description |
|---|---|---|
...args |
S |
Values filling the statement's parameters. See Parameter binding. |
Returns
The number of rows affected.
Errors
| Exception | Condition |
|---|---|
MissingPosError |
A numbered parameter unfilled |
MissingKeyError |
A named parameter unfilled |
UnexpectedPosError |
A positional argument unused |
UnexpectedKeyError |
A keyword argument unused |
Example
conn.prepare t"UPDATE users SET status = ${#status} WHERE created < ${#date}"
do |stmt|
let affected = stmt.execute status: "archived" date: "2023-01-01"
echo "Archived $affected users"
query ...args -> Iter[Row]
Runs the statement and iterates its result rows.
Outside a transaction, a busy error while fetching the next row is retried according to the connection's retry settings.
Parameters
| Name | Type | Description |
|---|---|---|
...args |
S |
Values filling the statement's parameters. See Parameter binding. |
Returns
An Iter yielding Rows. Only the most
recently yielded row stays valid -- see Row
validity.
Errors
The same as execute.