Prolog Driver

We will use a Prolog predicate to access the JDBC database. Accessing the JDBC database is done via some do-it yourself foreign predicates. Currently there are no Jekejeke Prolog built-ins to access a JDBC database. But Jekejeke Prolog provides an application programming interface. We have used this interface to provide a couple of Java foreign predicates that allow us to access a JDBC database.

The Prolog code will first built a SELECT statement by assembling WHERE conditions according to the given search criteria. The conditions are first accumulated in a list. A single string respective number condition is added to the list via the predicates str_cond/2 respectively num_cond/2. String conditions will need an encoding of the string into an SQL literal. The SQL literal encoding rule is specific to the used database management. We provided the Java foreign predicate literal_encode/2 for that purpose:

:- foreign(literal_encode/2, 'StatementAPI', literalEncode('String')).

% str_cond(+String, +ColumnOperator, +WhereList, -WhereList).
str_cond('', _, W, W) :- !.
str_cond(L, CO, W, [COE|W]) :-
    literal_encode(L, E),
    atom_concat(CO, E, COE).

% num_cond(+Number, +ColumnOperator, +WhereList, -WhereList).
num_cond('', _, W, W) :- !.
num_cond(N, CO, W, [CON|W]) :-
    atom_concat(CO, N, CON).

There is also a predicate make_where/2 which will assemble the final WHERE clause from the WHERE conditions. More details are given in the appendix. The foreign predicates for accessing the JDBC database are based on SQL. The implementation makes use of a driver instance which is specific to the used database management system. The interface is very minimal and only allows access to the database querying language (DQL). There is no support for the database manipulation language (DML) or the database definition language (DDL). The corresponding declaration of the Java foreign predicates reads as follows:

:- foreign(create_statement/1, 'StatementAPI', createStatement).
:- foreign(execute_query/3, 'StatementAPI',
executeQuery('CallOut', 'Statement', 'String')).
:- foreign(close_statement/1, 'StatementAPI', closeStatement('Statement')).

The drive/7 predicate is then straight forward. It will first build the WHERE clause and then prepend it with the SELECT clause and the FROM clause. As a next step we will open a statement object, execute the query and close the statement object. The predicate to execute the query is redoable and it will successively return result tuples. To protect this sequence we will use again the setup_call_cleanup/3 system predicate. The full code for the drive/7 predicate can be found in the appendix:

    atom_concat('SELECT * FROM employee', W, Q),
                       execute_query(S, Q, [X,Y,Z,T]),

We can again test the Prolog driver in the development environment. Before doing some testing it is important to setup the development environment so that it includes both the StatementAPI class and the SQL Driver class in the class path. After having consulted the Prolog text of the driver we can then query the drive/7 predicate. The simplest query sets all the search criteria to blank. The JDBC database will then return all employee/4 facts.

?- [example06/driver].
?- drive('','','','','','',X).
X = employee('Сергей', 'Иванов', 53, 18500) ;
X = employee('Сергей', 'Беляев', 53, 19000) ;
X = employee('Hans', 'Fischer', 62, 21500) ;