If supported by the database, the interface allows the use of prepared SQL statements with parameters (placeholders). Prepared SQL statements are pre-parsed by the database, and can be executed more efficiently for multiple times, with the placeholders acting like variables, taking on different values for each execution.
The syntax used for
prepared statements is that provided by the database, but a common syntax
is to use
? to indicate a placeholder. For example:
insert into employees (enum, ename, esalary, ejob) values (?, ?, ?, ?)
would be used to add rows to the employees relation.
Such an SQL statement has to be prepared before execution. It can then be executed in batches to insert several tuples in one go. Preparation involves parsing the SQL statement and setting up a buffer for the tuples.
A data template is used as an example buffer. For the insert command above it might look like:
emp(1234,"some name",1000.0,'some job')
The argument positions correspond to the order of the placeholder in the SQL statement. The types of the data will be used to type-check the tuples when they are inserted.
The following ECLiPSe goal uses a template to create a cursor for an insert command:
SQL = "insert into employees (enum,ename,esalary,ejob) values (?,?,?,?)", Template = emp(1234,"some name",1000.0,'some job'), session_sql_prepare(H, Template, SQL, Cursor),
H is a handle to a database session, and
Cursor is the cursor
created for the prepared statement
The cursor can then be used to insert several rows into the employee table.
cursor_next_execute(Cursor,emp(1001,"E.G. SMITH",1499.08,editor)), cursor_next_execute(Cursor,emp(1002,"D.E. JONES",1499.08,journalist)),
Similarly for queries a data template specifies the types of the columns retrieved. The positions of the arguments correspond to the position of the select list items. The example template above might be used for a query like
SQL = "select enum, ename, esalary, ejob from employees where esalary > 1000", Template = emp(1234,"some name",1000.0,'some job'), session_sql_query(H, Template, SQL, Cursor), cursor_next_tuple(Cursor,Tuple), % Tuple is now somthing like emp(1001,"E.G. SMITH",1499.08,editor)
If a structure or list appears in one of the argument positions this stands for a general term, to be stored or retrieved in external database format. This way one is not limited to atomic types which have natural mappings to database types.
Data is passed from ECLiPSe into the database via placeholders in prepared SQL statements, and passed from the database to ECLiPSe via the results tuples returned by executing SQL queries. The interface takes care of the conversion of data to/from ECLiPSe types to the external C API types, and the database then converts these to/from the internal types of the database, as determined by the SQL statement used. The exact internal database types, and the exact conversion rules between the C type and the database type is dependent on the database’s API, but in general the following should hold for ECLiPSe types:
Prolog terms, strings and atoms can have variable sizes, but when they are passed into and out of the database, they pass through fixed size buffers for reasons of efficiency.
In the case of fetching data from fixed size database fields, the size of these buffers is by default, the size of the field in the database. In the case of variable sized fields and of placeholders, a default size is chosen.
Rather than taking the default it is possible to write templates that specify a size. This is done by mentioning the size in the argument of the template.
For example the following two templates specify the same type of tuple but the second one defines some sizes for the different elements in the tuple as well.
emp(1234,"name", Rules, job) emp(1234,"10",rules(4000),'10')
The size information is used to define the minimum size of the buffer used to pass data between ECLiPSe and the database. Depending on the database and the situation (input/output, prepared/direct statements), such an intermediate buffer may not be used; in such cases, the buffer size will be ignored. Otherwise, if the data is too big to fit into the buffer, an error will be raised.
The data in the buffer is passed to/from the database, which may have its own size specification for the data, which is independent of the size information specified in the template. In the case of sending data to the database, and the data is too large for the database, the exact behaviour is dependent on the database. In the case of receiving the data from the database, and the data is too large for the buffer, an error will be raised.