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
:- foreign(literal_encode/2, 'StatementAPI', literalEncode('String')).
% str_cond(+String, +ColumnOperator, +WhereList, -WhereList).
str_cond('', _, W, W) :- !.
str_cond(L, CO, W, [COE|W]) :-
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
X = employee('Сергей', 'Иванов', 53, 18500) ;
X = employee('Сергей', 'Беляев', 53, 19000) ;
X = employee('Hans', 'Fischer', 62, 21500) ;