File: odbc.html

package info (click to toggle)
swi-prolog 5.10.1-1%2Bsqueeze1
links: PTS, VCS
area: main
in suites: squeeze
size: 76,436 kB
ctags: 45,143
sloc: ansic: 290,417; perl: 215,108; sh: 5,411; java: 5,136; makefile: 5,021; cpp: 2,168; yacc: 843; xml: 77; sed: 12
file content (1354 lines) | stat: -rw-r--r-- 56,496 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<HTML>
<HEAD>
<TITLE>SWI-Prolog ODBC Interface</TITLE><STYLE type="text/css">
/* Style sheet for SWI-Prolog latex2html
*/

dd.defbody
{ margin-bottom: 1em;
}

dt.pubdef
{ background-color: #c5e1ff;
}

.bib dd
{ margin-bottom: 1em;
}

.bib dt
{ float: left;
margin-right: 1.3ex;
}

pre.code
{ margin-left: 1.5em;
margin-right: 1.5em;
border: 1px dotted;
padding-top: 5px;
padding-left: 5px;
padding-bottom: 5px;
background-color: #f8f8f8;
}

div.navigate
{ text-align: center;
background-color: #f0f0f0;
border: 1px dotted;
padding: 5px;
}

div.title
{ text-align: center;
padding-bottom: 1em;
font-size: 200%;
font-weight: bold;
}

div.author
{ text-align: center;
font-style: italic;
}

div.abstract
{ margin-top: 2em;
background-color: #f0f0f0;
border: 1px dotted;
padding: 5px;
margin-left: 10%; margin-right:10%;
}

div.abstract-title
{ text-align: center;
padding: 5px;
font-size: 120%;
font-weight: bold;
}

div.toc-h1
{ font-size: 200%;
font-weight: bold;
}

div.toc-h2
{ font-size: 120%;
font-weight: bold;
margin-left: 2em;
}

div.toc-h3
{ font-size: 100%;
font-weight: bold;
margin-left: 4em;
}

div.toc-h4
{ font-size: 100%;
margin-left: 6em;
}

span.sec-nr
{ 
}

span.sec-title
{ 
}

span.pred-ext
{ font-weight: bold;
}

span.pred-tag
{ float: right;
font-size: 80%;
font-style: italic;
color: #202020;
}

/* Footnotes */

sup.fn { color: blue; text-decoration: underline; }
span.fn-text { display: none; }
sup.fn span {display: none;}
sup:hover span 
{ display: block !important;
position: absolute; top: auto; left: auto; width: 80%;
color: #000; background: white;
border: 2px solid;
padding: 5px; margin: 10px; z-index: 100;
font-size: smaller;
}
</STYLE>
</HEAD>
<BODY BGCOLOR="white"> 

<P>
<DIV class="title">SWI-Prolog ODBC Interface</DIV>
<DIV class="author">Jan Wielemaker <BR>
SWI, <BR>
University of Amsterdam <BR>
The Netherlands <BR>
E-mail: <A class="url" href="mailto:jan@swi-prolog.org">jan@swi-prolog.org</A></DIV>
<DIV class="abstract">
<DIV class="abstract-title">Abstract</DIV> This document describes the 
SWI-Prolog interface to ODBC, the Microsoft standard for <EM>Open 
DataBase Connectivity</EM>. These days there are ODBC managers from 
multiple vendors for many platforms as well as drivers for most 
databases, making it an attractive target for a Prolog database 
connection.

<P>The database interface is envisioned to consist of two layers. The 
first layer is an encapsulation of the core functionality of ODBC. This 
layer makes it possible to run SQL queries. The second layer exploits 
the relation between Prolog predicates and database tables, providing 
---a somewhat limited--- natural Prolog view on the data. The current 
interface only covers the first layer.
</DIV>

<H1><A NAME="document-contents">Table of Contents</A></H1>

<DIV class="toc">
<DIV class="toc-h2"><A class="sec" href="#sec:1"><SPAN class="sec-nr">1</SPAN> <SPAN class="sec-title">Introduction</SPAN></A></DIV>
<DIV class="toc-h2"><A class="sec" href="#sec:2"><SPAN class="sec-nr">2</SPAN> <SPAN class="sec-title">The 
ODBC layer</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.1"><SPAN class="sec-nr">2.1</SPAN> <SPAN class="sec-title">Connection 
management</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.2"><SPAN class="sec-nr">2.2</SPAN> <SPAN class="sec-title">Running 
SQL queries</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.2.1"><SPAN class="sec-nr">2.2.1</SPAN> <SPAN class="sec-title">One-time 
invocation</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.2.2"><SPAN class="sec-nr">2.2.2</SPAN> <SPAN class="sec-title">Parameterised 
queries</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.2.3"><SPAN class="sec-nr">2.2.3</SPAN> <SPAN class="sec-title">Fetching 
rows explicitely</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.3"><SPAN class="sec-nr">2.3</SPAN> <SPAN class="sec-title">Transaction 
management</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.4"><SPAN class="sec-nr">2.4</SPAN> <SPAN class="sec-title">Accessing 
the database dictionary</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.5"><SPAN class="sec-nr">2.5</SPAN> <SPAN class="sec-title">Getting 
more information</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.6"><SPAN class="sec-nr">2.6</SPAN> <SPAN class="sec-title">Representing 
SQL data in Prolog</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.7"><SPAN class="sec-nr">2.7</SPAN> <SPAN class="sec-title">Errors 
and warnings</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.7.1"><SPAN class="sec-nr">2.7.1</SPAN> <SPAN class="sec-title">ODBC 
messages: `Success with info'</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.7.2"><SPAN class="sec-nr">2.7.2</SPAN> <SPAN class="sec-title">ODBC 
errors</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.8"><SPAN class="sec-nr">2.8</SPAN> <SPAN class="sec-title">ODBC 
implementations</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.8.1"><SPAN class="sec-nr">2.8.1</SPAN> <SPAN class="sec-title">Using 
unixODBC</SPAN></A></DIV>
<DIV class="toc-h4"><A class="sec" href="#sec:2.8.2"><SPAN class="sec-nr">2.8.2</SPAN> <SPAN class="sec-title">Using 
Microsoft ODBC</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:2.9"><SPAN class="sec-nr">2.9</SPAN> <SPAN class="sec-title">Remaining 
issues</SPAN></A></DIV>
<DIV class="toc-h2"><A class="sec" href="#sec:3"><SPAN class="sec-nr">3</SPAN> <SPAN class="sec-title">Installation</SPAN></A></DIV>
<DIV class="toc-h3"><A class="sec" href="#sec:3.1"><SPAN class="sec-nr">3.1</SPAN> <SPAN class="sec-title">Unix 
systems</SPAN></A></DIV>
<DIV class="toc-h2"><A class="sec" href="#sec:4"><SPAN class="sec-nr">4</SPAN> <SPAN class="sec-title">Acknowledgments</SPAN></A></DIV>
</DIV>

<H2><A NAME="sec:1"><SPAN class="sec-nr">1</SPAN> <SPAN class="sec-title">Introduction</SPAN></A></H2>

<P>The value of RDMS for Prolog is often over-estimated, as Prolog 
itself can manage substantial amounts of data. Nevertheless a 
Prolog/RDMS interface provides advantages if data is already provided in 
an RDMS, data must be shared with other applications, there are strong 
persistency requirements or there is too much data to fit in memory.

<P>The popularity of ODBC makes it possible to design a single 
foreign-language module that provides RDMS access for a wide variety of 
databases on a wide variety of platforms. The SWI-Prolog RDMS interface 
is closely modeled after the ODBC API. This API is rather low-level, but 
defaults and dynamic typing provided by Prolog give the user quite 
simple access to RDMS, while the interface provides the best possible 
performance given the RDMS independency constraint.

<P>The Prolog community knows about various high-level connections 
between RDMS and Prolog. We envision these layered on top of the ODBC 
connection described here.

<H2><A NAME="sec:2"><SPAN class="sec-nr">2</SPAN> <SPAN class="sec-title">The 
ODBC layer</SPAN></A></H2>

<H3><A NAME="sec:2.1"><SPAN class="sec-nr">2.1</SPAN> <SPAN class="sec-title">Connection 
management</SPAN></A></H3>

<P>The ODBC interface deals with a single ODBC environment with multiple 
simultaneous connections. The predicates in this section deal with 
connection management.

<DL>
<DT class="pubdef"><A NAME="odbc_connect/3"><STRONG>odbc_connect</STRONG>(<VAR>+DSN, 
-Connection, +Options</VAR>)</A></DT>
<DD class="defbody">
Create a new ODBC connection to data-source <VAR>DSN</VAR> and return a 
handle to this connection in <VAR>Connection</VAR>. The connection 
handle is either an opaque structure or an atom of the <CODE>alias</CODE> 
option is used. In addition to the options below, options applicable to
<A NAME="idx:odbcsetconnection2:1"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A> 
may be provided.

<DL>
<DT><STRONG>user</STRONG>(<VAR>User</VAR>)</DT>
<DD class="defbody">
Define the user-name for the connection. This option must be present if 
the database uses authorization.</DD>
<DT><STRONG>password</STRONG>(<VAR>Password</VAR>)</DT>
<DD class="defbody">
Provide a password for the connection. Normally used in combination with <CODE>user(User)</CODE>.</DD>
<DT><STRONG>alias</STRONG>(<VAR>AliasName</VAR>)</DT>
<DD class="defbody">
Use <VAR>AliasName</VAR> as <VAR>Connection</VAR> identifier, making the 
connection available as a global resource. A good choice is to use the <VAR>DSN</VAR> 
as alias.</DD>
<DT><STRONG>open</STRONG>(<VAR>OpenMode</VAR>)</DT>
<DD class="defbody">
If <VAR>OpenMode</VAR> is <CODE>once</CODE> (default if an <CODE>alias</CODE> 
is provided), a second call to open the same <VAR>DSN</VAR> simply 
returns the existing connection. If <CODE>multiple</CODE> (default if 
there is no alias name), a second connection to the same data-source is 
opened.</DD>
<DT><STRONG>mars</STRONG>(<VAR>+Bool</VAR>)</DT>
<DD class="defbody">
If <CODE>true</CODE>, use Microsoft SQL server 2005 <EM>mars</EM> mode. 
This is support for multiple concurrent statements on a connection 
without requiring the dynamic cursor (which incurs an astounding 20-50x 
slowdown of query execution!!). MARS is a new feature in SQL2k5 
apparently, and only works if you use the native driver. For the 
non-native driver, specifying that it is enabled will have absolutely no 
effect.
</DD>
</DL>

<P>The following example connects to the WordNet<SUP class="fn">1<SPAN class="fn-text">An 
SQL version of WordNet is available from <A class="url" href="http://wordnet2sql.infocity.cjb.net/">http://wordnet2sql.infocity.cjb.net/</A></SPAN></SUP>
<CITE><A class="cite" href="#miller1990">[1]</A></CITE> database, using 
the connection alias <CODE>wordnet</CODE> and opening the connection 
only once:

<PRE class="code">
open_wordnet :-
        odbc_connect('WordNet', _,
                     [ user(jan),
                       password(xxx),
                       alias(wordnet),
                       open(once)
                     ]).
</PRE>

</DD>
<DT class="pubdef"><A NAME="odbc_driver_connect/3"><STRONG>odbc_driver_connect</STRONG>(<VAR>+DriverString, 
-Connection, +Options</VAR>)</A></DT>
<DD class="defbody">
Connects to a database using SQLDriverConnect(). This API allows for 
driver-specific additional options. DriverString is passed without 
checking. Options should <EM>not</EM> include <CODE>user</CODE> and
<CODE>password</CODE>.

<P>Whenever possible, applications should use <A NAME="idx:odbcconnect3:2"></A><A class="pred" href="#odbc_connect/3">odbc_connect/3</A>. 
If you need this predicate, please check the documentation for 
SQLDriverConnect() and the documentation of your driver.<SUP class="fn">bug<SPAN class="fn-text">Facilities 
to deal with prompted completion of the driver options are not yet 
implemented.</SPAN></SUP></DD>
<DT class="pubdef"><A NAME="odbc_disconnect/1"><STRONG>odbc_disconnect</STRONG>(<VAR>+Connection</VAR>)</A></DT>
<DD class="defbody">
Close the given <VAR>Connection</VAR>. This destroys the connection 
alias or, if there is no alias, makes further use of the <VAR>Connection</VAR> 
handle illegal.</DD>
<DT class="pubdef"><A NAME="odbc_current_connection/2"><STRONG>odbc_current_connection</STRONG>(<VAR>?Connection, 
?DSN</VAR>)</A></DT>
<DD class="defbody">
Enumerate the existing ODBC connections.</DD>
<DT class="pubdef"><A NAME="odbc_set_connection/2"><STRONG>odbc_set_connection</STRONG>(<VAR>+Connection, 
+Option</VAR>)</A></DT>
<DD class="defbody">
Set options on an existing connection. All options defined here may also 
be specified with <A NAME="idx:odbcconnect2:3"></A><SPAN class="pred-ext">odbc_connect/2</SPAN> 
in the option-list. Defined options are:

<DL>
<DT><STRONG>access_mode</STRONG>(<VAR>Mode</VAR>)</DT>
<DD class="defbody">
If <CODE>read</CODE>, tell the driver we only access the database in 
read mode. If <CODE>update</CODE> (default), tell the driver we may 
execute update commands.</DD>
<DT><STRONG>auto_commit</STRONG>(<VAR>bool</VAR>)</DT>
<DD class="defbody">
If <CODE>true</CODE> (default), each update statement is committed 
immediately. If <CODE>false</CODE>, an update statement starts a 
transaction that can be committed or rolled-back. See <A class="sec" href="#sec:2.3">section 
2.3</A> for details on transaction management.</DD>
<DT><STRONG>cursor_type</STRONG>(<VAR>CursorType</VAR>)</DT>
<DD class="defbody">
I haven't found a good description of what this does, but setting it to <CODE>dynamic</CODE> 
makes it possible to have multiple active statements on the same 
connection with Microsoft SQL server. Other values are <CODE>static</CODE>, <CODE>forwards_only</CODE> 
and <CODE>keyset_driven</CODE>.</DD>
<DT><STRONG>silent</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
If <CODE>true</CODE> (default <CODE>false</CODE>), statements returning
<CODE>SQL_SUCCESS_WITH_INFO</CODE> succeed without printing the info. 
See also <A class="sec" href="#sec:2.7.1">section 2.7.1</A>.</DD>
<DT><STRONG>null</STRONG>(<VAR>NullSpecifier</VAR>)</DT>
<DD class="defbody">
Defines how the SQL constant NULL is represented. Without specification, 
the default is the atom <CODE>$null$</CODE>. <VAR>NullSpecifier</VAR> is 
an arbitrary Prolog term, though the implementation is optimised for 
using an unbound variable, atom and functor with one unbound variable. 
The representation <CODE>null(_)</CODE> is a commonly used alternative.

<P>The specified default holds for all statements executed on this 
connection. Changing the connection default does not affect already 
prepared or running statements. The null-value can also be specified at 
the statement level. See the option list of <A NAME="idx:odbcquery4:4"></A><A class="pred" href="#odbc_query/4">odbc_query/4</A>.</DD>
<DT><STRONG>wide_column_threshold</STRONG>(<VAR>+Length</VAR>)</DT>
<DD class="defbody">
If the width of a column exceeds <VAR>Length</VAR>, use the API 
SQLGetData() to get the value incrementally rather than using a (large) 
buffer allocated with the statement. The default is to use this 
alternate interface for columns larger than 1024 bytes. There are two 
cases for using this option. In time critical applications with wide 
columns it may provide better performance at the cost of a higher memory 
usage and to work around bugs in SQLGetData(). The latter applies to 
Microsoft SQL Server fetching the definition of a view.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_get_connection/2"><STRONG>odbc_get_connection</STRONG>(<VAR>+Connection, 
?Property</VAR>)</A></DT>
<DD class="defbody">
Query for properties of the connection. <VAR>Property</VAR> is a term of 
the format <CODE><VAR>Name</VAR>(<VAR>Value</VAR>)</CODE>. If <VAR>Property</VAR> 
is unbound all defined properties are enumerated on backtracking. 
Currently the following properties are defined.

<DL>
<DT><STRONG>database_name</STRONG>(<VAR>Atom</VAR>)</DT>
<DD class="defbody">
Name of the database associated to the connection.
</DD>
<DT><STRONG>dbms_name</STRONG>(<VAR>Name</VAR>)</DT>
<DD class="defbody">
Name of the database engine. This constant can be used to identify the 
engine.
</DD>
<DT><STRONG>dbms_version</STRONG>(<VAR>Atom</VAR>)</DT>
<DD class="defbody">
Version identifier from the database engine.
</DD>
<DT><STRONG>driver_name</STRONG>(<VAR>Name</VAR>)</DT>
<DD class="defbody">
ODBC Dynamic Link Library providing the interface between ODBC and the 
database.
</DD>
<DT><STRONG>driver_odbc_version</STRONG>(<VAR>Atom</VAR>)</DT>
<DD class="defbody">
ODBC version supported by the driver.
</DD>
<DT><STRONG>driver_version</STRONG>(<VAR>Atom</VAR>)</DT>
<DD class="defbody">
The drivers version identifier.
</DD>
<DT><STRONG>active_statements</STRONG>(<VAR>Integer</VAR>)</DT>
<DD class="defbody">
Maximum number of statements that can be active at the same time on this 
connection. Returns 0 (zero) if this is unlimited.<SUP class="fn">2<SPAN class="fn-text">Microsoft 
SQL server can have multiple active statements after setting the option <CODE>cursor_type</CODE> 
to <CODE>dynamic</CODE>. See <A NAME="idx:odbcsetconnection2:5"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A>.</SPAN></SUP>
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_data_source/2"><STRONG>odbc_data_source</STRONG>(<VAR>?DSN, 
?Description</VAR>)</A></DT>
<DD class="defbody">
Query the defined data sources. It is not required to have any open 
connections before calling this predicate. <VAR>DSN</VAR> is the name of 
the data source as required by <A NAME="idx:odbcconnect3:6"></A><A class="pred" href="#odbc_connect/3">odbc_connect/3</A>. <VAR>Description</VAR> 
is the name of the driver. The driver name may be used to tailor the SQL 
statements used on the database. Unfortunately this name depends on the 
local installing details and is therefore not universally useful.
</DD>
</DL>

<H3><A NAME="sec:2.2"><SPAN class="sec-nr">2.2</SPAN> <SPAN class="sec-title">Running 
SQL queries</SPAN></A></H3>

<P>ODBC distinguishes between direct execution of literal SQL strings 
and parameterized execution of SQL strings. The first is a simple 
practical solution for infrequent calls (such as creating a table), 
while parameterized execution allows the driver and database to 
precompile the query and store the optimized code, making it suitable 
for time-critical operations. In addition, it allows for passing 
parameters without going through SQL-syntax and thus avoiding the need 
for quoting.

<H4><A NAME="sec:2.2.1"><SPAN class="sec-nr">2.2.1</SPAN> <SPAN class="sec-title">One-time 
invocation</SPAN></A></H4>

<DL>
<DT class="pubdef"><A NAME="odbc_query/3"><STRONG>odbc_query</STRONG>(<VAR>+Connection, 
+SQL, -RowOrAffected</VAR>)</A></DT>
<DD class="defbody">
Same as <A NAME="idx:odbcquery4:7"></A><A class="pred" href="#odbc_query/4">odbc_query/4</A> 
using <CODE>[]</CODE> for <VAR>Options</VAR>.</DD>
<DT class="pubdef"><A NAME="odbc_query/4"><STRONG>odbc_query</STRONG>(<VAR>+Connection, 
+SQL, -RowOrAffected, +Options</VAR>)</A></DT>
<DD class="defbody">
Fire an SQL query on the database represented by <VAR>Connection</VAR>.
<VAR>SQL</VAR> is any valid SQL statement. SQL statements can be 
specified as a plain atom, string or a term of the format
<VAR>Format</VAR>-<VAR>Arguments</VAR>, which is converted using <A NAME="idx:format2:8"></A><SPAN class="pred-ext">format/2</SPAN>.

<P>If the statement is a <CODE>SELECT</CODE> statement the result-set is 
returned in <VAR>RowOrAffected</VAR>. By default rows are returned 
one-by-one on backtracking as terms of the functor row/\arg{Arity} , 
where <VAR>Arity</VAR> denotes the number of columns in the result-set. 
The library pre-fetches the next value to be able to close the statement 
and return deterministic success when returning the last row of the 
result-set. Using the option findall/2 (see below) the result-set is 
returned as a list of user-specified terms. For other statements this 
argument returns <CODE>affected(Rows)</CODE>, where <VAR>Rows</VAR> 
represents the number of rows affected by the statement. If you are not 
interested in the number of affected rows <A NAME="idx:odbcquery2:9"></A><A class="pred" href="#odbc_query/2">odbc_query/2</A> 
provides a simple interface for sending SQL-statements.

<P>Below is a small example using the connection created from
<A NAME="idx:odbcconnect3:10"></A><A class="pred" href="#odbc_connect/3">odbc_connect/3</A>. 
Please note that the SQL-statement does not end in the `<CODE>;</CODE>' 
character.

<PRE class="code">
lemma(Lemma) :-
        odbc_query(wordnet,
                   'SELECT (lemma) FROM word',
                   row(Lemma).
</PRE>

<P>The following example adds a name to a table with parent-relations, 
returning the number of rows affected by the statement.

<PRE class="code">
insert_child(Child, Mother, Father, Affected) :-
        odbc_query(parents,
                   'INSERT INTO parents (name,mother,father) \
                      VALUES ("mary", "christine", "bob")',
                   affected(Affected)).
</PRE>

<P><VAR>Options</VAR> defines the following options.

<DL>
<DT><STRONG>types</STRONG>(<VAR>ListOfTypes</VAR>)</DT>
<DD class="defbody">
Determine the Prolog type used to report the column-values. When 
omitted, default conversion as described in <A class="sec" href="#sec:2.6">section 
2.6</A> is implied. A column may specify <CODE>default</CODE> to use 
default conversion for that column. The length of the type-list must 
match the number of columns in the result-set.

<P>For example, in the table <CODE>word</CODE> the first column is 
defined with the SQL type <CODE>DECIMAL(6)</CODE>. Using this SQL-type, 
``001'' is distinct from ``1'', but using Prolog integers is a valid 
representation for Wordnet <CODE>wordno</CODE> identifiers. The 
following query extracts rows using Prolog integers:

<PRE class="code">
?- odbc_query(wordnet,
              'select * from word', X,
              [ types([integer,default])
              ]).

X = row(1, entity) ;
X = row(2, thing) ;
...
</PRE>

<P>See also <A class="sec" href="#sec:2.6">section 2.6</A> for notes on 
type-conversion.</DD>
<DT><STRONG>null</STRONG>(<VAR>NullSpecifier</VAR>)</DT>
<DD class="defbody">
Specify SQL NULL representation. See <A NAME="idx:odbcsetconnection2:11"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A> 
for details.</DD>
<DT><STRONG>source</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
If <CODE>true</CODE> (default <CODE>false</CODE>), include the 
source-column with each result-value. With this option, each result in 
the row/\arg{N} -term is of the format below. <VAR>TableName</VAR> or
<VAR>ColumnName</VAR> may be the empty atom if the information is not 
available.<SUP class="fn">3<SPAN class="fn-text">This is one possible 
interface to this information. In many cases it is more efficient and 
convenient to provide this information separately as it is the same for 
each result-row.</SPAN></SUP>
<BLOCKQUOTE>
<CODE>column(TableName, ColumnName, Value)</CODE>
</BLOCKQUOTE>
</DD>
<DT><STRONG>findall</STRONG>(<VAR>Template, row(Column, ... )</VAR>)</DT>
<DD class="defbody">
Instead of returning rows on backtracking this option makes <A NAME="idx:odbcquery3:12"></A><A class="pred" href="#odbc_query/3">odbc_query/3</A> 
return all rows in a list and close the statement. The option is named 
after the Prolog <A NAME="idx:findall3:13"></A><SPAN class="pred-ext">findall/3</SPAN> 
predicate, as the it makes <A NAME="idx:odbcquery3:14"></A><A class="pred" href="#odbc_query/3">odbc_query/3</A> 
behave as the commonly used <A NAME="idx:findall3:15"></A><SPAN class="pred-ext">findall/3</SPAN> 
construct below.

<PRE class="code">
lemmas(Lemmas) :-
        findall(Lemma,
                odbc_query(wordnet,
                           'select (lemma) from word',
                           row(Lemma)),
                Lemmas).
</PRE>

<P>Using the findall/2 option the above can be implemented as below. The 
number of argument of the <CODE>row</CODE> term must match the number of 
columns in the result-set.

<PRE class="code">
lemmas(Lemmas) :-
        odbc_query(wordnet,
                   'select (lemma) from word',
                   Lemmas,
                   [ findall(Lemma, row(Lemma))
                   ]).
</PRE>

<BLOCKQUOTE><I>The current implementation is incomplete. It does not 
allow arguments of
<CODE>row(...)</CODE> to be instantiated. Plain instantiation can always 
be avoided using a proper SELECT statement. Potentially useful however 
would be the translation of compound terms, especially to translate 
date/time/timestamp structures to a format for use by the application.
</I></BLOCKQUOTE>
</DD>
<DT><STRONG>wide_column_threshold</STRONG>(<VAR>+Length</VAR>)</DT>
<DD class="defbody">
Specify threshold column width for using SQLGetData(). See <A NAME="idx:odbcsetconnection2:16"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A> 
for details.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_query/2"><STRONG>odbc_query</STRONG>(<VAR>+Connection, 
+SQL</VAR>)</A></DT>
<DD class="defbody">
As <A NAME="idx:odbcquery3:17"></A><A class="pred" href="#odbc_query/3">odbc_query/3</A>, 
but used for SQL-statements that should not return result-rows (i.e. all 
statements except for <CODE>SELECT</CODE>). The predicate prints a 
diagnostic message if the query returns a result.
</DD>
</DL>

<H4><A NAME="sec:2.2.2"><SPAN class="sec-nr">2.2.2</SPAN> <SPAN class="sec-title">Parameterised 
queries</SPAN></A></H4>

<P>ODBC provides for `parameterized queries'. These are SQL queries with 
a <CODE>?</CODE>-sign at places where parameters appear. The ODBC 
interface and database driver may use this to precompile the 
SQL-statement, giving better performance on repeated queries. This is 
exactly what we want if we associate Prolog predicates to database 
tables. This interface is defined by the following predicates:

<DL>
<DT class="pubdef"><A NAME="odbc_prepare/4"><STRONG>odbc_prepare</STRONG>(<VAR>+Connection, 
+SQL, +Parameters, -Statement</VAR>)</A></DT>
<DD class="defbody">
As <A NAME="idx:odbcprepare5:18"></A><A class="pred" href="#odbc_prepare/5">odbc_prepare/5</A> 
using <CODE>[]</CODE> for <VAR>Options</VAR>.</DD>
<DT class="pubdef"><A NAME="odbc_prepare/5"><STRONG>odbc_prepare</STRONG>(<VAR>+Connection, 
+SQL, +Parameters, -Statement, +Options</VAR>)</A></DT>
<DD class="defbody">
Create a statement from the given <VAR>SQL</VAR> (which may be a format 
specification as described with <A NAME="idx:odbcquery3:19"></A><A class="pred" href="#odbc_query/3">odbc_query/3</A>) 
statement that normally has one or more parameter-indicators (<CODE>?</CODE>) 
and unify <VAR>Statement</VAR> with a handle to the created statement. <VAR>Parameters</VAR> 
is a list of descriptions, one for each parameter. Each parameter 
description is one of the following:

<DL>
<DT><STRONG>default</STRONG></DT>
<DD class="defbody">
Uses the ODBC function SQLDescribeParam() to obtain information about 
the parameter and apply default rules. See <A class="sec" href="#sec:2.6">section 
2.6</A> for details. If the interface fails to return a type or the type 
is unknown to the ODBC interface a message is printed and the interface 
handles the type as text, which implies the user must supply an atom. 
The message can be suppressed using the <CODE>silent(true)</CODE> option 
of <A NAME="idx:odbcsetconnection2:20"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A>. 
An alternative mapping can be selected using the <VAR>&gt;</VAR> option 
of this predicate described below.</DD>
<DT><STRONG><VAR>SqlType</VAR></STRONG>(<VAR>Specifier, ...</VAR>)</DT>
<DD class="defbody">
Declare the parameter to be of type <VAR>SqlType</VAR> with the given 
specifiers. Specifiers are required for <CODE>char</CODE>, <CODE>varchar</CODE>, 
etc. to specify the field-width. When calling <A NAME="idx:odbcexecute23:21"></A><A class="pred" href="#odbc_execute/2">odbc_execute/[2-3]</A>, 
the user must supply the parameter values in the default Prolog type for 
this SQL type. See <A class="sec" href="#sec:2.6">section 2.6</A> for 
details.</DD>
<DT><B><VAR>PrologType</VAR> <VAR>&gt;</VAR> <VAR>SqlType</VAR></B></DT>
<DD class="defbody">
As above, but supply values of the given <VAR>PrologType</VAR>, using 
the type-transformation defined by the database driver. For example, if 
the parameter is specified as

<PRE class="code">
atom &gt; date
</PRE>

<P>The use must supply an atom of the format <CODE>YYYY-MM-DD</CODE> 
rather than a term <CODE>date(Year,Month,Day)</CODE>. This construct 
enhances flexibility and allows for passing values that have no proper 
representation in Prolog.
</DD>
</DL>

<P><VAR>Options</VAR> defines a list of options for executing the 
statement. See
<A NAME="idx:odbcquery4:22"></A><A class="pred" href="#odbc_query/4">odbc_query/4</A> 
for details. In addition, the following option is provided:

<DL>
<DT><STRONG>fetch</STRONG>(<VAR>FetchType</VAR>)</DT>
<DD class="defbody">
Determine the <VAR>FetchType</VAR>, which is one of <CODE>auto</CODE> 
(default) to extract the result-set on backtracking or <CODE>fetch</CODE> 
to prepare the result-set to be fetched using <A NAME="idx:odbcfetch3:23"></A><A class="pred" href="#odbc_fetch/3">odbc_fetch/3</A>.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_execute/3"><STRONG>odbc_execute</STRONG>(<VAR>+Statement, 
+ParameterValues, -RowOrAffected</VAR>)</A></DT>
<DD class="defbody">
Execute a statement prepared with <A NAME="idx:odbcprepare4:24"></A><A class="pred" href="#odbc_prepare/4">odbc_prepare/4</A> 
with the given
<VAR>ParameterValues</VAR> and return the rows or number of affected 
rows as <A NAME="idx:odbcquery4:25"></A><A class="pred" href="#odbc_query/4">odbc_query/4</A>. 
This predicate may return type_error exceptions if the provided 
parameter values cannot be converted to the declared types.

<P>ODBC doesn't appear to allow for multiple cursors on the same 
result-set.<SUP class="fn">4<SPAN class="fn-text">Is this right?</SPAN></SUP> 
This would imply there can only be one active <A NAME="idx:odbcexecute3:26"></A><A class="pred" href="#odbc_execute/3">odbc_execute/3</A> 
(i.e. with a choice-point) on a prepared statement. Suppose we have a 
table <CODE>age (name char(25), age integer)</CODE> bound to the 
predicate <SPAN class="pred-ext">age/2</SPAN> we cannot write the code 
below without special precautions. The ODBC interface therefore creates 
a clone of a statement if it discovers the statement is being executed, 
which is discarded after the statement is finished.<SUP class="fn">5<SPAN class="fn-text">The 
code is prepared to maintain a cache of statements. Practice should tell 
us whether it is worthwhile activating this.</SPAN></SUP>

<PRE class="code">
same_age(X, Y) :-
        age(X, AgeX),
        age(Y, AgeY),
        AgeX = AgeY.
</PRE>

</DD>
<DT class="pubdef"><A NAME="odbc_execute/2"><STRONG>odbc_execute</STRONG>(<VAR>+Statement, 
+ParameterValues</VAR>)</A></DT>
<DD class="defbody">
Like <A NAME="idx:odbcquery2:27"></A><A class="pred" href="#odbc_query/2">odbc_query/2</A>, 
this predicate is meant to execute simple SQL statements without 
interest in the result.</DD>
<DT class="pubdef"><A NAME="odbc_free_statement/1"><STRONG>odbc_free_statement</STRONG>(<VAR>+Statement</VAR>)</A></DT>
<DD class="defbody">
Destroy a statement prepared with <A NAME="idx:odbcprepare4:28"></A><A class="pred" href="#odbc_prepare/4">odbc_prepare/4</A>. 
If the statement is currently executing (i.e. <A NAME="idx:odbcexecute3:29"></A><A class="pred" href="#odbc_execute/3">odbc_execute/3</A> 
left a choice-point), the destruction is delayed until the execution 
terminates.
</DD>
</DL>

<H4><A NAME="sec:2.2.3"><SPAN class="sec-nr">2.2.3</SPAN> <SPAN class="sec-title">Fetching 
rows explicitely</SPAN></A></H4>

<A NAME="sec:sqlfetch"></A>

<P>Normally SQL queries return a result-set that is enumerated on 
backtracking. Using this approach a result-set is similar to a predicate 
holding facts. There are some cases where fetching the rows one-by-one, 
much like <A NAME="idx:read1:30"></A><SPAN class="pred-ext">read/1</SPAN> 
reads terms from a file is more appropriate and there are cases where 
only part of the result-set is to be fetched. These cases can be dealt 
with using <A NAME="idx:odbcfetch3:31"></A><A class="pred" href="#odbc_fetch/3">odbc_fetch/3</A>, 
which provides an interface to SQLFetchScroll().

<P>As a general rule of thumb, stay away from these functions if you do 
not really need them. Experiment before deciding on the strategy and 
often you'll discover the simply backtracking approach is much easier to 
deal with and about as fast.

<DL>
<DT class="pubdef"><A NAME="odbc_fetch/3"><STRONG>odbc_fetch</STRONG>(<VAR>+Statement, 
-Row, +Option</VAR>)</A></DT>
<DD class="defbody">
Fetch a row from the result-set of <VAR>Statement</VAR>. <VAR>Statement</VAR> 
must be created with <A NAME="idx:odbcprepare5:32"></A><A class="pred" href="#odbc_prepare/5">odbc_prepare/5</A> 
using the option <CODE>fetch(fetch)</CODE> and be executed using <A NAME="idx:odbcexecute2:33"></A><A class="pred" href="#odbc_execute/2">odbc_execute/2</A>. <VAR>Row</VAR> 
is unified to the fetched row or the atom <CODE>end_of_file</CODE><SUP class="fn">6<SPAN class="fn-text">This 
atom was selected to emphasise the similarity to read.</SPAN></SUP> 
after the end of the data is reached. Calling <A NAME="idx:odbcfetch2:34"></A><SPAN class="pred-ext">odbc_fetch/2</SPAN> 
after all data is retrieved causes a permission-error exception. <VAR>Option</VAR> 
is one of:

<DL>
<DT><STRONG>next</STRONG></DT>
<DD class="defbody">
Fetch the next row.
</DD>
<DT><STRONG>prior</STRONG></DT>
<DD class="defbody">
Fetch the result-set going backwards.
</DD>
<DT><STRONG>first</STRONG></DT>
<DD class="defbody">
Fetch the first row.
</DD>
<DT><STRONG>last</STRONG></DT>
<DD class="defbody">
Fetch the last row.
</DD>
<DT><STRONG>absolute</STRONG>(<VAR>Offset</VAR>)</DT>
<DD class="defbody">
Fetch absolute numbered row. Rows count from one.
</DD>
<DT><STRONG>relative</STRONG>(<VAR>Offset</VAR>)</DT>
<DD class="defbody">
Fetch relative to the current row. <CODE>relative(1)</CODE> is the same 
as <CODE>next()</CODE>, except that the first row extracted is row 2.
</DD>
<DT><STRONG>bookmark</STRONG>(<VAR>Offset</VAR>)</DT>
<DD class="defbody">
Reserved. Bookmarks are not yet supported in this interface.
</DD>
</DL>

<P>In many cases, depending on the driver and RDBMS, the cursor-type 
must be changed using <A NAME="idx:odbcsetconnection2:35"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A> 
for anything different from <CODE>next()</CODE> to work.

<P>Here is example code each time skipping a row from a table `test' 
holding a single column of integers that represent the row-number. This 
test was executed using unixODBC and MySQL on SuSE Linux.

<PRE class="code">
fetch(Options) :-
        odbc_set_connection(test, cursor_type(static)),
        odbc_prepare(test,
                     'select (testval) from test',
                     [],
                     Statement,
                     [ fetch(fetch)
                     ]),
        odbc_execute(Statement, []),
        fetch(Statement, Options).

fetch(Statement, Options) :-
        odbc_fetch(Statement, Row, Options),
        (   Row == end_of_file
        -&gt;  true
        ;   writeln(Row),
            fetch(Statement, Options)
        ).
</PRE>

</DD>
<DT class="pubdef"><A NAME="odbc_close_statement/+Statement"><STRONG>odbc_close_statement</STRONG>(<VAR>+Statement</VAR>)</A></DT>
<DD class="defbody">
loses the given statement (without freeing it). This must be used if not 
the whole result-set is retrieved using <A NAME="idx:odbcfetch3:36"></A><A class="pred" href="#odbc_fetch/3">odbc_fetch/3</A>.
</DD>
</DL>

<H3><A NAME="sec:2.3"><SPAN class="sec-nr">2.3</SPAN> <SPAN class="sec-title">Transaction 
management</SPAN></A></H3>

<A NAME="sec:sqltrans"></A>

<P>ODBC can run in two modi. By default, all update actions are 
immediately committed on the server. Using <A NAME="idx:odbcsetconnection2:37"></A><A class="pred" href="#odbc_set_connection/2">odbc_set_connection/2</A> 
this behaviour can be switched off, after which each SQL statement that 
can be inside a transaction implicitly starts a new transaction. This 
transaction can be ended using <A NAME="idx:odbcendtransaction2:38"></A><A class="pred" href="#odbc_end_transaction/2">odbc_end_transaction/2</A>.

<DL>
<DT class="pubdef"><A NAME="odbc_end_transaction/2"><STRONG>odbc_end_transaction</STRONG>(<VAR>+Connection, 
+Action</VAR>)</A></DT>
<DD class="defbody">
End the currently open transaction if there is one. Using <VAR>Action</VAR>
<CODE>commit</CODE> pending updates are made permanent, using
<CODE>rollback</CODE> they are discarded.
</DD>
</DL>

<P>The ODBC documentation has many comments on transaction management 
and its interaction with database cursors.

<H3><A NAME="sec:2.4"><SPAN class="sec-nr">2.4</SPAN> <SPAN class="sec-title">Accessing 
the database dictionary</SPAN></A></H3>

<P>With this interface we do not envision the use of Prolog as a 
database manager. Nevertheless, elementary access to the structure of a 
database is required, for example to validate a database satisfies the 
assumptions made by the application.

<DL>
<DT class="pubdef"><A NAME="odbc_current_table/2"><STRONG>odbc_current_table</STRONG>(<VAR>+Connection, 
-Table</VAR>)</A></DT>
<DD class="defbody">
Return on backtracking the names of all tables in the database 
identified by the connection.</DD>
<DT class="pubdef"><A NAME="odbc_current_table/3"><STRONG>odbc_current_table</STRONG>(<VAR>+Connection, 
?Table, ?Facet</VAR>)</A></DT>
<DD class="defbody">
Enumerate properties of the tables. Defines facets are:

<DL>
<DT><STRONG>qualifier</STRONG>(<VAR>Qualifier</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>owner</STRONG>(<VAR>Owner</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>comment</STRONG>(<VAR>Comment</VAR>)</DT>
<DD class="defbody">
These facets are defined by SQLTables()
</DD>
<DT><STRONG>arity</STRONG>(<VAR>Arity</VAR>)</DT>
<DD class="defbody">
This facet returns the number of columns in a table.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_table_column/3"><STRONG>odbc_table_column</STRONG>(<VAR>+Connection, 
?Table, ?Column</VAR>)</A></DT>
<DD class="defbody">
On backtracking, enumerate all columns in all tables.</DD>
<DT class="pubdef"><A NAME="odbc_table_column/3"><STRONG>odbc_table_column</STRONG>(<VAR>+Connection, 
?Table, ?Column, ?Facet</VAR>)</A></DT>
<DD class="defbody">
Provides access to the properties of the table as defined by the ODBC 
call SQLColumns(). Defined facets are:

<DL>
<DT><STRONG>table_qualifier</STRONG>(<VAR>Qualifier</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>table_owner</STRONG>(<VAR>Owner</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>table_name</STRONG>(<VAR>Table</VAR>)</DT>
<DD class="defbody">
See <A NAME="idx:odbccurrenttable3:39"></A><A class="pred" href="#odbc_current_table/3">odbc_current_table/3</A>.
</DD>
<DT><STRONG>data_type</STRONG>(<VAR>DataType</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>type_name</STRONG>(<VAR>TypeName</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>precision</STRONG>(<VAR>Precision</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>length</STRONG>(<VAR>Length</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>scale</STRONG>(<VAR>Scale</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>radix</STRONG>(<VAR>Radix</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>nullable</STRONG>(<VAR>Nullable</VAR>)</DT>
<DD class="defbody">
</DD>
<DT><STRONG>remarks</STRONG>(<VAR>Remarks</VAR>)</DT>
<DD class="defbody">
These facets are defined by SQLColumns()
</DD>
<DT><STRONG>type</STRONG>(<VAR>Type</VAR>)</DT>
<DD class="defbody">
More prolog-friendly representation of the type properties. See
<A class="sec" href="#sec:2.6">section 2.6</A>.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_type/3"><STRONG>odbc_type</STRONG>(<VAR>+Connection, 
?TypeSpec, ?Facet</VAR>)</A></DT>
<DD class="defbody">
Query the types supported by the data source. <VAR>TypeSpec</VAR> is 
either an integer type-id, the name of an ODBC SQL type or the constant
<CODE>all_types</CODE> to enumerate all known types. This predicate 
calls SQLGetTypeInfo() and its facet names are derived from the 
specification of this ODBC function:

<DL>
<DT><STRONG>name</STRONG>(<VAR>Name</VAR>)</DT>
<DD class="defbody">
Name used by the data-source. Use this in CREATE statements
</DD>
<DT><STRONG>data_type</STRONG>(<VAR>DataType</VAR>)</DT>
<DD class="defbody">
Numeric identifier of the type
</DD>
<DT><STRONG>precision</STRONG>(<VAR>Precision</VAR>)</DT>
<DD class="defbody">
When available, maximum precision of the type.
</DD>
<DT><STRONG>literal_prefix</STRONG>(<VAR>Prefix</VAR>)</DT>
<DD class="defbody">
When available, prefix for literal representation.
</DD>
<DT><STRONG>literal_suffix</STRONG>(<VAR>Suffix</VAR>)</DT>
<DD class="defbody">
When available, suffix for literal representation.
</DD>
<DT><STRONG>create_params</STRONG>(<VAR>CreateParams</VAR>)</DT>
<DD class="defbody">
When available, arguments needed to create the type.
</DD>
<DT><STRONG>nullable</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
Whether the type can be <CODE>NULL</CODE>. May be <CODE>unknown</CODE>
</DD>
<DT><STRONG>case_sensitive</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
Whether values for this type are case-sensitive.
</DD>
<DT><STRONG>searchable</STRONG>(<VAR>Searchable</VAR>)</DT>
<DD class="defbody">
Whether the type can be searched. Values are <CODE>false</CODE>,
<CODE>true</CODE>, <CODE>like_only</CODE> or <CODE>all_except_like</CODE>.
</DD>
<DT><STRONG>unsigned</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
When available, whether the value is signed. Please note that SWI-Prolog 
does not provide unsigned integral values.
</DD>
<DT><STRONG>money</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
Whether the type represents money.
</DD>
<DT><STRONG>auto_increment</STRONG>(<VAR>Bool</VAR>)</DT>
<DD class="defbody">
When available, whether the type can be auto-incremented.
</DD>
<DT><STRONG>local_name</STRONG>(<VAR>LocalName</VAR>)</DT>
<DD class="defbody">
Name of the type in local language.
</DD>
<DT><STRONG>minimum_scale</STRONG>(<VAR>MinScale</VAR>)</DT>
<DD class="defbody">
Minimum scale of the type.
</DD>
<DT><STRONG>maximum_scale</STRONG>(<VAR>MaxScale</VAR>)</DT>
<DD class="defbody">
Maximum scale of the type.
</DD>
</DL>

</DD>
</DL>

<H3><A NAME="sec:2.5"><SPAN class="sec-nr">2.5</SPAN> <SPAN class="sec-title">Getting 
more information</SPAN></A></H3>

<A NAME="sec:odbcinfo"></A>

<DL>
<DT class="pubdef"><A NAME="odbc_statistics/1"><STRONG>odbc_statistics</STRONG>(<VAR>?Key</VAR>)</A></DT>
<DD class="defbody">
Get statistical data on the ODBC interface. Currently defined keys are:

<DL>
<DT><STRONG>statements</STRONG>(<VAR>Created, Freed</VAR>)</DT>
<DD class="defbody">
Number of SQL statements that have been <VAR>Created</VAR> and <VAR>Freed</VAR> 
over all connections. Statements executed with <A NAME="idx:odbcquery23:40"></A><A class="pred" href="#odbc_query/2">odbc_query/[2-3]</A> 
increment <VAR>Created</VAR> as the query is created and <VAR>Freed</VAR> 
if the query is terminated due to deterministic success, failure, cut or 
exception. Statements created with <A NAME="idx:odbcprepare45:41"></A><A class="pred" href="#odbc_prepare/4">odbc_prepare/[4-5]</A> 
are freed by <A NAME="idx:odbcfreestatement1:42"></A><A class="pred" href="#odbc_free_statement/1">odbc_free_statement/1</A> 
or due to a fatal error with the statement.
</DD>
</DL>

</DD>
<DT class="pubdef"><A NAME="odbc_debug/1"><STRONG>odbc_debug</STRONG>(<VAR>+Level</VAR>)</A></DT>
<DD class="defbody">
Set the verbosity-level to <VAR>Level</VAR>. Default is 0. Higher levels 
make the system print debugging messages.
</DD>
</DL>

<H3><A NAME="sec:2.6"><SPAN class="sec-nr">2.6</SPAN> <SPAN class="sec-title">Representing 
SQL data in Prolog</SPAN></A></H3>

<A NAME="sec:sqltypes"></A>

<P>Databases have a poorly standardized but rich set of datatypes. Some 
have natural Prolog counterparts, some not. A complete mapping requires 
us to define Prolog data-types for SQL types that have no standardized 
Prolog counterpart (such as timestamp), the definition of a default 
mapping and the possibility to define an alternative mapping for a 
specific column. For example, many variations of the SQL <CODE>DECIMAL</CODE> 
type cannot be mapped to a Prolog integer. Nevertheless, mapping to an 
integer may be the proper choice for a specific application.

<P>The Prolog/ODBC interface defines the following Prolog result types 
with the indicated default transformation. Different result-types can be 
requested using the <CODE>types(TypeList)</CODE> option for the
<A NAME="idx:odbcquery4:43"></A><A class="pred" href="#odbc_query/4">odbc_query/4</A> 
and <A NAME="idx:odbcprepare5:44"></A><A class="pred" href="#odbc_prepare/5">odbc_prepare/5</A> 
interfaces.

<DL>
<DT><STRONG>atom</STRONG></DT>
<DD class="defbody">
Used as default for the SQL types <CODE>char</CODE>, <CODE>varchar</CODE>,
<CODE>longvarchar</CODE>, <CODE>binary</CODE>, <CODE>varbinary</CODE>,
<CODE>longvarbinary</CODE>, <CODE>decimal</CODE> and <CODE>numeric</CODE>. 
Can be used for all types.</DD>
<DT><STRONG>string</STRONG></DT>
<DD class="defbody">
SWI-Prolog extended type string. Use the type for special cases where 
garbage atoms must be avoided. Can be used for all types.</DD>
<DT><STRONG>codes</STRONG></DT>
<DD class="defbody">
List of character codes. Use this type if the argument must be analysed 
or compatibility with Prolog systems that cannot handle infinite-length 
atoms is desired. Can be used for all types.</DD>
<DT><STRONG>integer</STRONG></DT>
<DD class="defbody">
Used as default for the SQL types <CODE>bit</CODE>, <CODE>tinyint</CODE>,
<CODE>smallint</CODE> and <CODE>integer</CODE>. Please note that 
SWI-Prolog integers are signed 32-bit values, where SQL allows for 
unsigned values as well. Can be used for the integral, and <CODE>decimal</CODE> 
types as well as the types <CODE>date</CODE> and <CODE>timestamp</CODE>, 
which are represented as POSIX time-stamps (seconds after Jan 1, 1970).</DD>
<DT><STRONG>double</STRONG></DT>
<DD class="defbody">
Used as default for the SQL types <CODE>real</CODE>, <CODE>float</CODE> 
and
<CODE>double</CODE>. Can be used for the integral, float and <CODE>decimal</CODE> 
types as well as the types <CODE>date</CODE> and <CODE>timestamp</CODE>, 
which are represented as POSIX time-stamps (seconds after Jan 1, 1970). 
Representing time this way is compatible to SWI-Prologs time-stamp 
handling.</DD>
<DT><STRONG>date</STRONG></DT>
<DD class="defbody">
A Prolog term of the form <CODE>date(Year,Month,Day)</CODE> used as 
default for the SQL type <CODE>date</CODE>.</DD>
<DT><STRONG>time</STRONG></DT>
<DD class="defbody">
A Prolog term of the form <CODE>time(Hour,Minute,Second)</CODE> used as 
default for the SQL type <CODE>time</CODE>.</DD>
<DT><STRONG>timestamp</STRONG></DT>
<DD class="defbody">
A Prolog term of the form
<CODE>timestamp(Year,Month,Day,Hour,Minute,Second,Fraction)</CODE> used 
as default for the SQL type <CODE>timestamp</CODE>.
</DD>
</DL>

<H3><A NAME="sec:2.7"><SPAN class="sec-nr">2.7</SPAN> <SPAN class="sec-title">Errors 
and warnings</SPAN></A></H3>

<P>ODBC operations return success, error or `success with info'. This 
section explains how results from the ODBC layer are reported to Prolog.

<H4><A NAME="sec:2.7.1"><SPAN class="sec-nr">2.7.1</SPAN> <SPAN class="sec-title">ODBC 
messages: `Success with info'</SPAN></A></H4>

<A NAME="sec:successwithinfo"></A>

<P>If an ODBC operation returns `with info', the info is extracted from 
the interface and handled to the Prolog message dispatcher <A NAME="idx:printmessage2:45"></A><SPAN class="pred-ext">print_message/2</SPAN>. 
The level of the message is <CODE>informational</CODE> and the term is 
of the form:

<DL>
<DT><STRONG>odbc</STRONG>(<VAR>State, Native, Message</VAR>)</DT>
<DD class="defbody">
Here, <VAR>State</VAR> is the SQL-state as defined in the ODBC API,
<VAR>Native</VAR> is the (integer) error code of the underlying data 
source and <VAR>Message</VAR> is a human readable explanation of the 
message.
</DD>
</DL>

<H4><A NAME="sec:2.7.2"><SPAN class="sec-nr">2.7.2</SPAN> <SPAN class="sec-title">ODBC 
errors</SPAN></A></H4>

<A NAME="sec:sqlerror"></A>

<P>If an ODBC operation signals an error, it throws the exception
<CODE>error(<CODE>odbc(State, Native, Message)</CODE>, _)</CODE>. The 
arguments of the odbc/3 term are explained in <A class="sec" href="#sec:2.7.1">section 
2.7.1</A>.

<P>In addition, the Prolog layer performs the normal tests for proper 
arguments and state, signaling the conventional instantiation, type, 
domain and resource exceptions.

<H3><A NAME="sec:2.8"><SPAN class="sec-nr">2.8</SPAN> <SPAN class="sec-title">ODBC 
implementations</SPAN></A></H3>

<P>There is a wealth on ODBC implementations that are completely or 
almost compatible to this interface. In addition, a number of databases 
are delivered with an ODBC compatible interface. This implies you get 
the portability benefits of ODBC without paying the configuration and 
performance price. Currently this interface is, according to the
<A class="url" href="http://www.php.net">PHP</A> documentation on this 
subject, provided by Adabas D, IBM DB2, Solid, and Sybase SQL Anywhere.

<H4><A NAME="sec:2.8.1"><SPAN class="sec-nr">2.8.1</SPAN> <SPAN class="sec-title">Using 
unixODBC</SPAN></A></H4>

<P>The SWI-Prolog ODBC interface was developed using
<A class="url" href="http://www.unixodbc.org">unixODBC</A> and <A class="url" href="http://www.mysql.com">MySQL</A> 
on
<A class="url" href="http://www.suse.com">SuSE Linux</A>.

<H4><A NAME="sec:2.8.2"><SPAN class="sec-nr">2.8.2</SPAN> <SPAN class="sec-title">Using 
Microsoft ODBC</SPAN></A></H4>

<P>On MS-Windows, the ODBC interface is a standard package, linked 
against
<CODE>odbc32.lib</CODE>.

<H3><A NAME="sec:2.9"><SPAN class="sec-nr">2.9</SPAN> <SPAN class="sec-title">Remaining 
issues</SPAN></A></H3>

<P>The following issues are identified and waiting for concrete problems 
and suggestions.

<DL>
<DT><B>Transaction management</B></DT>
<DD>
This certainly requires a high-level interface. Possibly in combination 
with <A NAME="idx:callcleanup3:46"></A><SPAN class="pred-ext">call_cleanup/3</SPAN>, 
providing automatic rollback on failure or exception and commit on 
success.</DD>
<DT><B>High-level interface</B></DT>
<DD>
Attaching tables to predicates, partial <EM>DataLog</EM> implementation, 
etc.
</DD>
</DL>

<H2><A NAME="sec:3"><SPAN class="sec-nr">3</SPAN> <SPAN class="sec-title">Installation</SPAN></A></H2>

<H3><A NAME="sec:3.1"><SPAN class="sec-nr">3.1</SPAN> <SPAN class="sec-title">Unix 
systems</SPAN></A></H3>

<P>Installation on Unix system uses the commonly found <EM>configure</EM>,
<EM>make</EM> and <EM>make install</EM> sequence. SWI-Prolog should be 
installed before building this package. If SWI-Prolog is not installed 
as <B>pl</B>, the environment variable <CODE>PL</CODE> must be set to 
the name of the SWI-Prolog executable. Installation is now accomplished 
using:

<PRE class="code">
% ./configure
% make
% make install
</PRE>

<P>This installs the foreign libraries in <CODE>$PLBASE/lib/$PLARCH</CODE> 
and the Prolog library files in <CODE>$PLBASE/library</CODE>, where
<CODE>$PLBASE</CODE> refers to the SWI-Prolog `home-directory'.

<H2><A NAME="sec:4"><SPAN class="sec-nr">4</SPAN> <SPAN class="sec-title">Acknowledgments</SPAN></A></H2>

<P>The SWI-Prolog ODBC interface started from a partial interface by 
Stefano De Giorgi. Mike Elston suggested programmable 
null-representation with many other suggestions while doing the first 
field-tests with this package.

<H2>Bibliography</H2>

<DL class="bib"><DT class="bib"><A NAME="miller1990"><STRONG>[1]</STRONG></A><DD class="bib">
George Miller. Wordnet: An on-line lexical database.
<EM>International Journal of Lexicography</EM>, 3(4), 1990. (Special 
Issue).

<P></DD>
</DL>

<H1><A NAME="document-index">Index</A></H1>

<DL>
<DT><STRONG>C</STRONG></DT>
<DD>
</DD>
<DT>call_cleanup/3</DT>
<DD>
<A class="idx" href="#idx:callcleanup3:46">2.9</A></DD>
<DT><STRONG>F</STRONG></DT>
<DD>
</DD>
<DT>findall/3</DT>
<DD>
<A class="idx" href="#idx:findall3:13">2.2.1</A> <A class="idx" href="#idx:findall3:15">2.2.1</A></DD>
<DT>format/2</DT>
<DD>
<A class="idx" href="#idx:format2:8">2.2.1</A></DD>
<DT><STRONG>O</STRONG></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_close_statement/+Statement">odbc_close_statement/+Statement</A></DT>
<DD>
</DD>
<DT>odbc_connect/2</DT>
<DD>
<A class="idx" href="#idx:odbcconnect2:3">2.1</A></DD>
<DT><A class="idx" href="#odbc_connect/3">odbc_connect/3</A></DT>
<DD>
<A class="idx" href="#idx:odbcconnect3:2">2.1</A> <A class="idx" href="#idx:odbcconnect3:6">2.1</A> <A class="idx" href="#idx:odbcconnect3:10">2.2.1</A></DD>
<DT><A class="idx" href="#odbc_current_connection/2">odbc_current_connection/2</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_current_table/2">odbc_current_table/2</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_current_table/3">odbc_current_table/3</A></DT>
<DD>
<A class="idx" href="#idx:odbccurrenttable3:39">2.4</A></DD>
<DT><A class="idx" href="#odbc_data_source/2">odbc_data_source/2</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_debug/1">odbc_debug/1</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_disconnect/1">odbc_disconnect/1</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_driver_connect/3">odbc_driver_connect/3</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_end_transaction/2">odbc_end_transaction/2</A></DT>
<DD>
<A class="idx" href="#idx:odbcendtransaction2:38">2.3</A></DD>
<DT><A class="idx" href="#odbc_execute/2">odbc_execute/2</A></DT>
<DD>
<A class="idx" href="#idx:odbcexecute2:33">2.2.3</A></DD>
<DT><A class="idx" href="#odbc_execute/3">odbc_execute/3</A></DT>
<DD>
<A class="idx" href="#idx:odbcexecute3:26">2.2.2</A> <A class="idx" href="#idx:odbcexecute3:29">2.2.2</A></DD>
<DT>odbc_execute/[2-3]</DT>
<DD>
<A class="idx" href="#idx:odbcexecute23:21">2.2.2</A></DD>
<DT>odbc_fetch/2</DT>
<DD>
<A class="idx" href="#idx:odbcfetch2:34">2.2.3</A></DD>
<DT><A class="idx" href="#odbc_fetch/3">odbc_fetch/3</A></DT>
<DD>
<A class="idx" href="#idx:odbcfetch3:23">2.2.2</A> <A class="idx" href="#idx:odbcfetch3:31">2.2.3</A> <A class="idx" href="#idx:odbcfetch3:36">2.2.3</A></DD>
<DT><A class="idx" href="#odbc_free_statement/1">odbc_free_statement/1</A></DT>
<DD>
<A class="idx" href="#idx:odbcfreestatement1:42">2.5</A></DD>
<DT><A class="idx" href="#odbc_get_connection/2">odbc_get_connection/2</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_prepare/4">odbc_prepare/4</A></DT>
<DD>
<A class="idx" href="#idx:odbcprepare4:24">2.2.2</A> <A class="idx" href="#idx:odbcprepare4:28">2.2.2</A></DD>
<DT><A class="idx" href="#odbc_prepare/5">odbc_prepare/5</A></DT>
<DD>
<A class="idx" href="#idx:odbcprepare5:18">2.2.2</A> <A class="idx" href="#idx:odbcprepare5:32">2.2.3</A> <A class="idx" href="#idx:odbcprepare5:44">2.6</A></DD>
<DT>odbc_prepare/[4-5]</DT>
<DD>
<A class="idx" href="#idx:odbcprepare45:41">2.5</A></DD>
<DT><A class="idx" href="#odbc_query/2">odbc_query/2</A></DT>
<DD>
<A class="idx" href="#idx:odbcquery2:9">2.2.1</A> <A class="idx" href="#idx:odbcquery2:27">2.2.2</A></DD>
<DT><A class="idx" href="#odbc_query/3">odbc_query/3</A></DT>
<DD>
<A class="idx" href="#idx:odbcquery3:12">2.2.1</A> <A class="idx" href="#idx:odbcquery3:14">2.2.1</A> <A class="idx" href="#idx:odbcquery3:17">2.2.1</A> <A class="idx" href="#idx:odbcquery3:19">2.2.2</A></DD>
<DT><A class="idx" href="#odbc_query/4">odbc_query/4</A></DT>
<DD>
<A class="idx" href="#idx:odbcquery4:4">2.1</A> <A class="idx" href="#idx:odbcquery4:7">2.2.1</A> <A class="idx" href="#idx:odbcquery4:22">2.2.2</A> <A class="idx" href="#idx:odbcquery4:25">2.2.2</A> <A class="idx" href="#idx:odbcquery4:43">2.6</A></DD>
<DT>odbc_query/[2-3]</DT>
<DD>
<A class="idx" href="#idx:odbcquery23:40">2.5</A></DD>
<DT><A class="idx" href="#odbc_set_connection/2">odbc_set_connection/2</A></DT>
<DD>
<A class="idx" href="#idx:odbcsetconnection2:1">2.1</A> <A class="idx" href="#idx:odbcsetconnection2:5">2.1</A> <A class="idx" href="#idx:odbcsetconnection2:11">2.2.1</A> <A class="idx" href="#idx:odbcsetconnection2:16">2.2.1</A> <A class="idx" href="#idx:odbcsetconnection2:20">2.2.2</A> <A class="idx" href="#idx:odbcsetconnection2:35">2.2.3</A> <A class="idx" href="#idx:odbcsetconnection2:37">2.3</A></DD>
<DT><A class="idx" href="#odbc_statistics/1">odbc_statistics/1</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_table_column/3">odbc_table_column/3</A></DT>
<DD>
</DD>
<DT><A class="idx" href="#odbc_type/3">odbc_type/3</A></DT>
<DD>
</DD>
<DT><STRONG>P</STRONG></DT>
<DD>
</DD>
<DT>print_message/2</DT>
<DD>
<A class="idx" href="#idx:printmessage2:45">2.7.1</A></DD>
<DT><STRONG>R</STRONG></DT>
<DD>
</DD>
<DT>read/1</DT>
<DD>
<A class="idx" href="#idx:read1:30">2.2.3</A></DD>
</DL>

</BODY></HTML>