These predicates deal with sessions as a whole. A session is used to identify a connection to a database. Associated to that session are a number of cursors. These are handles to SQL statements which are currently being executed. For example a query where only some of the matching rows have been fetched.
Database transactions – where updates to the database are local to the session until committed, and where uncommitted changes can be rolled back, are supported if the external database supports transactions1.
This create a new session by establishing a new connections to the database server, and associates it with a handle, returned in Session.
The session remains in existence in subsequent goals. It is automatically closed if the program fails or aborts back beyond this call. The session is used as a access handle to the database in subsequent calls to this interface.
The automatic closure is particularly useful in case of a program aborting due to a runtime error. Closing the database ensures any database updates that have not been committed will be undone.
This closes a session, disconnecting from the database server. It takes effect immediately. This allow resources allocated for the session to be freed. To free all resources associated with a session, all cursors of the session should also be closed with cursor_close/1.
If executed outside the scope of a session_transaction/2 goal, this commits any transactional updates to the database made within Session. Otherwise, it simply succeeds without doing anything.
If executed outside the scope of a session_transaction/2 goal, this undoes all transactional changes made to the database since the last commit for this session. Otherwise, it will simply abort the complete outer transaction. (Note: not all changes can be rolled back; consult the DB manual for details)
This executes Goal as a database transaction. This predicate is only useful if the database supports transactions. Data base updates within Goal are committed if Goal succeeds; if Goal fails or aborts, the updates are rolled back.
Calls of this predicate can be nested, but only the outermost transaction is real. All the inner transactions are simply equivalent to call(Goal). This way it is possible to write a call to session_transaction/2, into some code that implements a simple update, but then to include that simple update into a larger transaction.
Transactions are local to one session so there is no way to safely make an update relating to several sessions.
recorded_transfer(Session,Date,Amount,FromAccount,ToAccount) :-
session_transaction(Session, (
transfer(Session, Amount,FromAccount,ToAccount),
check_overdraft_limit(FromAccount),
record_transfer(Date,Amount,FromAccount,ToAccount)
)).
transfer(Session, Amount,FromAccount,ToAccount) :-
session_transaction(Session,
transfer_(Session,Amount,FromAccount,ToAccount)
).
In the above example we can see two nested transactions. One simple bank transfer that is not recorded, and an outer transaction recording the occurrence of the transfer and checking the balance.
Since a nested transaction is simply a call of its goal, with no partial rollbacks care has to be taken not to redo transactions on failure unless one is sure one is at an outer transaction.
For database updates, lib(dbi) provides predicates to execute SQL statements on the database without returning results. session_sql/3 executes an SQL statement directly. session_sql_prepare/4 is used to prepare SQL statements, returning a cursor to the prepared statement, which can then be executed multiple times with different placeholder values using either cursor_next_execute/2 or cursor_all_execute/2 or cursor_N_execute/4. Cursors are automatically closed if the program backtracks or aborts beyond the predicate that created it. Alternatively, the cursor can be closed explicitly by cursor_close/1.
The datatypes of the parameters for the prepared statement is specified by a template given to session_sql_prepare/4. See section 11.3 for details on the templates.
This is the simplest interface to execute an SQL statement with no placeholders.
make_accounts(Session) :-
session_sql(Session,
"create table accounts \
(id decimal(4) not null,\
balance decimal(9,2) default 0.0 not null, \
overdraft decimal(9,2) default 0.0 not null \
)" ,_),
session_sql(Session,
"insert into accounts (id,balance) values (1001,1200.0)",1),
session_sql(Session,
"insert into accounts (id,balance) values (1002,4300.0)",1).
In the example we see session_sql/3 used, first to create a table, and then to initialise it with two rows. The rows processed counts are checked to make sure exactly one row is processed per statement.
This code assumes a session with handle Handle has been started beforehand.
This creates Cursor, which is a handle to the prepared statement, possibly with placeholders. Template specifies the types of the placeholders (see section 11.3).
transfer_(Session, Amount, FromAccount, ToAccount) :-
SQL = "update accounts set balance = balance + ? \
where id = ?",
Deduct is - Amount,
session_sql_prepare(Session,incbal(1.0,12),SQL,Update),
cursor_next_execute(Update,incbal(Deduct,FromAccount)),
cursor_next_execute(Update,incbal(Amount,ToAccount)).
In the example a cursor is prepared to modify account balances. It is used twice, once to deduct an amount and once to add that same amount to another account. Note: the example uses MySQL’s syntax for prepared statement, which may differ from other databases. Consult your database manual for prepared statement syntax.
Execute the prepared SQL statement represented by Cursor, with Tuple supplying the values for any parameter values. This call can be executed any number of times on the same cursor.
The SQL statement of Cursor is executed once for each Tuple in TupleList. This could be defined as:
cursor_all_execute( Cursor, []).
cursor_all_execute( Cursor, [Tuple | Tuples] ) :-
cursor_next_execute(Cursor, Tuple),
cursor_all_execute( Cursor, Tuples ).
Some databases supports the execution of multiple tuples of parameter values at once, doing this more efficiently than executing each tuple of parameter values one by one. This predicate is provided to support this.
Note that for databases that does not support execution of multiple tuples, this predicate is implemented by executing the Tuples one by one as in cursor_next_execute/2, and there is no gain in efficiency over using cursor_next_execute/2.
transfer_(Session, Amount, FromAccount, ToAccount) :-
SQL = "update accounts set balance = balance + ? \
where id = ?",
Deduct is - Amount,
session_sql_prepare(Session,incbal(1.0,12),SQL,Update),
Updates = [incbal(Deduct,FromAccount),incbal(Amount,ToAccount)],
cursor_N_execute(Update,_,Updates,[]).
The example shows how to re-code the bank transfer predicate from cursor_next_execute/2, to execute both updates with one call. This could lead to some performance improvement in a client server setting for databases that supports multiple parameter tuples.
For database queries, lib(dbi) provides predicates to execute SQL statements and extract the results returned by the database. session_sql_query/4 or session_sql_query/5 executes an SQL statement, returning a cursor to allow the results to be extracted from the database. The predicates to do this are cursor_next_tuple/2, cursor_all_tuples/2 and cursor_N_tuples/4.
The datatypes of the results tuple is specified by a template given to session_sql_query/4,5. See section 11.3 for details on the templates.
This executes SQL and creates the handle Cursor for the SQL query. Template specifies the datatypes of the results tuples.
A single tuple is retrieved from the database. Calling this predicate again with the same cursor will retrieve further tuples Any NULL values are returned as uninstantiated variables.
Once all the tuples have been retrieved this predicate fails.
If Tuple does not unify with the retrieved tuple, the predicate fails.
check_overdraft_limit(Session, Account) :-
L = ["select count(id) from accounts \
where id = ",Account," and balance < overdraft"],
concat_string(L,SQL),
session_sql_query(Session,c(0),SQL,OverdraftCheck),
cursor_next_tuple(OverdraftCheck,c(Count)),
Count = 0.
In this example a query is built to verify that the balance of an account is not less than its overdraft facility. All comparisons are done within the database, and we are just interested in checking that no rows match the ’where’ clause.
For this kind of application one would not normally use concat_string/2. SQL placeholders would be used instead. See session_sql_prepare_query/5.
The SQL query represented by the cursor is executed and all the matching tuples are collected in TupleList.
This could be defined as:
cursor_all_tuples( Cursor, Tuples ) :-
( cursor_next_tuple(Cursor, T) ->
Tuples = [T | Ts],
cursor_all_tuples(Cursor, Ts)
;
Tuples = []
).
If the underlying DB supports the retrieving mutule tuples in one go, then a buffer full of tuples matching the query is retrieved, otherwise all the remaining tuples are retrieved.
TupleList and RestTupleList form a difference list containing these tuples. Retrieved is the number of tuples retrieved.
The library allow SQL queries to be prepared and parameterised, if prepared SQL statements are supported by the underlying database. Templates are needed for specifying the datatypes of the parameters (as with session_sql_prepare/4), and for the results tuples (as with session_sql_query/4. An SQL query is prepared by session_sql_prepare_query/5, it then needs to be executed by cursor_next_execute/2 or cursor_next_execute/3 (cursor_next_execute/3 allows the specification of options for the cursor), and the results can then be retrieved by cursor_next_tuple/2, cursor_all_tuples/2 and cursor_N_tuples/4. After executing cursor_next_execute/2, it can be executed again with a new tuple of parameter values. Any unretrieved results from the previous execute are discarded. Note that this is non-logical: the discarded results are not recovered on backtracking.
This creates Cursor, which is a handle to the prepared SQL query.
By changing the placeholders one gets a fresh query and can start extracting tuples again.
In this example a generic query is built to check whether an account is overdrawn, and a cursor for this query is created.
make_check_overdraft_limit(Session, Cursor) :-
SQL = "select count(id) from accounts where ID = ? \
and balance < overdraft",
session_sql_prepare_query(Session,a(0),c(0),SQL,Cursor).
check_overdraft_limit(Check,Account) :-
cursor_next_execute(Check,a(Account)),
cursor_next_tuple(Check,c(Count)),
Count = 0.
The check_overdraft_limit/2 predicate uses the cursor to check an account. This cursor can be re-used to check any number of accounts.