3. SQL Interface

The ‘(gamma sql)’ module provides interface with MySQL and PostgreSQL database management systems.

Usage:

 
(use-modules ((gamma sql)))
Scheme procedure: sql-open-connection params

This function opens a connection to the SQL server and returns a connection object. This object is then used as argument to sql-query and sql-close-connection functions.

The params argument supplies the connection parameters. It is a list of conses, each of which is composed from a keyword and a value.

Keyword: #:iface

Defines the type of the SQL interface. Valid values are: ‘"mysql"’, to connect to a MySQL server, and ‘"pgsql"’, to connect to a Postgres server.

Keyword: #:host

Defines server host name. The value is a string, containing the host name or ASCII representation of the host IP address.

Keyword: #:port

Defines the port number server is listening on. The value is a decimal port number.

Keyword: #:socket

If the SQL server is listening on a socket, this keyword defines the UNIX pathname of the socket. This keyword cannot be used together with ‘#:host’ or ‘#:port’ keyword pairs.

Keyword: #:user

Sets the SQL user name.

Keyword: #:pass

Sets the SQL user password.

Keyword: #:db

Sets the database name.

Keyword: #:ssl-cert

Defines full pathname of the SSL certificate to use. If this keyword is present, the connection with the server will be encrypted using SSL.

Currently it is implemented only for MySQL connections.

Keyword: #:config-file

Use the specified MySQL configuration file to obtain missing parameters.

Keyword: #:config-group

Obtain missing parameters from the specified group in the MySQL configuration file (see ‘#:config-file’, above).

Scheme procedure: sql-close-connection conn

Close the SQL connection. The conn must be a connection descriptor returned from a previous call to sql-open-connection.

Scheme procedure: sql-query conn query

Conn is a connection descriptor returned from a previous call to sql-open-connection, and query is a valid SQL query. This function executes the query and returns its results.

If query is a SELECT query (or a similar query, returning tuples), the return is a list, each element of which is a list representing a row. Elements of each row (columns) are string values.

If query results in some modifications to the database (e.g. an UPDATE statement), the sql-query function returns the number of affected database rows.

Error Keyword: sql-error

An error of this type is raised when any of the above functions fails. Two arguments are supplied: a string describing the error, and error message from the underlying SQL implementation.

Scheme syntax: sql-catch-failure (handler) expr
Scheme syntax: sql-catch-failure expr

This syntax executes the Scheme expression expr and calls handler if a gsql-error exception occurs. In its second form, sql-catch-failure calls a function named sql-error-handler if a sql-error exception occurs. The sql-error-handler must be declared by the user.

The error handler must be declared as follows:

 
(define (handler key func fmt fmtargs data)
  ...)

where:

key

The error key (‘sql-error’).

func

Name of the Scheme function that encountered the error.

fmt

Format string suitable for format.

fmtargs

Arguments to fmt.

data

Interface-specific error description. It is a list consisting of two elements. The first element is an integer code of the error, if supported by the underlying implementation, or #f if not. The second element is a textual description of the error obtained from the underlying implementation.

For example:

 
(define (sql-error-handler key func fmt fmtargs data)
  (apply format (current-error-port) fmt fmtargs))
Scheme syntax: sql-ignore-failure (value) expr
Scheme syntax: sql-ignore-failure expr

Evaluates Scheme expression expr and returns the result of evaluation, or value if a gsql-error exception occurs.

In its second form, returns #f in case of error.