The interface supports two types of querying. In direct querying, the query statement is not prepared while in prepared querying the query statement is prepared before being executed. The results from both types of querying are retrieved tuple at a time. Direct querying is done by the predicate:
| ?- db_query(ConnectionHandle, QueryHandle, SQLQueryList, ReturnList).
ConnectionHandle is the name of the handle used for the database connection. QueryHandle is the name of the handle for this particular query. Currently, the query handle is being used only for prepared queries. However, in future versions, the query handle can be used in direct queries to retrieve arbitrary tuples from a result set using cursors. Also, it will be possible to combine arbitrary tuples from different queries to the same database using the query handle. The SQLQueryList is a list of terms which is used to build the SQL query. The terms in this list are ground atoms. ReturnList is a list of variables each of which correspond to a return value in the query. It is upto the user to specify the correct number of return variables corresponding to the query. Also, as in the case of a connection handle, the user is responsible for giving the name to the query handle. For example, a query on the student database to select all the students for a given advisor is accomplished by the call:
| ?- X = adv,
db_query(ha, qa, ['select T.name from student T where T.advisor = ', X], [P]),
fail.
where ha and qa are respectively the connection handle and query handle name the user chose.
Observe that the query list is composed of the SQL string and a ground value for the advisor. The return list is made of one variable corresponding to the student name. The failure drive loop retrieves all the tuples.
Preparing a query is done by the call to the predicate:
| ?- db_prepare(ConnectionHandle, QueryHandle, SQLQueryList).
As before, ConnectionHandle and QueryHandle specify the handles for the connection and the query. The SQLQueryList is a list of terms which build up the query string. The placeholder `?' is used for values which have to be bound during the execution of the statement. For example, to prepare a query for selecting the advisor name for a student name using our student database:
| ?- db_prepare(ha, qa, ['select T.advisor from student T where T.name = ?']).
A prepared statement is executed using the predicate:
| ?- db_prepare_execute(QueryHandle, BindList, ReturnList).
The BindList contains the ground values corresponding to the `?' in the prepared statement. The ReturnList is a list of variables for each argument in a tuple of the result set.
For direct querying, the statement handle is closed automatically when all the tuples in the result set have been retrieved. In order to explicitly close a statement handle, and free all the resources associated with the handle, a call is made to the predicate:
| ?- db_statement_close(QueryHandle).
where QueryHandle is the query handle for the statement to be closed.
The interface is also able to transparently handle Prolog terms. Users can both save and retrieve terms without any special processing.