2
\chapter{The New XSB-Database Interface} \label{db_interface}
3
%====================================================
6
{\Large {\bf By Saikat Mukherjee and Michael Kifer }}
10
%=====================
12
The XSB-DB interface is a package that allows XSB users to access
13
databases through various drivers. Using this interface, information
14
in different DBMSs can be accessed by SQL queries. The interface
15
defines Prolog predicates which makes it easy to connect to databases,
16
query them, and disconnect from the databases. Central to the concept
17
of a connection to a database is the notion of a \emph{handle}. A
18
connection handle describes a particular connection to a database.
19
Similar to a connection handle is the notion of a query handle which
20
describes a particular query statement. As a consequence of the
21
handles, it is possible to open multiple database connections (to the
22
same or different databases) and keep alive multiple queries (again
23
from the same or different connections). The interface also supports
24
dynamic loading of drivers. As a result, it is possible to query
25
databases using different drivers concurrently~\footnote{In
26
\version{}, this package has not been ported to the multi-threaded
29
\section{Configuring the Interface}
31
Generally, each driver has to be configured separately, but if the database
32
packages such as ODBC, MySql, etc., are installed in standard places then
33
the XSB configuration mechanism will do the job automatically.
35
Under Windows, the command
41
in the diectory {\tt XSB$\backslash$build} will normally configure the ODBC
42
driver without problems. For the MySQL driver one has to edit the file
45
packages\dbdrivers\mysql\cc\NMakefile.mak
48
to indicate where MySQL is installed.
51
Under Unix, the {\tt configure} script will build the drivers automatically
52
if the {\tt --with-dbdrivers} option is specified. If, however, ODBC and
53
MySQL are not installed in their standard places, you will have to provide
54
the following parameters to the configure script:
57
\item {\tt --with-odbc-libdir=LibDIR} -- {\tt LibDIR} is the directory
58
where the library libodbc.so lives on your system.
59
\item {\tt --with-odbc-incdir=IncludeDIR} -- {\tt IncludeDIR} is the
60
directory where the ODBC header files, such as {\tt sql.h} live.
61
\item {\tt --with-mysql-libdir=MySQLlibdir} -- {\tt MySQLlibdir} is the
62
directory where MySQL's shared libraries live on your system.
63
\item {\tt --with-mysql-incdir=MySQLincludeDir} -- {\tt MySQLincludeDir} is
64
the directory where MySQL's header files live.
68
Under Cygwin, the ODBC libraries come with the distribution; they are
69
located in the directory {\tt /cygdrive/c/cygwin/lib/w32api/} and are called
70
{\tt odbc32.a} and {\tt odbccp32.a}. (Check if your installation is complete
71
and has these libraries!)
72
Otherwise, the configuration of the interface under cygwin is
73
same as in unix (you do not need to provide any ODBC-specific parameters to
74
the configure script under Cygwin).
77
If at the time of configuring XSB some database packages ({\it e.g.},
78
MySQL) are not installed on your system, you can install them later and
79
configure the XSB interface to them then. For instance, to configure the
80
ODBC interface separately, you can type
83
cd packages/dbdrivers/odbc
87
Again, if ODBC is installed in a non-standard location, you might need to
88
supply the options {\tt --with-odbc-libdir} and {\tt --with-odbc-incdir} to
89
the configure script. Under Cygwin ODBC is always installed in a
90
standard place, and {\tt configure} needs no additional parameters.
92
Under Windows, separate configuration of the XSB-DB interfaces is also
93
possible, but you need Visual Studio installed. For instance, to configure
94
the MySQL interface, type
97
cd packages\dbdrivers\mysql\cc
98
nmake /f NMakefile.mak
101
As before, you might need to edit the NMakefile.mak script to tell the
102
compiler where the required MySQL's libraries are. You also need the file
103
{\tt packages$\backslash$dbdrivers$\backslash$mysql$\backslash$mysql\_init.P} with the following content:
106
:- export mysql_info/2.
107
mysql_info(support, 'yes').
108
mysql_info(libdir, '').
109
mysql_info(ccflags, '').
110
mysql_info(ldflags, '').
114
Similarly, to configure the ODBC interface, do
117
cd packages\dbdrivers\odbc\cc
118
nmake /f NMakefile.mak
121
You will also need to create the file {\tt packages$\backslash$dbdrivers$\backslash$odbc$\backslash$odbc\_init.P}
122
with the following contents:
125
:- export odbc_info/2.
126
odbc_info(support, 'yes').
127
odbc_info(libdir, '').
128
odbc_info(ccflags, '').
129
odbc_info(ldflags, '').
134
\section{Using the Interface}
135
%========================================
137
We use the {\tt student} database as our example to illustrate
138
the usage of the XSB-DB interface in this manual. The schema of the
139
student database contains three columns viz. the student name,
140
the student id, and the name of the advisor of the student.
142
The XSB-DB package has to be first loaded before using any of the
143
predicates. This is done by the call:
149
Next, the driver to be used for connecting to the database has to
150
be loaded. Currently, the interface has support for a native MySQL driver
151
(using the MySQL C API), and an ODBC driver. For example, to load
152
the ODBC driver call:
155
| ?- load_driver(odbc).
158
Similarly, to load the mysql driver call:
161
| ?- load_driver(mysql).
164
\subsection{Connecting to and Disconnecting from Databases}
165
%=================================================
167
There are two predicates for connecting to databases, {\tt db\_connect/5}
168
and {\tt db\_connect/6}.
169
The {\tt db\_connect/5} predicate is for ODBC connections, while {\tt
171
for other (non-ODBC) database drivers.
174
| ?- db_connect(+Handle, +Driver, +DSN, +User, +Password).
175
| ?- db_connect(+Handle, +Driver, +Server, +Database, +User, +Password).
178
The {\tt db\_connect/5} predicate
179
assumes that an entry for a data source name (DSN) exists in the {\tt
180
odbc.ini} file. The {\tt Handle} is the handle name used for the
181
connection. The {\tt Driver} is the driver being used for the connection.
182
The {\tt User} and {\tt Password} are the user name and password being used
183
for the connection. The user is responsible for giving the name to the
184
handle. To connect to the data source mydb using the user name xsb and
185
password xsb with the odbc driver, the call is as follows:
188
| ?- db_connect(ha, odbc, mydb, xsb, xsb).
192
where {\tt ha} is the user-chosen handle name (a Prolog atom) for the
195
The {\tt db\_connect/6} predicate is used for drivers other than ODBC. The
196
arguments {\tt Handle}, {\tt Driver}, {\tt User}, and {\tt Password} are
197
the same as for {\tt db\_connect/5}. The {\tt Server} and {\tt Database}
198
arguments specify the server and database to connect to. For example, for
199
a connection to a database called {\tt test} located on the server {\tt
200
wolfe} with the user name {\tt xsb}, the password {\tt foo}, and using
201
the {\tt mysql} driver, the call is:
204
| ?- db_connect(ha, mysql, wolfe, test, xsb, foo).
208
where {\tt ha} is the handle name the user chose for the connection.
210
If the connection is successfully made, the predicate invocation will
211
succeed. This step is necessary before anything can be done with the
212
data sources since it gives XSB the opportunity to initialize system
213
resources for the session.
215
To close a database connection use:
218
| ?- db_disconnect(Handle).
222
where handle is the handle name for the connection. For example,
223
to close the connection to above mysql database call:
226
| ?- db_disconnect(ha).
229
and XSB will give all the resources it allocated for this session back
233
\subsection{Querying Databases}
234
%=====================================
236
The interface supports two types of querying. In direct querying, the
237
query statement is not prepared while in prepared querying the query
238
statement is prepared before being executed. The results from
239
both types of querying are retrieved tuple at a time.
240
Direct querying is done by the predicate:
243
| ?- db_query(ConnectionHandle, QueryHandle, SQLQueryList, ReturnList).
246
ConnectionHandle is the name of the handle used for the database connection.
247
QueryHandle is the name of the handle for this particular query.
248
Currently, the query handle is being used only for prepared queries.
249
However, in future versions, the query handle can be used in direct
250
queries to retrieve arbitrary tuples from a result set using cursors.
251
Also, it will be possible to combine arbitrary tuples from different
252
queries to the same database using the query handle.
253
The SQLQueryList is a list of terms which is used to build the SQL query.
254
The terms in this list are ground atoms. ReturnList is a list of
255
variables each of which correspond to a return value in the query.
256
It is upto the user to specify the correct number of return variables
257
corresponding to the query. Also, as in the case of a connection handle,
258
the user is responsible for giving the name to the query handle.
259
For example, a query on the student database to select all the students
260
for a given advisor is accomplished by the call:
264
db_query(ha, qa, ['select T.name from student T where T.advisor = ', X], [P]),
269
where {\tt ha} and {\tt qa} are respectively the connection handle and query
270
handle name the user chose.
272
Observe that the query list is composed of the sql string and a ground value
273
for the advisor. The return list is made of one variable corresponding to
274
the student name. The failure drive loop retrieves all the tuples.
276
Preparing a query is done by the call to the predicate:
279
| ?- db_prepare(ConnectionHandle, QueryHandle, SQLQueryList).
282
As before, ConnectionHandle and QueryHandle specify the handles for
283
the connection and the query. The SQLQueryList is a list of terms which
284
build up the query string. The placeholder `?' is used for values which
285
have to be bound during the execution of the statement.
286
For example, to prepare a query for selecting the advisor name for a student
287
name using our student database:
290
| ?- db_prepare(ha, qa, ['select T.advisor from student T where T.name = ?']).
293
A prepared statement is executed using the predicate:
296
| ?- db_prepare_execute(QueryHandle, BindList, ReturnList).
299
The BindList contains the ground values corresponding to the `?' in
300
the prepared statement. The ReturnList is a list of variables for
301
each argument in a tuple of the result set.
303
For direct querying, the statement handle is closed automatically when
304
all the tuples in the result set have been retrieved. In order to explicitly
305
close a statement handle, and free all the resources associated with
306
the handle, a call is made to the predicate:
309
| ?- db_statement_close(QueryHandle).
313
where QueryHandle is the query handle for the statement to be closed.
315
The interface is also able to transparently handle Prolog terms.
316
Users can both save and retrieve terms without any special processing.
319
\section{Error Handling}
320
%============================
322
Each predicate in the XSB-DB interface throws an exception with the functor
325
xsb_error(database(Number), Message)
328
where Number is a string with the
329
error number and Message is a string with a slightly detailed error message.
330
It is upto the user to catch this exception
331
and proceed with error handling. This is done by the throw-catch error
332
handling mechanism in XSB. For example, in order to catch the error which
333
will be thrown when the user attempts to close a database connection for
334
a handle {\tt (ha}) which does not exist:
337
| ?- catch(db_disconnect(ha),
338
xsb_error(database(Number), Message), handler(Number, Message)).
341
It is the user's responsibility to define the handler predicate which can be as simple
342
as printing out the error number and message or may involve more
343
complicated processing.
345
A list of error numbers and messages that are thrown by the XSB-DB interface
350
\item {\bf XSB\_DBI\_001: XSB\_DBI ERROR: Driver already registered}\\
351
This error is thrown when the user tries to load a driver, using the {\tt load\_driver}
352
predicate, which has already been loaded previously.
354
\item {\bf XSB\_DBI\_002: XSB\_DBI ERROR: Driver does not exist}\\
355
This error is thrown when the user tries to connect to a database, using
356
{\tt db\_connect}, with a driver which has not been loaded.
358
\item {\bf XSB\_DBI\_003: XSB\_DBI ERROR: Function does not exist in this driver}\\
359
This error is thrown when the user tries to use a function support
360
for which does not exist in the corresponding driver. For example,
361
this error is generated if the user
362
tries to use {\tt db\_prepare} for a connection established with the
365
\item {\bf XSB\_DBI\_004: XSB\_DBI ERROR: No such connection handle}\\
366
This error is thrown when the user tries to use a connection handle
367
which has not been created.
369
\item {\bf XSB\_DBI\_005: XSB\_DBI ERROR: No such query handle}\\
370
This error is thrown when the user tries to use a query handle which has
373
\item {\bf XSB\_DBI\_006: XSB\_DBI ERROR: Connection handle already exists}\\
374
This error is thrown when the user tries to create a connection handle in
375
{\tt db\_connect} using a name which already exists as a connection handle.
377
\item {\bf XSB\_DBI\_007: XSB\_DBI ERROR: Query handle already exists}\\
378
This error is thrown when the user tries to create a query handle, in
379
{\tt db\_query} or {\tt db\_prepare}, using a name which already exists as
380
a query handle for a different query.
382
\item {\bf XSB\_DBI\_008: XSB\_DBI ERROR: Not all parameters supplied}\\
383
This error is thrown when the user tries to execute a prepared statement,
384
using {\tt db\_prepare\_execute}, without supplying values for all the
385
parameters in the statement.
387
\item {\bf XSB\_DBI\_009: XSB\_DBI ERROR: Unbound variable in parameter list}\\
388
This error is thrown when the user tries to execute a prepared statement,
389
using {\tt db\_prepare\_execute}, without binding all the parameters of the
394
\section{Notes on specific drivers}
395
%============================
397
\subsubsection{ODBC Driver}
399
The ODBC driver has been tested in Linux using the {\tt unixodbc} driver
400
manager. It currently supports the following functionality: (a)
401
connecting to a database using a DSN, (b) direct querying of the database,
402
(c) using prepared statements to query the database, (d) closing a
403
statement handle, and (d) disconnecting from the database.
404
The ODBC driver has also been tested under Windows and Cygwin.
406
\subsubsection{MySQL Driver}
408
The MySQL driver has been implemented using the native MySQL C API.
409
Currently, it has support for the following functionality: (a) connecting
410
to a database using the non-DSN {\tt db\_connect}, (b) direct querying of
411
the database, and (c) disconnecting from the database. Support for prepared
412
statements will be available from MySQL 4.1 version at which point the
413
driver can be updated with such a feature.
415
The MySQL driver has been tested under Linux and Windows.
420
%%% TeX-master: "manual2"
added
removed
docs/userman/xsb_dbi.tex
