Statement API

This class provides the Java methods for the do-it yourself foreign predicates. We will use our reference data type to work with statement objects. The JDBC statement object is created by the predicate create_statement/1. The predicate will return a reference term to a new statement object. The predicate execute_query/3 is then responsible for executing a query and retrieving result tuples. The predicate close_statement/1 allows closing the statement object. The Java methods for the foreign predicates have been defined as follows:

import java.sql.*;
import java.util.Properties;

public class StatementAPI {
    public static Statement createStatement()
                    throws InterpreterMessage;
    public static Object executeQuery(CallOut co,
                    Statement obj, String select)
    public static void closeStatement(Statement obj)

We decided to statically allocate the SQL driver and the connection credentials in the StatementAPI class. We also hardwired the user name, the password and JDBC connection URL. To run the example the “xxx” strings have to be replaced by the parameters at hand. In practice one could use a more flexible solution where the driver class and the connection parameters would be configurable. There are a couple of APIs and products around that provide this functionality, but for the sake of simplicity we show here a static solution:

    private final static Properties info = new Properties();
private final static Driver driver;

static {
try {
info.put("user", "xxx");
info.put("password", "xxx");
Class<?> clazz = Class.forName("xxx");
driver = (Driver) clazz.newInstance();
} catch (ClassNotFoundException x) {
throw new RuntimeException(x);
} catch (InstantiationException x) {
throw new RuntimeException(x);
} catch (IllegalAccessException x) {
throw new RuntimeException(x);

The Java method executeQuery implements a non-deterministic predicate. For more details on implementing non-deterministic predicates via Java methods see the programming interface documentation of the runtime library. One row will be returned in the form of a Prolog list. Upon redo further rows are fetched. The Prolog list is dynamically built. The Prolog type of the column values is inferred from the Java type returned by the JDBC interface. In practice one could implement a more complex mapping:

                ResultSetMetaData meta = set.getMetaData();
Object valList = Knowledgebase.OP_NIL;
for (int i = meta.getColumnCount(); i >= 1; i--) {
Object col = set.getObject(i);
Object val;
if (col instanceof String) {
val = col;
} else if (col instanceof Integer) {
val = col;
} else {
throw new InterpreterMessage(
valList = new TermCompound(Knowledgebase.OP_CONS,
val, valList);

The statement object basically encapsulates an SQL cursor factory. Whether this cursor does some prefetching depends on the configuration of the JDBC driver and the database manage-ment system at hand. In our approach we also allocate one database connection per SQL statement. Most JDBC drivers and database management systems would allow for the creation of multiple and thus concurrent SQL statement over the same database connection. We didn’t make use of this feature here.

When statement object is not anymore needed it will be returned via the Java method closeStatement(). This method will close the SQL statement, which in turn by the definition of JDBC protocol will also close any pending SQL cursor on this statement. Since in our model each SQL statement has one database connection associated and since we do not cache da-tabase connections, we manually close the database connection as well:

    public static void closeStatement(Statement obj)
throws InterpreterMessage {
Connection con = null;
try {
con = obj.getConnection();

In practice one would also use a more flexible solution here. The creation of a database connection is usually quite expensive in time and space. Therefore one would try to pool database connections or to pre-allocate database connections. A database management system might terminate an idle database connection at any time. Therefore any pooling would also need a more clever detection of externally closed database connections and their reopening. There are a couple of APIs and products around that would provide pooling.