(use dbi)
(use gauche.collection) ; to make 'map' work on the query result

(guard (e ((<dbi-error> e)
           ;; handle error
           ))
  (let* ((conn   (dbi-connect "dbi:mysql:test;host=dbhost"))
         (query  (dbi-prepare conn
                   "SELECT id, name FROM users WHERE department = ?"))
         (result (dbi-execute query "R&D"))
         (getter (relation-accessor result)))
    (map (lambda (row)
           (list (getter row "id")
                 (getter row "name")))
         result)))

12.27.1 DBI user API

DBI Conditions

There are several predefined conditions dbi API may throw. See Exceptions for the details of conditions.

Condition Type: <dbi-error> ¶

{dbi} The base class of dbi-related conditions. Inherits <error>.

Condition Type: <dbi-nonexistent-driver-error> ¶

{dbi} This condition is thrown by dbi-connect when it cannot find the specified driver. Inherits <dbi-error>.

Instance Variable of <dbi-nonexistent-driver-error>: driver-name ¶

Holds the requested driver name as a string.

Condition Type: <dbi-unsupported-error> ¶

{dbi} This condition is thrown when the called method isn’t supported by the underlying driver. Inherits <dbi-error>.

Condition Type: <dbi-parameter-error> ¶

{dbi} This condition is thrown when the number of parameters given to the prepared query doesn’t match the ones in the prepared statement.

Besides these errors, if a driver relies on dbi to parse the prepared SQL statement, <sql-parse-error> may be thrown if an invalid SQL statement is passed to dbi-prepare. (see text.sql - SQL parsing and construction).

Connecting to the database

Function: dbi-connect dsn :key username password ¶

{dbi} Connect to a database using a data source specified by dsn (data source name). Dsn is a string with the following syntax:

dbi:driver:options

Driver part names a specific driver. You need to have the corresponding driver module, dbd.driver, installed in your system. For example, if dsn begins with "dbi:mysql:", dbi-connect tries to load dbd.mysql.

Interpretation of the options part is up to the driver. Usually it is in the form of key1=value1;key2=value2;..., but some driver may interpret it differently. For example, mysql driver allows you to specify a database name at the beginning of options. You have to check out the document of each driver for the exact specification of options.

The keyword arguments gives extra information required for connection. The username and password are commonly supported arguments. The driver may recognize more keyword arguments.

If a connection to the database is successfully established, a connection object (an instance of a subclass of <dbi-connection>) is returned. Otherwise, an error is signaled.

Class: <dbi-connection> ¶

{dbi} The base class of a connection to a database system. Each driver defines a subclass of this to keep information about database-specific connections.

Method: dbi-open? (c <dbi-connection>) ¶

{dbi} Queries whether a connection to the database is still open (active).

Method: dbi-close (c <dbi-connection>) ¶

{dbi} Closes a connection to the database. This causes releasing resources related to this connection. Once closed, c cannot be used for any dbi operations (except passing to dbi-open?). Calling dbi-close on an already closed connection has no effect.

Although a driver usually closes a connection when <dbi-connection> object is garbage-collected, it is not a good idea to rely on that, since the timing of GC is unpredictable. The user program must make sure that it calls dbi-close at a proper moment.

Function: dbi-list-drivers ¶

{dbi} Returns a list of module names of known drivers.

Class: <dbi-driver> ¶

{dbi} The base class of a driver. You usually don’t need to see this as far as you’re using the high-level dbi API.

Function: dbi-make-driver driver-name ¶

{dbi} This is a low-level function called from dbi-connect method, and usually a user doesn’t need to call it.

Loads a driver module specified by driver-name, and instantiate the driver class and returns it.

Preparing and issuing queries

Method: dbi-prepare conn sql :key pass-through … ¶

{dbi} From a string representation of SQL statement sql, creates and returns a query object (an instance of <dbi-query> or its subclass) for the database connection conn

Sql may contain parameter slots, denoted by ?.

(dbi-prepare conn "insert into tab (col1, col2) values (?, ?)")

(dbi-prepare conn "select * from tab where col1 = ?")

They will be filled when you actually issue the query by dbi-execute. There are some advantages of using parameter slots: (1) The necessary quoting is done automatically. You don’t need to concern about security holes caused by improper quoting, for example. (2) Some drivers support a feature to send the template SQL statement to the server at the preparation stage, and send only the parameter values at the execution stage. It would be more efficient if you issue similar queries lots of time.

If the backend doesn’t support prepared statements (SQL templates having ? parameters), the driver may use text.sql module to parse sql. It may raise <sql-parse-error> condition if the given SQL is not well formed.

You may pass a true value to the keyword argument pass-through to suppress interpretation of SQL and pass sql as-is to the back end database system. It is useful if the back-end supports extension of SQL which text.sql doesn’t understand.

If the driver lets prepared statement handled in back-end, without using text.sql, the pass-through argument may be ignored. The driver may also take other keyword arguments. Check out the documentation of individual drivers.

Note: Case folding of SQL statement is implementation dependent. Some DBMS may treat table names and column names in case insensitive way, while others do in case sensitive way. To write a portable SQL statement, make them quoted identifiers, that is, always surround names by double quotes.

Class: <dbi-query> ¶

{dbi} Holds information about prepared query, created by dbi-prepare. The following slots are defined.

Instance Variable of <dbi-query>: connection ¶

Contains the <dbi-connection> object.

Instance Variable of <dbi-query>: prepared ¶

If the driver prepares query by itself, this slot may contain a prepared statement. It is up to each driver how to use this slot, so the client shouldn’t rely on its value.

Method: dbi-open? (q <dbi-query>) ¶

{dbi} Returns #t iff the query can still be passed to dbi-execute.

Method: dbi-close (q <dbi-query>) ¶

{dbi} Destroy the query and free resources associated to the query. After this operation, dbi-open? returns #f for q, and the query can’t be used in any other way. Although the resource may be freed when q is garbage-collected, it is strongly recommended that the application closes queries explicitly.

Method: dbi-execute (q <dbi-query>) parameter … ¶

{dbi} Executes a query created by dbi-prepare. You should pass the same number of parameters as the query expects.

If the issued query is select statement, dbi-execute returns an object represents a relation. A relation encapsulates the values in rows and columns, as well as meta information like column names. See "Retrieving query results" below for how to access the result.

If the query is other types, such as create, insert or delete, the return value of the query closure is unspecified.

Method: dbi-do conn sql :optional options parameter-value … ¶

{dbi} This is a convenience procedure when you create a query and immediately execute it. It is equivalent to the following expression, although the driver may overload this method to avoid creating intermediate query object to avoid the overhead.

(dbi-execute (apply dbi-prepare conn sql options)
             parameter-value …)

Method: dbi-escape-sql conn str ¶

{dbi} Returns a string where special characters in str are escaped.

The official SQL standard only specify a single quote (') as such character. However, it doesn’t specify non-printable characters, and the database system may use other escaping characters. So it is necessary to use this method rather than doing escaping by your own.

;; assumes c is a valid DBI connection
(dbi-escape-sql c "don't know")
  ⇒ "don''t know"

Retrieving query results

If the query is a select statement, it returns an object of both <collection> and <relation>. It is a collection of rows (that is, it implements <collection> API), so you can use map, for-each or other generic functions to access rows. You can also use the relation API to retrieve column names and accessors from it. See util.relation - Relation framework, for the relation API, and gauche.collection - Collection framework, for the collection API.

The actual class of the object returned from a query depends on the driver, but you may use the following method on it.

Method: dbi-open? result ¶

{dbi} Check whether the result of a query is still active. The result may become inactive when it is explicitly closed by dbi-close and/or the connection to the database is closed.

Method: dbi-close result ¶

{dbi} Close the result of the query. This may cause releasing resources related to the result. You can no longer use result once it is closed, except passing it to dbi-open?.

Although a driver usually releases resources when the result is garbage-collected, the application shouldn’t rely on that and is recommended call dbi-close explicitly when it is done with the result.

---

12.27.2 Writing drivers for DBI

Writing a driver for a specific database system means implementing a module dbd.foo, where foo is the name of the driver.

The module have to implement several classes and methods, as explained below.

DBI classes to implement

You have to define the following classes.

• Subclass <dbi-driver>. The class name must be <foo-driver>, where foo is the name of the driver. Usually this class produces a singleton instance, and is only used to dispatch dbi-make-connection method below.
• Subclass <dbi-connection>. An instance of this class is created by dbi-make-connection. It needs to keep the information about the actual connections.
• Subclass <relation> and <collection> to represent query results suitable for the driver. (In most cases, the order of the result of SELECT statement is significant, since it may be sorted by ORDER BY clause. Thus it is more appropriate to inherit <sequence>, rather than <collection>).
• Optionally, subclass <dbi-query> to keep driver-specific information of prepared queries.

DBI methods to implement

The driver need to implement the following methods.

Method: dbi-make-connection (d <foo-driver>) (options <string>) (options-alist <list>) :key username password … ¶

{dbi} This method is called from dbi-connect, and responsible to connect to the database and to create a connection object. It must return a connection object, or raise an <dbi-error> if it cannot establish a connection.

Options is the option part of the data source name (DSN) given to dbi-connect. options-alist is an assoc list of the result of parsing options. Both are provided so that the driver may interpret options string in nontrivial way.

For example, given "dbi:foo:myaddressbook;host=dbhost;port=8998" as DSN, foo’s dbi-make-connection will receive "myaddressbook;host=dbhost;port=8998" as options, and (("myaddressbook" . #t) ("host" . "dbhost") ("port" . "8998")) as options-alist.

After options-alist, whatever keyword arguments given to dbi-connect are passed. DBI protocol currently specifies only username and password. The driver may define other keyword arguments. It is recommended to name the driver-specific keyword arguments prefixed by the driver name, e.g. for dbd.foo, it may take a :foo-whatever keyword argument.

It is up to the driver writer to define what options are available and the syntax of the options. The basic idea is that the DSN identifies the source of the data; it’s role is like URL in WWW. So, it may include the hostname and port number of the database, and/or the name of the database, etc. However, it shouldn’t include information related to authentication, such as username and password. That’s why those are passed via keyword arguments.

Method: dbi-prepare (c <foo-connection>) (sql <string>) :key pass-through … ¶

{dbi} This method should create and return a prepared query object, which is an instance of <dbi-query> or its subclass. The query specified by sql is issued to the database system when the prepared query object is passed to dbi-execute.

The method must set c to the connection slot of the returned query object.

Sql is an SQL statement. It may contain placeholders represented by '?'. The query closure should take the same number of arguments as of the placeholders. It is up to the driver whether it parses sql internally and construct a complete SQL statement when the query closure is called, or it passes sql to the back-end server to prepare the statement and let the query closure just send parameters.

If the driver parses SQL statement internally, it should recognize a keyword argument pass-through. If a true value is given, the driver must treat sql opaque and pass it as is when the query closure is called.

The driver may define other keyword arguments. It is recommended to name the driver-specific keyword arguments prefixed by the driver name, e.g. for dbd.foo, it may take a :foo-whatever keyword argument.

Method: dbi-execute-using-connection (c <foo-connection>) (q <dbi-query>) (params <list>) ¶

{dbi} This method is called from dbi-execute. It must issue the query kept in q. If the query is parameterized, the actual parameters given to dbi-execute are passed to params argument.

If q is a select-type query, this method must return an appropriate relation object.

Method: dbi-escape-sql (c <foo-connection>) str ¶

{dbi} If the default escape method isn’t enough, the driver may overload this method to implement a specific escaping. For example, MySQL treats backslash characters specially as well as single quotes, so it has its dbi-escape-sql method.

Method: dbi-open? (c <foo-connection>) ¶

Method: dbi-open? (q <foo-query>) ¶

Method: dbi-open? (r <foo-result>) ¶

Method: dbi-close (c <foo-connection>) ¶

Method: dbi-close (q <foo-query>) ¶

Method: dbi-close (r <foo-result>) ¶

{dbi} Queries open/close status of a connection and a result, and closes a connection and a result. The close methods should cause releasing resources used by connection/result. The driver has to allow dbi-close to be called on a connection or a result which has already been closed.

Method: dbi-do (c <foo-connection>) (sql <string>) :optional options parameter-value … ¶

{dbi} The default method uses dbi-prepare and dbi-execute to implement the function. It just works, but the driver may overload this method in order to skip creating intermediate query object for efficiency.

DBI utility functions

The following functions are low-level utilities which you may use to implement the above methods.

Function: dbi-parse-dsn data-source-name ¶

{dbi} Parse the data source name (DSN) string given to dbi-connect, and returns tree values: (1) The driver name in a string. (2) ’options’ part of DSN as a string. (3) parsed options in an assoc list. This may raise <dbi-error> if the given string doesn’t conform DSN syntax.

You don’t need to use this to write a typical driver, for the parsing is done before dbi-make-connection is called. This method may be useful if you’re writing a kind of meta-driver, such as a proxy.

Function: dbi-prepare-sql connection sql ¶

{dbi} Parses an SQL statement sql which may contain placeholders, and returns a closure, which generates a complete SQL statement when called with actual values for the parameters. If the back-end doesn’t support prepared statements, you may use this function to prepare queries in the driver.

Connection is a DBI connection to the database. It is required to escape values within SQL properly (see dbi-escape-sql above).

;; assume c contains a valid dbi connection
((dbi-prepare-sql c "select * from table where id=?") "foo'bar")
 => "select * from table where id='foo''bar'"