3 Queries
This library provides 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, such as references to undefined tables, columns, or operations, etc
SQL runtime errors, such as integrity constraint violations
violations of a specialized query function’s expectations, such as using query-value with a query that returns 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
a break occurring during a connection operation
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 (eg, SET NAMES). 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 encoding. When possible, the connection will observe the change and automatically disconnect with an error.
Synchronization Connections are internally synchronized: it is safe to use a connection from different threads concurrently. Most connections are not kill-safe: killing a thread that is using a connection may leave the connection locked, causing future operations to block indefinitely. See also Kill-safe Connections.
3.1 Statements
a string containing a single SQL statement
a prepared statement produced by prepare
a virtual statement produced by virtual-statement
a statement-binding value produced by bind-prepared-statement
an instance of a struct type that implements prop:statement
A SQL statement may contain parameter placeholders that stand for SQL scalar values; such statements are called parameterized queries. The parameter values must be supplied when the statement is executed; the parameterized statement and parameter values are sent to the database back end, which combines them correctly and safely.
Use parameters instead of Racket string operations (eg, format or string-append) to avoid SQL Injection.
The syntax of placeholders varies depending on the database system. For example:
PostgreSQL:
select * from the_numbers where n > $1;
MySQL, ODBC:
select * from the_numbers where n > ?;
SQLite:
supports both syntaxes (plus others)
procedure
(statement? x) → boolean?
x : any/c
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 exactly one row of exactly one column.
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 virtual statement can be given “inline” parameters; if the statement is a statement-binding, no inline parameters are permitted.
The types of parameters and returned fields are described in SQL Types and Conversions.
procedure
(query-exec connection stmt arg ...) → void?
connection : connection? stmt : statement? arg : any/c
> (query-exec pgc "insert into the_numbers values (42, 'the answer')")
> (query-exec pgc "delete from the_numbers where n = $1" 42)
procedure
(query-rows connection stmt arg ... [ #:group groupings #:group-mode group-mode]) → (listof vector?) connection : connection? stmt : statement? arg : any/c
groupings :
(let* ([field/c (or/c string? exact-nonnegative-integer?)] [grouping/c (or/c field/c (vectorof field/c))]) (or/c grouping/c (listof grouping/c))) = null group-mode : (listof (or/c 'preserve-null 'list)) = null
> (query-rows pgc "select * from the_numbers where n = $1" 2) '(#(2 "company"))
> (query-rows c "select 17") '(#(17))
If groupings is not empty, the result is the same as if group-rows had been called on the result rows.
procedure
(query-list connection stmt arg ...) → list?
connection : connection? stmt : statement? arg : any/c
> (query-list c "select n from the_numbers where n < 2") '(0 1)
> (query-list c "select 'hello'") '("hello")
procedure
connection : connection? stmt : statement? arg : any/c
> (query-row pgc "select * from the_numbers where n = $1" 2) '#(2 "company")
> (query-row pgc "select min(n), max(n) from the_numbers") '#(0 3)
procedure
(query-maybe-row connection stmt arg ...) → (or/c vector? #f)
connection : connection? stmt : statement? arg : any/c
> (query-maybe-row pgc "select * from the_numbers where n = $1" 100) #f
> (query-maybe-row c "select 17") '#(17)
procedure
(query-value connection stmt arg ...) → any/c
connection : connection? stmt : statement? arg : any/c
> (query-value pgc "select timestamp 'epoch'") (sql-timestamp 1970 1 1 0 0 0 0 #f)
> (query-value pgc "select d from the_numbers where n = $1" 3) "a crowd"
procedure
(query-maybe-value connection stmt arg ...) → (or/c any/c #f)
connection : connection? stmt : statement? arg : any/c
> (query-maybe-value pgc "select d from the_numbers where n = $1" 100) #f
> (query-maybe-value c "select count(*) from the_numbers") 4
procedure
(in-query connection stmt arg ... [ #:fetch fetch-size #:group groupings #:group-mode group-mode]) → sequence? connection : connection? stmt : statement? arg : any/c fetch-size : (or/c exact-positive-integer? +inf.0) = +inf.0
groupings :
(let* ([field/c (or/c string? exact-nonnegative-integer?)] [grouping/c (or/c field/c (vectorof field/c))]) (or/c grouping/c (listof grouping/c))) = null group-mode : (listof (or/c 'preserve-null 'list)) = null
If fetch-size is +inf.0, all rows are fetched when the sequence is created. If fetch-size is finite, a cursor is created and fetch-size rows are fetched at a time, allowing processing to be interleaved with retrieval. On some database systems, ending a transaction implicitly closes all open cursors; attempting to fetch more rows may fail. On PostgreSQL, a cursor can be opened only within a transaction.
If groupings is not empty, the result is the same as if group-rows had been called on the result rows. If groupings is not empty, then fetch-size must be +inf.0; otherwise, an exception is raised.
> (for/list ([n (in-query pgc "select n from the_numbers where n < 2")]) n) '(0 1)
> (call-with-transaction pgc (lambda () (for ([(n d) (in-query pgc "select * from the_numbers where n < $1" 4 #:fetch 1)]) (printf "~a: ~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:
> (for ([n (in-query pgc "select * from the_numbers")]) (displayln n)) in-query: query returned wrong number of columns
statement: "select * from the_numbers"
expected: 1
got: 2
3.3 General Query Support
A general query result is either a simple-result or a rows-result.
The info field is an association list, but its contents vary based on database system and may change in future versions of this library (even new minor versions). The following keys are supported for multiple database systems:
'insert-id: If the value is a positive integer, the statement was an INSERT statement and the value is a system-specific identifier for the inserted row. For PostgreSQL, the value is the row’s OID, if the table has OIDs (for an alternative, see the INSERT ... RETURNING statement). For MySQL, the value is the same as the result of last_insert_id function—
that is, the value of the row’s AUTO_INCREMENT field. If there is no such field, the value is #f. For SQLite, the value is the same as the result of the last_insert_rowid function— that is, the ROWID of the inserted row. 'affected-rows: The number (a nonnegative integer) of rows inserted by an INSERT statement, modified by an UPDATE statement, or deleted by a DELETE statement. Only directly affected rows are counted; rows affected because of triggers or integrity constraint actions are not counted.
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).
procedure
(query connection stmt arg ...)
→ (or/c simple-result? rows-result?) connection : connection? stmt : statement? arg : any/c
procedure
(group-rows result #:group groupings [ #:group-mode group-mode]) → rows-result? result : rows-result?
groupings :
(let* ([field/c (or/c string? exact-nonnegative-integer?)] [grouping/c (or/c field/c (vectorof field/c))]) (or/c grouping/c (listof grouping/c))) group-mode : (listof (or/c 'preserve-null 'list)) = null
See also The N+1 Selects Problem.
> (define vehicles-result (rows-result '(((name . "type")) ((name . "maker")) ((name . "model"))) `(#("car" "honda" "civic") #("car" "ford" "focus") #("car" "ford" "pinto") #("bike" "giant" "boulder") #("bike" "schwinn" ,sql-null))))
> (group-rows vehicles-result #:group '(#("type")))
(rows-result
'(((name . "type"))
((name . "grouped") (grouped ((name . "maker")) ((name . "model")))))
'(#("car" (#("honda" "civic") #("ford" "focus") #("ford" "pinto")))
#("bike" (#("giant" "boulder") #("schwinn" #<sql-null>)))))
The grouped final column is given the name "grouped".
The groupings argument may also be a list of vectors; in that case, the grouping process is repeated for each set of grouping fields. The grouping fields must be distinct.
> (group-rows vehicles-result #:group '(#("type") #("maker")) #:group-mode '(list))
(rows-result
'(((name . "type"))
((name . "grouped")
(grouped
((name . "maker"))
((name . "grouped") (grouped ((name . "model")))))))
'(#("car" (#("honda" ("civic")) #("ford" ("focus" "pinto"))))
#("bike" (#("giant" ("boulder")) #("schwinn" ())))))
procedure
(rows->dict result #:key key-field/s #:value value-field/s [ #:value-mode value-mode]) → dict? result : rows-result?
key-field/s :
(let ([field/c (or/c string? exact-nonnegative-integer?)]) (or/c field/c (vectorof field/c)))
value-field/s :
(let ([field/c (or/c string? exact-nonnegative-integer?)]) (or/c field/c (vectorof field/c))) value-mode : (listof (or/c 'list 'preserve-null)) = null
If value-mode contains 'list, a list of values is accumulated for each key; otherwise, there must be at most one value for each key. Values consisting of all sql-null? values are dropped unless value-mode contains 'preserve-null.
> (rows->dict vehicles-result #:key "model" #:value '#("type" "maker"))
'#hash(("pinto" . #("car" "ford"))
("focus" . #("car" "ford"))
(#<sql-null> . #("bike" "schwinn"))
("civic" . #("car" "honda"))
("boulder" . #("bike" "giant")))
> (rows->dict vehicles-result #:key "maker" #:value "model" #:value-mode '(list))
'#hash(("honda" . ("civic"))
("ford" . ("focus" "pinto"))
("schwinn" . ())
("giant" . ("boulder")))
3.4 Prepared Statements
A prepared statement is the result of a call to prepare.
Any server-side or native-library resources associated with a prepared statement are released when the prepared statement is garbage-collected or when the connection that owns it is closed; prepared statements do not need to be (and cannot be) explicitly closed.
procedure
(prepare connection stmt) → prepared-statement?
connection : connection? stmt : (or/c string? virtual-statement?)
procedure
(prepared-statement? x) → boolean?
x : any/c
(list supported? type typeid)
The supported? field indicates whether the type is supported by this library; the type field is a symbol corresponding to an entry in one of the tables in SQL Type Conversions; 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.
procedure
(bind-prepared-statement pst params) → statement-binding?
pst : prepared-statement? params : (listof any/c)
> (let* ([get-name-pst (prepare pgc "select d 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.
procedure
(statement-binding? x) → boolean?
x : any/c
procedure
(virtual-statement gen) → virtual-statement?
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.
> (define pst (virtual-statement (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) '(0 1 2)
> (query-list slc pst 3) '(0 1 2)
procedure
(virtual-statement? x) → boolean?
x : any/c
3.5 Transactions
The functions described in this section provide a consistent interface to transactions.
A managed transaction is one created via either start-transaction or call-with-transaction. In contrast, an unmanaged transaction is one created by evaluating a SQL statement such as START TRANSACTION. A nested transaction is a transaction created within the extent of an existing transaction. If a nested transaction is committed, its changes are promoted to the enclosing transaction, which may itself be committed or rolled back. If a nested transaction is rolled back, its changes are discarded, but the enclosing transaction remains open. Nested transactions are implemented via SQL SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT.
ODBC connections must use managed transactions exclusively; using transaction-changing SQL may cause these functions to behave incorrectly and may cause additional problems in the ODBC driver. ODBC connections do not support nested transactions.
PostgreSQL, MySQL, and SQLite connections must not mix managed and unmanaged transactions. For example, calling start-transaction and then executing a ROLLBACK statement is not allowed. Note that in MySQL, some SQL statements have implicit transaction effects. For example, in MySQL a CREATE TABLE statement implicitly commits the current transaction. These statements also must not be used within managed transactions. (In contrast, PostgreSQL and SQLite both support transactional DDL.)
All errors raised by checks performed by this library, such as parameter arity and type errors, leave the transaction open and unchanged (1).
All errors originating from PostgreSQL cause the transaction to become invalid (3).
Most errors originating from MySQL leave the transaction open and unchanged (1), but a few cause the transaction to become invalid (3). In the latter cases, the underlying behavior of MySQL is to roll back the transaction but leave it open (see the MySQL documentation). This library detects those cases and marks the transaction invalid instead.
Most errors originating from SQLite leave the transaction open and unchanged (1), but a few cause the transaction to become invalid (3). In the latter cases, the underlying behavior of SQLite is to roll back the transaction (see the SQLite documentation). This library detects those cases and marks the transaction invalid instead.
All errors originating from an ODBC driver cause the transaction to become invalid (3). The underlying behavior of ODBC drivers varies widely, and ODBC provides no mechanism to detect when an existing transaction has been rolled back, so this library intercepts all errors and marks the transaction invalid instead.
If a transaction is open when a connection is disconnected, it is implicitly rolled back.
procedure
(start-transaction c [ #:isolation isolation-level #:option option]) → void? c : connection?
isolation-level :
(or/c 'serializable 'repeatable-read 'read-committed 'read-uncommitted #f) = #f option : any/c = #f
PostgreSQL supports 'read-only and 'read-write for the corresponding transaction options.
SQLite supports 'deferred, 'immediate, and 'exclusive for the corresponding locking modes.
MySQL and ODBC no not support any options.
If c is already in a transaction, isolation-level and option must both be #f, and a nested transaction is opened.
See also Transactions and Concurrency.
procedure
(commit-transaction c) → void?
c : connection?
If the current transaction is a nested transaction, the nested transaction is closed, its changes are incorporated into the enclosing transaction, and the enclosing transaction is resumed.
If no transaction is open, this function has no effect.
procedure
(rollback-transaction c) → void?
c : connection?
If the current transaction is a nested transaction, the nested transaction is closed, its changes are abandoned, and the enclosing transaction is resumed.
If no transaction is open, this function has no effect.
procedure
(in-transaction? c) → boolean?
c : connection?
procedure
(needs-rollback? c) → boolean?
c : connection?
procedure
(call-with-transaction c proc [ #:isolation isolation-level #:option option]) → any c : connection? proc : (-> any)
isolation-level :
(or/c 'serializable 'repeatable-read 'read-committed 'read-uncommitted #f) = #f option : any/c = #f
Calling either commit-transaction or rollback-transaction when the open transaction was created by call-with-transaction causes an exception to be raised.
If a further nested transaction is open when proc completes (that is, created by an unmatched start-transaction call), an exception is raised and the nested transaction created by call-with-transaction is rolled back.
3.6 SQL Errors
SQL errors are represented by the exn:fail:sql exception type.
struct
(struct exn:fail:sql exn:fail (sqlstate info) #:extra-constructor-name make-exn:fail:sql) sqlstate : (or/c string? symbol?) info : (listof (cons/c symbol? any/c))
SQLite error codes; errors are represented as a symbol based on the error constant’s name, such as 'busy for SQLITE_BUSY; three code are provided in extended form: 'ioerr-blocked, 'ioerr-locked, and 'readonly-rollback.
ODBC: see the database system’s documentation
The info field contains all information available about the error as an association list. The available keys vary, but the 'message key is typically present; its value is a string containing the error message.
> (with-handlers ([exn:fail:sql? exn:fail:sql-info]) (query pgc "select * from nosuchtable"))
'((severity . "ERROR")
(code . "42P01")
(message . "relation \"nosuchtable\" does not exist")
(position . "15")
(file . "parse_relation.c")
(line . "857")
(routine . "parserOpenTable"))
Errors originating from the db library, such as arity and contract errors, type conversion errors, etc, are not represented by exn:fail:sql.
3.7 Database Catalog Information
procedure
(list-tables c [#:schema schema]) → (listof string?)
c : connection?
schema : (or/c 'search-or-current 'search 'current) = 'search-or-current
If schema is 'search, the list contains all tables in the current schema search path (with the possible exception of system tables); if the search path cannot be determined, an exception is raised. If schema is 'current, the list contains all tables in the current schema. If schema is 'search-or-current (the default), the search path is used if it can be determined; otherwise the current schema is used. The schema search path cannot be determined for ODBC-based connections.
procedure
(table-exists? c table-name [ #:schema schema #:case-sensitive? case-sensitive?]) → boolean? c : connection? table-name : string?
schema : (or/c 'search-or-current 'search 'current) = 'search-or-current case-sensitive? : any/c = #f
3.8 Creating New Kinds of Statements
value
prop:statement :
(struct-type-property/c (-> any/c connection? statement?))