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):
Use query-exec method to execute a SQL statement for effect.
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.)
#(struct:simple-result ((command insert 0 1)))
#(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.
(#(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")|
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")|
|> (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"
The in-query function produces a sequence that can be used with Racket’s iteration forms:
Errors in queries are usually non-fatal.
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.
Alternatively, a parameterized query may be prepared in advance and executed later. Prepared statements can be executed multiple times with different parameter values.
|> (query-list pgc get-less-than-pst 1)|
|> (query-list pgc (bind-prepared-statement get-less-than-pst 2))|
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.
|> (query-list pgc1 get-less-than-pst 1) ; prepares statement for pgc1|
|> (query-list pgc2 get-less-than-pst 2) ; prepares statement for pgc2|
|> (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)|