Next: Procedures for managing connections, Previous: Legalities, Up: The (database postgres*) Modules [Contents][Index]
To access a PostgreSQL database from Guile, first load the postgres
database module. This is done using the use-modules
syntax:
(use-modules (database postgres))
The next step is to make a connection to the database using the
pg-connectdb
procedure. Then, having created a valid connection to a
database server, we can use the pg-exec
procedure to execute
queries and the other pg-
procedures to retrieve the results of the
queries.
The following Guile session opens a connection and executes an
SQL statement on the test
database on the default host (either
localhost
or the host specified in the PGHOST
environment
variable.)
guile> (use-modules (database postgres)) guile> (define test (pg-connectdb "dbname=test")) guile> test #<PG-CONN:test::5432:> guile> (define result (pg-exec test "SELECT * FROM PEOPLE")) guile> result #<PG-RESULT:TUPLES_OK:3:5> |
The test
object is a PG-CONN
type representing the database
connection. Displaying the PG-CONN
object shows that the serial number
of the connection is 1, that the database name is test
on the default
host (the hostname field is empty) on port number 5432 and that the default
options were passed to the back-end. This object is passed to pg-exec
which returns a PG-RESULT
object representing the result of the SQL
statement executed on the server. In this case, displaying the PG-RESULT
object shows us that the serial number of the result is 2, that the return code
from the database was PGRES_TUPLES_OK
and that we have received 3 tuples
with 5 fields.
Having obtained a result object from the database we can find out how many rows and how many columns are in the result. We can also find out the names of the columns:
guile> (pg-ntuples result) 3 guile> (pg-nfields result) 5 guile> (pg-fname result 0) "surname" guile> (pg-fname result 1) "firstname" |
To retrieve the data from the result we use the pg-getvalue
procedure.
This takes a result object along with the row and column numbers and returns a
string containing that value. pg-getvalue
always returns a string: it
is up to the caller to convert this to whatever Scheme object they require.
guile> (pg-getvalue result 0 0) "Bitdiddle" guile> (pg-getvalue result 0 1) "Ben" guile> (pg-getvalue result 1 0) "Ator" guile> (pg-getvalue result 1 1) "Eva Lu" |
Guile-PG connections are closed when Guile’s garbage collector
collects the discarded object. This only happens when the connection itself
and all of the results of pg-exec
are unreachable. So to close the
connection we just rebind the results from that connection and the connection
symbol itself:
guile> (define result '()) guile> (define test '()) |
Typically though the result and connection variables will be let
bindings so the connection will usually be closed soon after the body of the
let
returns.
Next: Procedures for managing connections, Previous: Legalities, Up: The (database postgres*) Modules [Contents][Index]