- cursor_N_execute(++Cursor, -Executed, +TupleList, -RestTupleList)
- Executes the parametrised prepared SQL statement represented by Cursor, once for each tuple in TupleList.
- cursor_N_tuples(++Cursor, -Retrieved, -ResultTuples, -RestResultTuples)
- Retrieve result tuples from the SQL query in the difference list ResultTuples and RestResultTuples.
- cursor_all_execute(++Cursor, +TupleList)
- Executes the parametrised prepared SQL statement represented by Cursor, once for each tuple in TupleList.
- cursor_all_tuples(++Cursor, -ResultTuples)
- Retrieve all remaining result tuples from the SQL query in ResultTuples
- cursor_close(++Cursor)
- Close the cursor associated with Cursor
- cursor_next_execute(++Cursor, +Tuple)
- Executes the parametrised prepared SQL statement represented by Cursor.
- cursor_next_execute(++Cursor, +Tuple, ++Options)
- Executes the parametrised prepared SQL statement represented by Cursor, with options Options.
- cursor_next_tuple(++Cursor, -ResultTuple)
- Retrieve the next result tuple from the SQL query in ResultTuple
- session_close(++Session)
- close a session for the DBMS
- session_commit(++Session)
- commits transactional changes made to the database.
- session_rollback(++Session)
- rollback transactional changes made to the database.
- session_sql(++Session, ++SQL, -RowProcessedCount)
- Executes a SQL statement on the database server.
- session_sql_prepare(++Session, +ParamTemplate, ++SQL, -Cursor)
- Prepares a non-query SQL statement for execution by the DBMS.
- session_sql_prepare_query(++Session, +ParamTemplate, +ResultTemplate, ++SQLQuery, -Cursor)
- Prepares a SQL query for execution by the DBMS.
- session_sql_query(++Session, +ResultTemplate, ++SQLQuery, -Cursor)
- Executes a SQL query on the database server.
- session_sql_query(++Session, +ResultTemplate, ++SQLQuery, ++Options, -Cursor)
- Executes a SQL query on the database server with options specified by Options.
- session_start(++Login, ++Password, ++Options, -Session)
- starts a new session for the DBMS
- session_transaction(++Session, +Goal)
- executes Goal as a database transaction.
- session_transaction(?, ?, ?)
- No description available
- struct cursor(handle, session)
- No description available
This library provides an interface to database management systems (DBMS) that supports SQL (Structure Query Language), allowing SQL commands, written in an ECLiPSe program, to be passed to the DBMS for processing, and their results (if any) to be passed back to ECLiPSe.
The exact SQL supported by the library is determined by the underlying DBMS, as the SQL commands are passed directly to the DBMS via its C API. If supported by the C API, the library also allows the use of prepared SQL statements, where a SQL statement, possibly parameterised to accept different input data, is parsed once by the DBMS, and efficiently executed multiple times. Support for transactions (with commit and rollback) is also provided.
The library provides a relatively low-level interface to the DBMS, but higher level interfaces (e.g. where a SQL query is viewed as a predicate, yielding different results tuples on backtracking) can be constructed on top of this.
Currently, MySQL (version 5.0 or later) is supported by the library. Note that the MySQL client dynamic library (libmysqlclient.so on Unix systems, libmysql.dll on Windows) is not included with the distribution, and if this file is not found by ECLiPSe on loading lib(dbi), there will be an error. This file can be obtained by downloading MySQL, and placing the .so or .dll file into a place where ECLiPSe can find it, i.e. one of the following:
Data is exchanged with the DBMS via:
Input and output data in the structures need to be converted from/to ECLiPSe datatypes. The ECLiPSe datatype is specified by a template, which specifies the datatype by example, i.e. an example result tuple structure (for output) or parameter structure (for input) where each argument specify the type for that argument, e.g. emp(123,"some name",100.0,'some job',extra(data)), specifies that the first argument should be an integer, the second a string, the third a real, the fourth, an atom, and the last, a general Prolog term. Note that general Prolog terms are stored in the database in a binary representation, and the conversion is done by the library.
The data is exchanged with the DBMS via buffers. Some DBMS require the sizes of the buffers to be specified. This is done automatically by the library, but if the default sizes need to overridden (e.g. if the argument is large), the template can be used to specify the size of buffers for variable length data, e.g. emp(123,"1000",100.0,'2000',extra(5000)), where the second argument "1000" specifies a buffer size of 1000 bytes, the third argument '2000' a buffer size of 2000 bytes, and the last argument, extra(5000), a buffer size of 5000 bytes. Note that any size specifications are simply ignored if not required.