cursor_next_execute(++Cursor, +Tuple, ++Options)

Cursor

A cursor handle

Tuple

A tuple of parameter values matching the template for this cursor (structure)

Options

Options (list of Option:Value pairs or nil)

Description

Executes the parameterised prepared SQL statement represented by Cursor, previously prepared by session_sql_prepare/4 or session_sql_prepare_query/5. The parameter values for this execution is supplied by Tuple. Options is a (possibly empty) list of Option:Value pairs, specifying DBMS-specific options for the cursor.

Tuple is a structure whose name and arity match the parameter template when Cursor was prepared, and the arguments give the values for the parameters for this execution, and must be compatible with the type specified by the template, except that an argument can be an uninstantiated variable, to denote a NULL value for the corresponding parameter.

If the SQL statement is a query, and was prepared as a query using session_sql_prepare_query/5, results can be obtained from the query by the cursor_*_tuple family of predicates.

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.

type

Specifies the type of cursor, and is only meaningful if the buffering option is set to server. Value can be either no_cursor (the default) or read_only. These correspond to the MySQL statement attribute STMT_ATTR_CURSOR_TYPE of CURSOR_TYPE_NO_CURSOR and CURSOR_TYPE_READ_ONLY respectively (See the MySQL manual for details).

Only one active cursor of type no_cursor is allowed per session, and this active cursor must be closed before another query can be issued to the DBMS server. read_only cursor does not have this restriction, and several such cursors can be active at the same time.

Exceptions

(5) type error

Cursor is not a valid cursor handle

(5) type error

Type mismatch between parameter template specification for Cursor and actual tuple data

(6) out of range

Invalid option specification in Options

(dbi_buffer_over)

Parameter value(s) too big for the buffer

(dbi_error)

Error from DBMS while executing SQL associated with Cursor.

(dbi_bad_template)

ParamTemplate not specified when Cursor was created

(dbi_bad_cursor)

The Cursor is not in a state to execute a query (e.g. it was cancelled)

See Also