@c This file is part of the Gamma manual.
@c Copyright (C) 2010 Sergey Poznyakoff
@c See gamma.texi for copying conditions.
@c *******************************************************************
@cindex SQL
@cindex MySQL
@cindex PostgreSQL
The @samp{(gamma sql)} module provides interface with MySQL and
PostgreSQL database management systems. 

Usage:

@lisp
(use-modules ((gamma sql)))
@end lisp

@deffn {Scheme procedure} sql-open-connection params
@cindex connection to @acronym{SQL}, opening
This function opens a connection to the @acronym{SQL}
server and returns a connection object. This object is
then used as argument to @code{sql-query} and
@code{sql-close-connection} functions.

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

@defvr {Keyword} #:iface
Defines the type of the @acronym{SQL} interface.  Valid values are:
@samp{"mysql"}, to connect to a MySQL server, and @samp{"pgsql"}, to
connect to a Postgres server.
@end defvr

@defvr {Keyword} #:host
Defines server host name.  The value is a string, containing the host
name or @acronym{ASCII} representation of the host IP address.
@end defvr

@defvr {Keyword} #:port
Defines the port number server is listening on.  The value is a
decimal port number.
@end defvr

@defvr {Keyword} #:socket
If the @acronym{SQL} server is listening on a socket, this keyword
defines the @acronym{UNIX} pathname of the socket.  This keyword
cannot be used together with @samp{#:host} or @samp{#:port} keyword
pairs.
@end defvr

@defvr {Keyword} #:user
Sets the @acronym{SQL} user name.
@end defvr

@defvr {Keyword} #:pass
Sets the @acronym{SQL} user password.
@end defvr

@defvr {Keyword} #:db
Sets the database name.
@end defvr

@defvr {Keyword} #:ssl-cert
@cindex @acronym{SSL}, using with @acronym{SQL}
Defines full pathname of the @acronym{SSL} certificate to use.  If
this keyword is present, the connection with the server will be
encrypted using @acronym{SSL}.

Currently it is implemented only for MySQL connections.
@end defvr

@defvr {Keyword} #:config-file
@cindex config file, MySQL
@cindex option file, MySQL
Use the specified MySQL configuration file to obtain missing parameters.
@end defvr

@defvr {Keyword} #:config-group
Obtain missing parameters from the specified group in the MySQL
configuration file (see @samp{#:config-file}, above).
@end defvr
@end deffn

@deffn {Scheme procedure} sql-close-connection conn
@cindex connection to @acronym{SQL}, closing
Close the @acronym{SQL} connection.  The @var{conn} must be a
connection descriptor returned from a previous call to
@code{sql-open-connection}.
@end deffn

@deffn {Scheme procedure} sql-query conn query
@cindex query, @acronym{SQL}
@var{Conn} is a connection descriptor returned from a previous call to
@code{sql-open-connection}, and @var{query} is a valid @acronym{SQL}
query.  This function executes the query and returns its results.

If @var{query} is a @code{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 (@dfn{columns}) are string
values.

If @var{query} results in some modifications to the database (e.g. an
@code{UPDATE} statement), the @code{sql-query} function returns
the number of affected database rows.
@end deffn

@defvr {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 @acronym{SQL} implementation.
@end defvr

@deffn {Scheme syntax} sql-catch-failure (handler) expr
@deffnx {Scheme syntax} sql-catch-failure expr
This syntax executes the Scheme expression @var{expr} and calls
@code{handler} if a @code{gsql-error} exception occurs.  
In its second form, @code{sql-catch-failure} calls a function named
@code{sql-error-handler} if a @code{sql-error} exception occurs.
The @code{sql-error-handler} must be declared by the user.

The error handler must be declared as follows:

@lisp
(define (handler key func fmt fmtargs data)
  ...)
@end lisp

@noindent
where:

@table @var
@item key
The error key (@samp{sql-error}).

@item func
Name of the Scheme function that encountered the error.

@item fmt
Format string suitable for @code{format}.

@item fmtargs
Arguments to @var{fmt}.

@item 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 @code{#f} if not.
The second element is a textual description of the error obtained from
the underlying implementation.
@end table

For example:

@lisp
@group
(define (sql-error-handler key func fmt fmtargs data)
  (apply format (current-error-port) fmt fmtargs))
@end group
@end lisp

@end deffn

@deffn {Scheme syntax} sql-ignore-failure (value) expr
@deffnx {Scheme syntax} sql-ignore-failure expr
Evaluates Scheme expression @var{expr} and returns the result of
evaluation, or @var{value} if a @code{gsql-error} exception occurs.

In its second form, returns @code{#f} in case of error.
@end deffn