
\chapter{The New XSB-Database Interface} \label{db_interface}
%====================================================

\begin{center}
{\Large {\bf By Saikat Mukherjee and Michael Kifer }}
\end{center}

\section{Introduction}
%=====================

The XSB-DB interface is a package that allows XSB users to access
databases through various drivers. Using this interface, information
in different DBMSs can be accessed by SQL queries. The interface defines
Prolog predicates which makes it easy to connect to databases, 
query them, and disconnect from the databases. Central to the 
concept of a connection to a database is the notion of a \emph{handle}.
A connection handle describes a particular connection to a database.
Similar to a connection handle is the notion of a query handle which
describes a particular query statement. As a consequence of the handles,
it is possible to open multiple database connections (to the same or
different databases) and keep alive multiple queries (again from the
same or different connections). The interface also supports dynamic loading of
drivers. As a result, it is possible to query databases using different drivers
concurrently.

\section{Configuring the Interface}

Generally, each driver has to be configured separately, but if the database
packages such as ODBC, MySql, etc., are installed in standard places then
the XSB configuration mechanism will do the job automatically.

Under Windows, typing
%%
\begin{verbatim}
    makexsb_wind  
\end{verbatim}
%%
in the XSB's {\tt build} directory will normally configure the ODBC driver
without problems. For the MySQL driver one has to edit the file
%%
\begin{verbatim}
    packages\dbdrivers\mysql\cc\NMakefile.mak  
\end{verbatim}
%%
to indicate where MySQL is installed.


Under Unix, the {\tt configure} script should also build the drivers automatically.
If, however, ODBC and MySQL are not installed in their standard places, you
will have to provide the following parameters to the configure script:
%%
\begin{itemize}
\item  {\tt --with-odbc-libdir=LibDIR} -- {\tt LibDIR} is the directory
  where the library libodbc.so lives on your system. 
\item  {\tt --with-odbc-incdir=IncludeDIR} -- {\tt IncludeDIR} is the
  directory where the ODBC header files, such as {\tt sql.h} live.
\item {\tt --with-mysql-libdir=MySQLlibdir} -- {\tt MySQLlibdir} is the
  directory where MySQL's shared libraries live on your system.
\item {\tt --with-mysql-incdir=MySQLincludeDir} -- {\tt MySQLincludeDir} is
  the directory where MySQL's header files live.  
\end{itemize}
%%

Under Cygwin, the interface needs to be linked with the Visual Studio ODBC libraries.
By default, the odbc interface assumes the location of the odbc32.lib and 
odbccp32.lib as 
\begin{verbatim}
C:\Program Files\Microsoft Visual Studio\VC98\lib\. 
\end{verbatim}
In the event of these libraries being installed in a different directory,
ypu have to manually change the line in the function make\_odbc\_driverH 
which is defined in the file 
\begin{verbatim}
packages/dbdrivers/odbc/odbc_driver_config.P
\end{verbatim}
where the paths are
specified. Otherwise, the configuration of the interface under cygwin is
same as in unix.


If at the time of configuring XSB some database packages ({\it e.g.},
MySQL) are not installed on your system, you can install them later and
configure the XSB interface to them then. For instance, to configure the
ODBC interface separately, you can type
%%
\begin{verbatim}
    cd packages/dbdrivers/odbc
    configure
\end{verbatim}
%%
Again, if ODBC is installed in a non-standard location, you might need to
supply the options {\tt --with-odbc-libdir} and {\tt --with-odbc-incdir}
to the configure script.

Under Windows, separate configuration of the XSB-DB interfaces is also
possible, but you need Visual Studio installed. For instance, to do so for
the MySQL interface, type
%%
\begin{verbatim}
    cd packages\dbdrivers\mysql\cc  
    nmake /f NMakefile.mak
\end{verbatim}
%%
As before, you might need to edit the NMakefile.mak script to tell the
compiler where the required MySQL's libraries are.


\section{Using the Interface}
%========================================

We use the {\tt student} database as our example to illustrate 
the usage of the XSB-DB interface in this manual. The schema of the
student database contains three columns viz. the student name, 
the student id, and the name of the advisor of the student.

The XSB-DB package has to be first loaded before using any of the
predicates. This is done by the call:

\begin{verbatim}
| ?- [dbdrivers].
\end{verbatim}

Next, the driver to be used for connecting to the database has to 
be loaded. Currently, the interface has support for a native MySQL driver
(using the MySQL C API), and an ODBC driver. For example, to load 
the ODBC driver call:

\begin{verbatim}
| ?- load_driver(odbc).
\end{verbatim}

Similarly, to load the mysql driver call:

\begin{verbatim}
| ?- load_driver(mysql).
\end{verbatim}

\subsection{Connecting to and Disconnecting from Databases}
%=================================================

There are two predicates for connecting to databases. 

\begin{verbatim}
| ?- db_connect(Handle, Driver, DSN, User, Password).
\end{verbatim}

This predicate is specific for an ODBC connection only.
This predicate assumes that an entry for a data source name (DSN) exists
in the {\tt ODBC.INI} file. The {\tt Handle} is the handle name used for 
the connection. The {\tt Driver} is the driver being used for the 
connection.
The {\tt User} and {\tt Password} are the user name and password 
being used for the connection. The user is responsible for giving 
the name to the handle.
To connect to the data source mydb using the user name xsb and password xsb
with the odbc driver, the call is as follows: 

\begin{verbatim}
| ?- db_connect(ha, odbc, mydb, xsb, xsb).
\end{verbatim}

\noindent
where {\tt ha} is the handle name that the user chose for the connection.
The following version of {\tt db\_connect} is used for drivers other than
ODBC.

\begin{verbatim}
| ?- db_connect(Handle, Driver, Server, Database, User, Password).
\end{verbatim}

The {\tt Handle}, {\tt Driver}, {\tt User},
and {\tt Password} are the same as before. The {\tt Server} and 
{\tt Database} arguments specify the server and database to connect to.
For example, for a connection to a database called test in the server
wolfe with the user name xsb, password xsb, and using the mysql
driver, the call is:

\begin{verbatim}
| ?- db_connect(ha, mysql, wolfe, test, xsb, xsb).
\end{verbatim}

\noindent
where {\tt ha} is the handle name the user chose for the connection.

If the connection is successfully made, the predicate invocation will
succeed.  This step is necessary before anything can be done with the
data sources since it gives XSB the opportunity to initialize system
resources for the session.

To close a database connection use:

\begin{verbatim}
| ?- db_disconnect(Handle).
\end{verbatim}

\noindent
where handle is the handle name for the connection. For example, 
to close the connection to above mysql database call:

\begin{verbatim}
| ?- db_disconnect(ha).
\end{verbatim}

and XSB will give all the resources it allocated for this session back
to the system.


\subsection{Querying Databases}
%=====================================

The interface supports two types of querying. In direct querying, the
query statement is not prepared while in prepared querying the query
statement is prepared before being executed. The results from
both types of querying are retrieved tuple at a time.
Direct querying is done by the predicate:

\begin{verbatim}
| ?- db_sql(ConnectionHandle, QueryHandle, SQLQueryList, ReturnList).
\end{verbatim}

ConnectionHandle is the name of the handle used for the database connection.
QueryHandle is the name of the handle for this particular query. 
Currently, the query handle is being used only for prepared queries. 
However, in future versions, the query handle can be used in direct
queries to retrieve arbitrary tuples from a result set using cursors.
Also, it will be possible to combine arbitrary tuples from different 
queries to the same database using the query handle.
The SQLQueryList is a list of terms which is used to build the SQL query.
The terms in this list are ground atoms. ReturnList is a list of 
variables each of which correspond to a return value in the query.
It is upto the user to specify the correct number of return variables 
corresponding to the query. Also, as in the case of a connection handle, 
the user is responsible for giving the name to the query handle.
For example, a query on the student database to select all the students
for a given advisor is accomplished by the call:

\begin{verbatim}
| ?- X = adv,
     db_sql(ha, qa, ['select T.name from student T where T.advisor = ', X], [P]),
     fail.
\end{verbatim}

\noindent
where {\tt ha} and {\tt qa} are respectively the connection handle and query
handle name the user chose.

Observe that the query list is composed of the sql string and a ground value
for the advisor. The return list is made of one variable corresponding to
the student name. The failure drive loop retrieves all the tuples.

Preparing a  query is done by the call to the predicate:

\begin{verbatim}
| ?- db_prepare(ConnectionHandle, QueryHandle, SQLQueryList).
\end{verbatim}

As before, ConnectionHandle and QueryHandle specify the handles for
the connection and the query. The SQLQueryList is a list of terms which
build up the query string. The placeholder `?' is used for values which 
have to be bound during the execution of the statement.
For example, to prepare a query for selecting the advisor name for a student
name using our student database:

\begin{verbatim}
| ?- db_prepare(ha, qa, ['select T.advisor from student T where T.name = ?']).
\end{verbatim}

A prepared statement is executed using the predicate:

\begin{verbatim}
| ?- db_prepare_execute(QueryHandle, BindList, ReturnList).
\end{verbatim}

The BindList contains the ground values corresponding to the `?' in
the prepared statement. The ReturnList is a list of variables for
each argument in a tuple of the result set.

For direct querying, the statement handle is closed automatically when
all the tuples in the result set have been retrieved. In order to explicitly
close a statement handle, and free all the resources associated with
the handle, a call is made to the predicate:

\begin{verbatim}
| ?- db_statement_close(QueryHandle).
\end{verbatim}

\noindent
where QueryHandle is the query handle for the statement to be closed.

The interface is also able to transparently handle Prolog terms. 
Users can both save and retrive terms without any special processing.


\section{Error Handling}
%============================

Each predicate in the XSB-DB interface throws an exception with the functor 
%%
\begin{verbatim}
   xsb_error(database(Number), Message) 
\end{verbatim}
%%
where Number is a string with the 
error number and Message is a string with a slightly detailed error message.
It is upto the user to catch this exception
and proceed with error handling. This is done by the throw-catch error
handling mechanism in XSB. For example, in order to catch the error which
will be thrown when the user attempts to close a database connection for 
a handle {\tt (ha}) which does not exist:

\begin{verbatim}
| ?- catch(db_disconnect(ha),
     xsb_error(database(Number), Message), handler(Number, Message)).
\end{verbatim}

It is the user's responsibility to define the handler predicate which can be as simple
as printing out the error number and message or may involve more 
complicated  processing.

A list of error numbers and messages that are thrown by the XSB-DB interface
is given below:

\begin{itemize}

\item {\bf XSB\_DBI\_001: XSB\_DBI ERROR: Driver already registered}\\
This error is thrown when the user tries to load a driver, using the {\tt load\_driver} 
predicate, which has already been loaded previously.

\item {\bf XSB\_DBI\_002: XSB\_DBI ERROR: Driver does not exist}\\
This error is thrown when the user tries to connect to a database, using
{\tt db\_connect}, with a driver which has not been loaded.

\item {\bf XSB\_DBI\_003: XSB\_DBI ERROR: Function does not exist in this driver}\\
This error is thrown when the user tries to use a function support 
for which does not exist in the corresponding driver. For example, 
this error is generated if the user
tries to use {\tt db\_prepare} for a connection established with the
mysql driver.

\item {\bf XSB\_DBI\_004: XSB\_DBI ERROR: No such connection handle}\\
This error is thrown when the user tries to use a connection handle
which has not been created.

\item {\bf XSB\_DBI\_005: XSB\_DBI ERROR: No such query handle}\\
This error is thrown when the user tries to use a query handle which has 
not been created.

\item {\bf XSB\_DBI\_006: XSB\_DBI ERROR: Connection handle already exists}\\
This error is thrown when the user tries to create a connection handle in
{\tt db\_connect} using a name which already exists as a connection handle.

\item {\bf XSB\_DBI\_007: XSB\_DBI ERROR: Query handle already exists}\\
This error is thrown when the user tries to create a query handle, in
{\tt db\_sql} or {\tt db\_prepare}, using a name which already exists as
a query handle for a different query.

\item {\bf XSB\_DBI\_008: XSB\_DBI ERROR: Not all parameters supplied}\\
This error is thrown when the user tries to execute a prepared statement,
using {\tt db\_prepare\_execute}, without supplying values for all the
parameters in the statement.

\item {\bf XSB\_DBI\_009: XSB\_DBI ERROR: Unbound variable in parameter list}\\
This error is thrown when the user tries to execute a prepared statement,
using {\tt db\_prepare\_execute}, without binding all the parameters of the
statement.

\end{itemize}

\section{Notes on specific drivers}
%============================

\subsubsection{ODBC Driver}

The ODBC driver has been tested in Linux using the unixodbc driver manager.
It currently supports the following functionalities: (a) connecting to a 
database using a DSN, (b) direct querying of the database, (c) using 
prepared statements to query the database, (d) closing a statement handle, and
(d) disconnecting from the database. 

\subsubsection{MySQL Driver}

The MySQL driver has been implemented using the native MySQL C API. Currently, it
has support for the following functionalities: (a) connecting to 
a database using the non-DSN {\tt db\_connect}, (b) direct querying of
the database, and (c) disconnecting from the database. Support for prepared
statement functionality will be available from MySQL 4.1 version at which
point the driver can be updated with such a feature.

%%% Local Variables: 
%%% mode: latex
%%% TeX-master: "manual2"
%%% End: 
