3 Queries
The database package implements a high-level functional query API, unlike many other database libraries, which present a stateful, iteration-based interface to queries. When a query function is invoked, it either returns a result or, if the query caused an error, raises an exception. Different query functions impose different constraints on the query results and offer different mechanisms for processing the results.
SQL syntax errors, including references to undefined tables, columns, or operations, etc
violations of a specialized query function’s expectations, such as query-value getting a recordset with multiple columns
supplying the wrong number or wrong types of parameters to a prepared query, executing a prepared query with the wrong connection, etc
changing communication settings, such as changing the connection’s character encoding
communication failures and internal errors in the library
Character encoding This library is designed to interact with database systems using the UTF-8 character encoding. The connection functions attempt to negotiate UTF-8 communication at the beginning of every connection, but some systems also allow the character encoding to be changed via SQL commands. If this happens, the client might be unable to reliably communicate with the database, and data might get corrupted in transmission. Avoid changing a connection’s character set encoding.
Synchronization Connections are internally synchronized: it is
safe to perform concurrent queries on the same connection object from
different threads. Connections are not kill-safe: killing a
thread that is using a connection—
3.1 Statements
a string containing a single SQL statement, possibly with parameters
a prepared statement produced by prepare
a statement generator produced by statement-generator
a statement-binding value produced by bind-prepared-statement
(statement? x) → boolean? |
x : any/c |
(or (string? x) |
(prepared-statement? x) |
(statement-generator? x) |
(statement-binding? x)) |
3.2 Simple queries
The simple query API consists of a set of functions specialized to various types of queries. For example, query-value is specialized to queries that return a recordset of exactly one column and exactly one row.
If a statement takes parameters, the parameter values are given as additional arguments immediately after the SQL statement. Only a statement given as a string, prepared statement, or statement generator can be given “inline” parameters; if the statement is a statement-binding, no inline parameters are permitted.
(query-exec connection stmt arg ...) → void? |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: | |
> (query-exec c "insert into some_table values (1, 'a')") | |
> (query-exec pgc "delete from some_table where n = $1" 42) | |
(query-rows connection stmt arg ...) → (listof (vectorof field)) |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-rows pgc "select * from the_numbers where n = $1" 2) |
(#(2 "company")) |
> (query-rows c "select 17") |
(#(17)) |
(query-list connection stmt arg ...) → (listof field) |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-list c "select n from the_numbers where n < 2") |
(0 1) |
> (query-list c "select 'hello'") |
("hello") |
(query-row connection stmt arg ...) → (vectorof field) |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-row myc "select * from the_numbers where n = ?" 2) |
#(2 "company") |
> (query-row c "select 17") |
#(17) |
(query-maybe-row connection stmt arg ...) |
→ (or/c (vectorof field) false/c) |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-maybe-row pgc "select * from the_numbers where n = $1" 100) |
> (query-maybe-row c "select 17") |
#(17) |
(query-value connection stmt arg ...) → field |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-value pgc "select timestamp 'epoch'") |
#(struct:sql-timestamp 1970 1 1 0 0 0 0 #f) |
> (query-value pgc "select s from the_numbers where n = $1" 3) |
"a crowd" |
(query-maybe-value connection stmt arg ...) → (or/c field false/c) |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: |
> (query-value myc "select s from some_table where n = ?" 100) |
> (query-value c "select 17") |
17 |
(in-query connection stmt arg ...) → sequence? |
connection : connection? |
stmt : statement? |
arg : any/c |
Examples: | ||||
| ||||
(0 1) | ||||
| ||||
| ||||
An in-query application can provide better performance when it appears directly in a for clause. In addition, it may perform stricter checks on the number of columns returned by the query based on the number of variables in the clause’s left-hand side:
Example: | ||
| ||
in-query: query returned 2 columns (expected 1): "select * | ||
from the_numbers" |
3.3 General query support
A general query result is either a simple-result or a recordset.
(struct simple-result (info)) |
info : any/c |
The info field is usually an association list, but do not rely on its contents; it varies based on database system and may change in future versions of this library (even new minor versions).
The headers field is a list whose length is the number of columns in the result rows. Each header is usually an association list containing information about the column, but do not rely on its contents; it varies based on the database system and may change in future version of this library (even new minor versions).
(query connection stmt arg ...) → (or/c simple-result? recordset?) |
connection : connection? |
stmt : statement? |
arg : any/c |
(query-fold connection stmt fold-proc init) → alpha |
connection : connection? |
stmt : (or/c string? statement-binding?) |
fold-proc : (alpha field ... -> alpha) |
init : alpha |
3.4 Prepared statements
This package also includes functions for preparing parameterized queries. A parameterized query may be executed any number of times with different values for its parameters.
A prepared statement is the result of a call to prepare.
The syntax of parameterized queries varies depending on the database system. For example:
PostgreSQL:
select * from the_numbers where num > $1;
MySQL, ODBC:
select * from the_numbers where num > ?;
SQLite:
supports both syntaxes (plus others)
(prepare connection stmt) → prepared-statement? |
connection : connection? |
stmt : (or/c string? statement-generator?) |
(prepared-statement? x) → boolean? |
x : any/c |
(prepared-statement-parameter-types pst) |
→ (listof (list/c boolean? (or/c symbol? #f) any/c)) |
pst : prepared-statement? |
(list supported? type typeid)
The supported? field is #t if the type is supported by this library; the type field is a symbol corresponding to one of the tables in Type correspondences, and the typeid field is a system-specific type identifier. The type description list format may be extended with additional information in future versions of this library.
(prepared-statement-result-types pst) |
→ (listof (list/c boolean? (or/c symbol? #f) any/c)) |
pst : prepared-statement? |
(bind-prepared-statement pst params) → statement-binding? |
pst : prepared-statement? |
params : (listof any/c) |
Example: | ||||||||
| ||||||||
("company" "a crowd") |
Most query functions perform the binding step implicitly, but there are functions such as query-fold that do not accept query parameters; bind-prepared-statement is neccessary to use a parameterized query with such functions.
(statement-binding? x) → boolean? |
x : any/c |
(statement-generator gen) → statement-generator? |
gen : (or/c string? (-> dbsystem? string?)) |
The gen argument must be either a SQL string or a function that accepts a databse system object and produces a SQL string. The function variant allows the SQL syntax to be dynamically customized for the database system in use.
Examples: | |||||||
| |||||||
> (query-list pgc pst 3) | |||||||
(1 2) | |||||||
> (query-list slc pst 3) | |||||||
(1 2) |
(statement-generator? x) → boolean? |
x : any/c |