On this page:
3.1 Statements
statement?
3.2 Simple queries
query-exec
query-rows
query-list
query-row
query-maybe-row
query-value
query-maybe-value
in-query
3.3 General query support
simple-result
recordset
query
query-fold
3.4 Prepared statements
prepare
prepared-statement?
prepared-statement-parameter-types
prepared-statement-result-types
bind-prepared-statement
statement-binding?
statement-generator
statement-generator?

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.

Errors In most cases, a query error does not cause the connection to be disconnected. Specifically, the following kinds of errors should never cause a connection to be disconnected:
  • 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

The following kinds of errors may cause a connection to be disconnected:
  • 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—or shutting down the connection’s managing custodian—may leave the connection in a damaged state, causing future operations to return garbage, raise errors, or block indefinitely.

3.1 Statements

All queries require both a connection and a statement, which is one of the following:

(statement? x)  boolean?
  x : any/c
Returns #t if x is a statement, #f otherwise.

Equivalent to the following:
  (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
Executes a SQL statement for effect.

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
Executes a SQL query, which must produce a recordset, and returns the list of rows (as vectors) from the query.

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
Executes a SQL query, which must produce a recordset of exactly one column, and returns the list of values from the query.

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
Executes a SQL query, which must produce a recordset of exactly one row, and returns its (single) row result as a vector.

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
Like query-row, but the query may produce zero rows; in that case, #f is returned.

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
Executes a SQL query, which must produce a recordset of exactly one column and exactly one row, and returns its single value result.

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
Like query-value, but the query may produce zero rows; in that case, #f is returned.

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
Executes a SQL query, which must produce a recordset, and returns a sequence. Each step in the sequence produces as many values as the recordset has columns.

Examples:

  > (for/list ([n (in-query pgc "select n from the_numbers where n < 2")])
      n)

  (0 1)

  > (for ([(n d)
           (in-query pgc "select * from the_numbers where n < $1" 4)])
      (printf "~a is ~a\n" n d))

  0: nothing

  1: the loneliest number

  2: company

  3: a crowd

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:

  > (for ([n (in-query pgc "select * from the_numbers")])
      (displayln n))

  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
Represents the result of a SQL statement that does not return a relation, such as an INSERT or DELETE statement.

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).

(struct recordset (headers rows))
  headers : (listof any/c)
  rows : (listof vector?)
Represents the result of SQL statement that results in a relation, such as a SELECT query.

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
Executes a query, returning a structure that describes the results. Unlike the more specialized query functions, query supports both recordset-returning and effect-only queries.

(query-fold connection stmt fold-proc init)  alpha
  connection : connection?
  stmt : (or/c string? statement-binding?)
  fold-proc : (alpha field ... -> alpha)
  init : alpha
Left fold over the results of the query. The arity of fold-proc must include a number one greater than the number of columns returned by the query. Inline parameter arguments ae not supported; parameter binding must be done explicitly.

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?)
Prepares a (possibly parameterized) statement. The resulting prepared statements are tied to the connection that prepared them; it is an error to use them with any other connection. (The prepared statement holds its connection link weakly; a reference to a prepared statement will not keep a connection from being garbage collected.)

(prepared-statement? x)  boolean?
  x : any/c
Returns #t if x is a prepared statement created by prepare, #f otherwise.

Returns a list with one element for each of the prepared statement’s parameters. Each element is itself a list of the following form:

  (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.

If pst is a recordset-producing statement (eg, SELECT), returns a list of type descriptions as described above, identifying the SQL types (or pseudotypes) of the result columns. If pst does not produce a recordset, the function returns the empty list.

(bind-prepared-statement pst params)  statement-binding?
  pst : prepared-statement?
  params : (listof any/c)
Fills in the parameters of a parameterized prepared query. The resulting statement can be executed with query or any of the other query functions, but it must be used with the same connection that created it.

Example:

  > (let* ([get-name-pst
           (prepare pgc "select description from the_numbers where n = $1")]
           [get-name2
            (bind-prepared-statement get-name-pst (list 2))]
           [get-name3
            (bind-prepared-statement get-name-pst (list 3))])
      (list (query-value pgc get-name2)
            (query-value pgc get-name3)))

  ("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
Returns #t if x is a statement created by bind-prepared-statement, #f otherwise.

Creates a statement generator stmt, which encapsulates a weak hash mapping connections to prepared statement objects. When a query function is called with stmt and a connection, the weak hash is consulted to see if the statement has already been prepared for that connection. If so, the prepared statement is used; otherwise, the statement is prepared and stored in the table.

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:

  > (define pst
      (statement-generator
       (lambda (dbsys)
         (case (dbsystem-name dbsys)
           ((postgresql) "select n from the_numbers where n < $1")
           ((sqlite3) "select n from the_numbers where n < ?")
           (else (error "unknown system"))))))
  > (query-list pgc pst 3)

  (1 2)

  > (query-list slc pst 3)

  (1 2)

(statement-generator? x)  boolean?
  x : any/c
Returns #t if x is a statement generator created by statement-generator, #f otherwise.