Queries
Two functions run sql. Both take the connection, the sql and a list of parameters, and an optional timeout_ms.
| Function | Gives |
|---|---|
Db.query(conn, sql, params, timeout_ms) | Result[List[Db.Row]], the rows of the statement; a statement without a result gives none |
Db.execute(conn, sql, params, timeout_ms) | Result[Int], the rows inserted, updated or deleted |
timeout_ms is the limit of the call in milliseconds (-1, the default, takes the connection’s timeout_ms; 0 has none). A statement that runs over it fails, and Db.timeout?(e.message) tells that failure from others. See Timeouts and the page of each database.
A Db.Row is a Map[String, Db.Value], in column order. Read a column with row["name"] and look at the Db.Value with case.
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"
def run() -> Result[Void]
conn = try Db.open("sqlite::memory:", [Sqlite.driver()])
try Db.execute(conn, "create table users (name text not null, age integer)", [])
changed = try Db.execute(conn, "insert into users (name, age) values (?, ?), (?, ?)",
[Db.Value.Text("Ana"), Db.Value.Int(31), Db.Value.Text("Bob"), Db.Value.Null])
puts(changed)
rows = try Db.query(conn, "select name, age from users order by name", [])
for row in rows
case row["name"]
Db.Value.Text(name) -> puts(name)
_ -> puts("no name")
end
end
Db.close(conn)
return ok()
end
def main() -> Void
case run()
ok(_) -> puts("done")
error(message) -> IO.warn(message)
end
end2
Ana
Bob
doneParameters #
A placeholder is ?, and only ?, on every database. The adapter turns it into what the database wants ($1 for PostgreSQL) and always binds the value: a value is never put into the sql text. Write the sql as a constant of your program, and pass what comes from outside as parameters.
The number of parameters must match the number of placeholders, or the call is a bad input. A ? inside a string, a quoted name or a comment is not a placeholder.
A list does not expand one placeholder. For in (...), build the ?, ?, ? from the length of the list, only the placeholders, and pass the values as parameters.
import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"
def main() -> Void
conn = Db.open("sqlite::memory:", [Sqlite.driver()]).unwrap!
Db.execute(conn, "create table users (id integer primary key, name text not null)", []).unwrap!
Db.execute(conn, "insert into users (name) values ('Ana'), ('Bob'), ('Cy'), ('Dee')", []).unwrap!
ids = [1, 3, 4]
holes = ids.map do |_id| "?" end
marks = holes.join(", ")
params = ids.map do |id| Db.Value.Int(id) end
rows = Db.query(conn, "select name from users where id in (#{marks}) order by id", params).unwrap!
puts(rows.size)
Db.close(conn)
end3One statement #
Only the first and only statement of the string runs. Two statements are an error, and so is a result with two columns of the same name: give them an alias.