session_sql_query(++Session, +ResultTemplate, ++SQLQuery, ++Options, -Cursor)

Session

A session handle

ResultTemplate

Template defining the types of results tuple (structure)

SQLQuery

A SQL statement query (string)

Options

Options (list of Option:Value pairs or nil)

Cursor

Returned cursor handle

Description

Executes a SQL query on the database server. The predicate returns in Cursor the cursor handle for this SQL query, and the results can then be retrieved using cursor_*_tuple family of predicates. Options is a (possibly empty) list of Option:Value pairs, specifying DBMS-specific options for the cursor.

The SQL query returns result in tuples of N elements each. Each tuple is mapped to a Prolog structure of arity N. ResultTemplate is a structure of arity N specifying the types of the return data for ECLiPSe. See the general description of this library or the manual for a description of the template specification.

The SQL query must be valid for the DBMS to execute. It can contain NULL characters, i.e. it can contain binary data.

MySQL specific:

Options is used to specify the type of cursor used. Currently this only applies to cursors for SQL queries. The options are:

buffering

Specifies where the result set of a SQL query is buffered. Value can be either client (the default) or server. By default, the whole of the result set for a query is copied to the client (i.e. the ECLiPSe process running lib(dbi)) after the SQL query is executed. The alternative is to leave the result set on the DBMS server, and only get the result tuples from the server one by one (with e.g. cursor_next_tuple/2).

The default buffering is on the client side, because this is the default of the MySQL C API, and in addition, it imposes certain restrictions on how the result tuples can be retrieved. However, as the whole result set is retreived, this can impose significant memory overheads if there are many tuples in the result set. On the other hand, there is no restrictions on how many active client buffered cursor is allowed per session at the same time, but only one active server buffered cursor is allowed at any one time -- a query result must be exhausted or the cursor explicitly closed before another query can be issued for that session.

type

This option is not relevant for the direct SQL queries of session_sql_query/4. It is only relevant for prepared queries, and has no effect here.

Exceptions

(5) type error

Session is not a valid session handle, or SQLQuery not a string, or ResultTemplate not a structure

(6) out of range

Invalid option specification in Options

(dbi_error)

Error from DBMS while executing SQLQuery.

(dbi_bad_template)

ResultTemplate has the wrong arity

Examples

  check_overdraft_limit(Session, Account) :-
      L = ["select count(id) from accounts \
          where     id = ",Account," and balance < overdraft"],
      concat_string(L,SQL),
      % the buffering:server option is MySQL specific
      session_sql_query(Session,c(0),SQL,[buffering:server],OverdraftCheck),
      cursor_next_tuple(OverdraftCheck,c(Count)),
      Count = 0.

See Also