11.1  Introduction

The ECLⁱPS^e SQL database interface is a low-level interface to the SQL language. As far as possible it has been attempted to give the full power of the SQL interface to the programmer.

A number of features are designed to permit transfer of large amounts of data with minimal overhead:

• The buffered retrieval of multiple tuples from a relation or view.
• Buffered multiple updates and inserts
• Re-use of SQL statements that are parametrised with placeholders.
• Tuple templates to permit the allocation and re-use of fixed size buffers.

The interface provides safe handles to database cursors. Higher level interfaces can be constructed on top of this. For example:

• Viewing an SQL query as a predicate that yields different tuples on backtracking.
• Viewing an SQL query as a lazily constructed list of tuples.

On backtracking, cursors as well as database sessions are automatically closed. This is required if one is to build abstractions where they are hidden from the programmer.

Nowhere in the interface are SQL commands ever looked into. They are always passed straight from the user to the database. This means that any SQL command supported by the underlying database will be accessible. Note also any differences in the SQL between different databases are also not hidden from the user, so the SQL written by the user must be valid SQL for the database that is being interfaced to.

When retrieving or inserting tuples, tuple templates are used to specify the types of the different fields being retrieved or inserted. This allows for a flexible mapping between database internal types and the tuples retrieved. For example one can retrieve a date as either a string or an atom. What type combinations are supported will depend on the underlying database.

It is possible to write SQL queries with parameters (SQL placeholders), so that one can use the same query several times, with different values. This feature, together with the availability of any number of cursors can be used to write complex queries not expressible with a singe SQL statement.

If the database has a generic binary format, such as Binary Long Object (BLOB) fields, one can store and retrieve arbitrary terms in them automatically using the interface. To exchange ECLⁱPS^e terms with other applications via the database, the terms can be converted to EXDR data interchange format (see the Embedding and Interfacing chapter for details) before storing them in the database.

It is possible to open several sessions to different databases simultaneously. Transactions apply to one session only though.

The code is written so that it will be relatively easy to extend it to interface to different kinds of databases. The currently supported database is MySQL.