2.2.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.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 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.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-DD rather 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:

fetch(FetchType)

Determine the FetchType, which is one of auto (default) to extract the result-set on backtracking or fetch 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.^{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.

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.