2.3.2 Parameterised queries
ODBC provides for‘parameterized queries’. These are SQL
queries with a
-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
using
for Options.[]
- 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:?
- default
- Uses the ODBC function SQLDescribeParam() to obtain information
about the parameter and apply default rules. See section
2.7 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
char
,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.7 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-DD
rather than a termdate(Year,Month,Day)
. This construct enhances flexibility and allows for passing values that have no proper representation in Prolog. - Variable
- Interpreted as
default
. It unifies Variable with the PrologType > SqlType as using the types derived.4 The current version does not provide the field with in SqlType. Future versions may improve on that. This feature is first of all intended for debugging. Usingodbc_debug(1)
, the library prints details on the derived types.
Options defines a list of options for executing the statement. See odbc_query/4 for details. In addition, the following option is provided:
- fetch(FetchType)
- Determine the FetchType, which is one of
auto
(default) to extract the result-set on backtracking orfetch
to 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.5Is 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.6The 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.
- odbc_cancel_thread(+ThreadId)
- 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.
- odbc_free_statement(+Statement)
- 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.