ODBC provides for `parameterized queries'. These are SQL queries with
-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:
- odbc_prepare(+Connection, +SQL, +Parameters, -Statement)
- As odbc_prepare/5
- odbc_prepare(+Connection, +SQL, +Parameters, -Statement, +Options)
- Create a statement from the given SQL (which may be a format
specification as described with odbc_query/3)
statement that normally has one or more parameter-indicators (
) and unify Statement with a handle to the created statement. Parameters is a list of descriptions, one for each parameter. Each parameter description is one of the following:
- Uses the ODBC function SQLDescribeParam() to obtain information about
the parameter and apply default rules. See section
2.6 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
silent(true)option of odbc_set_connection/2. An alternative mapping can be selected using the > option of this predicate described below.
- SqlType(Specifier, ...)
- Declare the parameter to be of type SqlType with the given
specifiers. Specifiers are required for
varchar, etc. to specify the field-width. When calling odbc_execute/[2-3], the user must supply the parameter values in the default Prolog type for this SQL type. See section 2.6 for details.
- PrologType > SqlType
- As above, but supply values of the given PrologType, using
the type-transformation defined by the database driver. For example, if
the parameter is specified as
atom > date
The use must supply an atom of the format
YYYY-MM-DDrather than a term
date(Year,Month,Day). This construct enhances flexibility and allows for passing values that have no proper representation in Prolog.
Options defines a list of options for executing the statement. See odbc_query/4 for details. In addition, the following option is provided:
- Determine the FetchType, which is one of
auto(default) to extract the result-set on backtracking or
fetchto prepare the result-set to be fetched using odbc_fetch/3.
- odbc_execute(+Statement, +ParameterValues, -RowOrAffected)
- Execute a statement prepared with odbc_prepare/4
with the given
ParameterValues and return the rows or number of affected
rows as odbc_query/4.
This predicate may return type_error exceptions if the provided
parameter values cannot be converted to the declared types.
ODBC doesn't appear to allow for multiple cursors on the same result-set.4Is this right? This would imply there can only be one active odbc_execute/3 (i.e. with a choice-point) on a prepared statement. Suppose we have a table
age (name char(25), age integer)bound to the predicate age/2 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.5The code is prepared to maintain a cache of statements. Practice should tell us whether it is worthwhile activating this.
same_age(X, Y) :- age(X, AgeX), age(Y, AgeY), AgeX = AgeY.
- odbc_execute(+Statement, +ParameterValues)
- Like odbc_query/2, this predicate is meant to execute simple SQL statements without interest in the result.
- If the thread ThreadId is currently blocked inside odbc_execute/3 then interrupt it. If ThreadId is not currently executing odbc_execute/4 then odbc_cancel_thread/1 succeeds but does nothing. If ThreadId is not a valid thread ID or alias, an exception is raised.
- Destroy a statement prepared with odbc_prepare/4. If the statement is currently executing (i.e. odbc_execute/3 left a choice-point), the destruction is delayed until the execution terminates.