1 Introduction

The following annotated program demonstrates how to connect to a database and perform simple queries. Some of the SQL syntax is PostgreSQL-specific, such as the syntax of query parameters.

> (require (planet ryanc/db:1:0))

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

#(struct:object:connection% ...)

Use query-exec method to execute a SQL statement for effect.

> (query-exec pgc      "create temporary table the_numbers (n integer, description varchar(80))")

> (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')")

#(struct:simple-result ((command insert 0 1)))

> (query pgc      "select n, description from the_numbers where n % 2 = 0")

#(struct:recordset (((name . "n") (typeid . 23)) ((name . "description") (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, description 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 description 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 containing one column), use query-value.

> (query-value pgc "select count(*) from the_numbers")

4

> (query-value pgc "select description from the_numbers where n = 5")

query-value: query returned zero rows: "select description

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 description from the_numbers where n = 5")

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 are usually non-fatal.

> (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 (SQL code 42P01)

"okay to proceed!"

Queries may contain parameters. The easiest way to execute a parameterize query is to provide the parameters “inline” after the SQL statement in the query function call.

> (query-value pgc      "select description 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 statement generator, which prepares statements on demand and caches them for future use with the same connection.

> (define get-less-than-pst       (statement-generator "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)