Previous Up Next

11.3  Data Templates

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 SQL.

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.

11.3.1  Conversion between ECLiPSe and 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:

Strings and atoms
are converted to C char * type. This should be used for non-numeric data. Restrictions may apply depending on the SQL datatype – for example, non-binary string types (such as VARCHAR) does not accept generic binary strings, and SQL data and time types must be in the correct syntax – consult the database manual for the syntax for these types.
Integers and Floats
are converted to C integers and doubles, which are then converted to the specified SQL numeric types. The numbers are passed to the database’s C API at the maximum precision and size supported by the database’s API. Any integers outside the range representable by the C API’s integer type will raise an error. Note that while the number passed to the database is at maximum precisiion and size, the corresponding SQL numberic type specified by the SQL statement that receives the number may be smaller (e.g. SMALLINT). The exact behaviour in this case depends on the database.
General terms
are converted to ECLiPSe’s internal dbformat - a flat binary representation of the term, and then to an appropriate SQL binary type. This allows ECLiPSe to store and retrieve general terms, but if it is required to exchange Prolog terms with external sources via the database, then the term should be first converted to EXDR format, and the EXDR string can then be passed to the database. Note that EXDR can only represent a subset of terms – see the Embedding and Interfacing manual for details.

11.3.2  Specifying buffer sizes in templates

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.

’123’
defines an atom datatype where the maximum length in bytes is 123. The length is given as a decimal number.
"123"
defines a string datatype where the maximum length in bytes is 123. The length is given as a decimal number.
s(123,X)
Describes any Prolog term that occupies up to 123 bytes in external database format. Any structure whose first argument is an integer can be used.

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.

Previous Up Next