1 Introduction
The following annotated program demonstrates how to connect to a database and perform simple queries. Some of the SQL syntax used below is PostgreSQL-specific, such as the syntax of query parameters.
> (require (planet ryanc/db:1:4))
First we create a connection. Replace user, db, and password below with the appropriate values for your configuration (see Creating connections for other connection examples):
> (define pgc (postgresql-connect #:user user #:database db #:password password)) > pgc (object:connection% ...)
Use query-exec method to execute a SQL statement for effect.
> (query-exec pgc "create temporary table the_numbers (n integer, d varchar(20))")
> (query-exec pgc "insert into the_numbers values (0, 'nothing')")
> (query-exec pgc "insert into the_numbers values (1, 'the loneliest number')")
> (query-exec pgc "insert into the_numbers values (2, 'company')")
The query function is a more general way to execute a statement. It returns a structure encapsulating information about the statement’s execution. (But some of that information varies from system to system and is subject to change.)
> (query pgc "insert into the_numbers values (3, 'a crowd')") (simple-result '((command insert 0 1)))
> (query pgc "select n, d from the_numbers where n % 2 = 0") (recordset '(((name . "n") (typeid . 23)) ((name . "d") (typeid . 1043))) '(#(0 "nothing") #(2 "company")))
When the query is known to return a recordset and when the field descriptions are not needed, it is more convenient to use the query-rows function.
> (query-rows pgc "select n, d from the_numbers where n % 2 = 0") '(#(0 "nothing") #(2 "company"))
Use query-row for queries that are known to return a recordset of exactly one row.
> (query-row pgc "select * from the_numbers where n = 0") '#(0 "nothing")
Similarly, use query-list for queries that produce a recordset of exactly one column.
> (query-list pgc "select d from the_numbers order by n") '("nothing" "the loneliest number" "company" "a crowd")
When a query is known to return a single value (one row and one column), use query-value.
> (query-value pgc "select count(*) from the_numbers") 4
> (query-value pgc "select d from the_numbers where n = 5") query-value: query returned zero rows: "select d from
the_numbers where n = 5"
When a query may return zero or one rows, as the last example, use query-maybe-row or query-maybe-value instead.
> (query-maybe-value pgc "select d from the_numbers where n = 5") #f
The in-query function produces a sequence that can be used with Racket’s iteration forms:
> (for ([(n d) (in-query pgc "select * from the_numbers where n < 4")]) (printf "~a is ~a\n" n d))
0: nothing
1: the loneliest number
2: company
3: a crowd
> (for/fold ([sum 0]) ([n (in-query pgc "select n from the_numbers")]) (+ sum n)) 6
Errors in queries generally do not cause the connection to disconnect.
> (begin (with-handlers [(exn:fail? (lambda (e) (printf "~a~n" (exn-message e))))] (query-value pgc "select NoSuchField from NoSuchTable")) (query-value pgc "select 'okay to proceed!'")) query-value: relation "nosuchtable" does not exist (SQLSTATE 42P01)
"okay to proceed!"
Queries may contain parameters. The easiest way to execute a parameterized query is to provide the parameters “inline” after the SQL statement in the query function call.
> (query-value pgc "select d from the_numbers where n = $1" 2) "company"
> (query-list pgc "select n from the_numbers where n > $1 and n < $2" 0 3) '(1 2)
Alternatively, a parameterized query may be prepared in advance and executed later. Prepared statements can be executed multiple times with different parameter values.
> (define get-less-than-pst (prepare pgc "select n from the_numbers where n < $1")) > (query-list pgc get-less-than-pst 1) '(0)
> (query-list pgc (bind-prepared-statement get-less-than-pst 2)) '(0 1)
A prepared statement is tied to the connection used to create it; attempting to use it with another connection results in an error. Unfortunately, in some scenarios such as web servlets, the lifetimes of connections are short or difficult to track, making prepared statements inconvenient. In such cases, a better tool is the virtual statement, which prepares statements on demand and caches them for future use with the same connection.
> (define get-less-than-pst (virtual-statement "select n from the_numbers where n < $1")) > (query-list pgc1 get-less-than-pst 1) ; prepares statement for pgc1 '(0)
> (query-list pgc2 get-less-than-pst 2) ; prepares statement for pgc2 '(0 1)
> (query-list pgc1 get-less-than-pst 3) ; uses existing prep. stmt. '(0 1 2)
When a connection’s work is done, it should be disconnected.
> (disconnect pgc)