This library provides database independent access procedures. The database specific operations are provided in database driver (DBD) libraries.
Following example describes how to query a database using (dbd odbc)DBD library.
(import (dbi))
(define conn (dbi-connect "dbi:odbc:server=XE"
:username "username"
:password "password"))
(let* ((query (dbi-prepare conn "SELECT * FROM table WHERE id > ?"))
(result (dbi-execute-query! query 1)))
(do ((row (dbi-fetch! result) (dbi-fetch! result)))
((not row))
(vector-for-each (lambda (col) (print col)) row)))
There is nothing specific to the underlying database system except the
argument "dbi:odbc:server=XE" passed to dbi-connect, from which
dbi library figures out that it is an access to odbc, loads
(dbd odbc) library and let it handle the specific operations.
If you want to use other database named boo, then you just need to pass
"dbi:_boo_:_parameter_" to dbi-connect instead. As long
as you have (dbd boo) installed then everything stays the same.
Connects 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 a corresponding
driver library, (dbd _driver_), installed in your system.
Interpretation of the options part is up to the driver. Usually it is
in the form of key1=value1;key2=value2;.... However the DBD
implementations can have different format so you need to check the document
of each driver for exact specification of options.
rest argument will be passed to the underlying procedure.
NOTE: username, password and auto-commit are strongly encouraged to be implemented in the DBD library.
If a connection to the database is successfully established, a connection object is returned.
Checks if the given connection is still open.
The method shall return #t if the given connection is still open, otherwise it shall return #f.
Closes a connection to the database.
NOTE: Users are strongly encouraged to close a connection explicitly. DBD might not close opened connections automatically.
Commits or rollback transactions on the given connection, respectively.
From a string representation of SQL sql, creates and returns a query object for the database connection conn.
sql may contain parameter slot, denoted by ?.
(dbi-prepare conn "insert into tab (col1, col2) values (?, ?)")
(dbi-prepare conn "select * from tab where col1 = ?")If args is not null, then the procedure shall bind given parameters to the place holder.
Checks if the given query is still open.
The method shall return #t if the given query is still open, otherwise it shall return #f.
Closes a query.
NOTE: Users are strongly encouraged to close a query explicitly. DBD might not close opened query automatically.
Binds the given value to query at index.
The procedure shall accept integer index and may accept other type of index.
Executes given query. If the args is not null, then procedure shall bind the given args as its parameters.
The dbi-execute! shall return a integer value representing affected
row count.
The dbi-execute-query! shall return a result set object which can be
used with dbi-fetch! or dbi-fetch-all!. The implementation may
allow to return a specific result set object and it is users' responsibility
to use it with fetching the result.
NOTE: There is a default implementation of dbi-execute-query! and
returns the given query as a result set object.
Fetches a row or all rows from the given query.
The dbi-fetch! shall return a vector representing the query result, if
there is no more data available it shall return #f.
The dbi-fetch-all! shall return a list of vectors representing the
query result.
NOTE: There is a default implementation of dbi-fetch-all!, it calls
dbi-fetch! until it returns #f.
Commits or rollback transactions on the given query, respectively.
Returns a column names affected by the given query.
The procedure shall return a vector as its result.
The DBI library provides some of conditions which should be raised by underlying DBD implementations.
NOTE: The listed conditions may or may not be raised by underlying DBD implementation.
Root condition of DBI condition. &dbi-error is a sub condition
of &error.
All DBI related condition should inherit this condition.
This condition is raised when DBD implementation can't be found.
condition-driver-name returns missing driver name.
This condition indicates that DBI feature is not supported.
Implementations should raise this condition when methods can't be implemented on the target DBMS.
This condition holds SQL status code.
Implementations should raise this condition when SQL execution is failed with SQL status code.
Writing a driver for specific data base system means implementing a library
(dbd _foo_) where foo is the name of the driver.
The library have to implement a creating a driver procedure and several classes and methods as explained below.
The method described above section must behave as it expected there, especially behaviours described with shall.
The driver will be created by the procedure named make-_foo_-driver.
And it is strongly encouraged to be implemented as a subclass of
<dbi-driver> for future extension.
You have to define the following classes.
Subclass <dbi-connection>. An instance of this class is created
by dbi-make-connection. It needs to keep the information about the
actual connections.
Optional: subclass <dbi-driver> for actual driver instance.
Optional: subclass <dbi-query> to keep driver specific information
of prepared statement.
The driver needs to implement the following methods.
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 error which should be a sub condition of &dbi-error.
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 given to dbi-connect are passed.
The driver is strongly encouraged to implement :username,
:password and :auto-commit (if the database is supported)
keyword arguments to specify the authentication information and commit mode.
The method must create and return a prepared query object which is an
instance of <dbi-query> or its subclass.
sql is an SQL statement. It may contain placeholders represented by
'?'. The implementation must accept it to keep DBI portable even though
the database doesn't.
Binds a parameter value at the place index.
Queries open/close status of a connection and a query, and closes
a connection and a query. The close method should cause releasing resources
used by connection/query. The driver has to allow dbi-close to be
called on a connection or a query which has already been closed.
Commits/rollbacks a connection or a query.
Implementation must behave as described above section.
Database data type and Scheme type are usually not the same. However to keep DBI portable it is important to follow a guideline. Here I suggest the data conversion between a database and Scheme object.
Following is database data type to Scheme type conversion guideline. The driver implementation should follow.
String
Bytevector
Date from SRFI-19
Time from SRFI-19
Binary port, preferably not retrieving all data at once.
Textual port, preferably not retrieving all data at once.