Embedded SQL Cursor Types

DataFlex SQL drivers support various cursor types for embedded SQL statements: Forward Only, Dynamic, Static, and Keyset Driven. While Forward Only cursors (the default used by DataFlex for many years) retrieve data fastest and with the least overhead, specifying different cursor types can render results more tailored to application needs and avoid certain limitations. For instance, Microsoft SQL Server’s Forward Only cursors require variable length columns to be last in the SELECT statement. For queries that must include variable length columns intermixed with others, you can specify Static or Dynamic cursors instead. Note that Static cursors are generally faster than Dynamic but will not detect changes made by other users.

Cursors

SQL SELECT statements return a set of rows known as a result set. Cursors are an extension to result sets that provide a mechanism to retrieve one row or a small set of rows at a time. There are different cursor implementations with varying characteristics. Depending on the cursor type, there can be differences in performance, memory usage, and the location (client or server) of the cursor. Some cursor implementations also enforce restrictions on the SQL queries that can be executed. One such restriction is that on SQL Server, Forward Only cursors require variable length columns to be last in the SELECT.

The cursor type of an embedded SQL statement can be set with SQLSetStmtAttribute of an opened SQL Statement handle:

Example

Use sql.pkg
Get SQLOpen of hdbc to hstmt
If (hstmt <> 0) Begin
    Send SQLSetStmtAttribute of hstmt SQLSTMTATTRIB_CURSOR_TYPE SQL_CURSOR_STATIC
End

SQLSTMTATTRIB_CURSOR_TYPE can be set to the following values:

• SQL_CURSOR_FORWARD_ONLY
• SQL_CURSOR_DYNAMIC
• SQL_CURSOR_STATIC
• SQL_CURSOR_KEYSET_DRIVEN

Default: SQL_CURSOR_FORWARD_ONLY

The various cursor types have the following characteristics:

Forward Only Cursors

Forward Only cursors are usually the fastest way of processing a result set. A Forward Only cursor can only process a result set from beginning to end. A Forward Only cursor will fetch changes made by others.

On Microsoft SQL Server, Forward Only cursors have the limitation that variable length columns (varchar(max), nvarchar(max), varbinary(max), and the obsolete text) must be last in the SELECT.

Static Cursors

Static cursors always return the result set as it was when the statement was executed. Changes made by other applications will not be seen by a Static cursor.

Dynamic Cursors

Dynamic cursors are dynamic in the sense that they will detect all changes made to the rows in the result set by other applications. Dynamic cursors can also be scrolled forward and backward.

Keyset-Driven Cursors

A Keyset-Driven cursor lies between a Static and a Dynamic cursor in its ability to detect changes. It will see changed rows but will not see changes to the membership and order of the result set.

In general, a Forward Only cursor will retrieve data fastest with the least amount of overhead. On Microsoft SQL Server, Forward Only cursors have a limitation that variable length columns must be last in the SELECT. For queries that possibly have variable length columns not at the end, a Static or Dynamic cursor can be used. A Static cursor is usually faster than a Dynamic cursor but will not detect changes made by other users/applications.

SQLSetStmtAttribute - cSQLStatement

Set a statement attribute for an embedded SQL statement.

Type: Procedure

Parameters: - Integer iAttribId - String sAttribValue

Parameter Description: - iAttribId: Statement attribute to set - sAttribValue: Attribute value

Syntax

Function SQLGetStmtAttribute Integer iAttribId Returns String

Description

Sets an embedded SQL Statement attribute. The attribute must be set on an opened (with SQLOpen) embedded SQL statement handle.

The following statement attributes can be set:

• SQLSTMTATTRIB_CURSOR_TYPE

Allowed values: - SQL_CURSOR_FORWARD_ONLY - SQL_CURSOR_DYNAMIC - SQL_CURSOR_STATIC - SQL_CURSOR_KEYSET_DRIVEN

Default: SQL_CURSOR_FORWARD_ONLY

Example

Get SQLOpen of hdbc to hstmt
If (hstmt <> 0) Begin
    Send SQLSetStmtAttribute of hstmt SQLSTMTATTRIB_CURSOR_TYPE SQL_CURSOR_STATIC
End

Different cursor types have different characteristics. See ‘Embedded SQL cursor types’ for more info.

See Also

• SQLGetStmtAttribute

SQLGetStmtAttribute - cSQLStatement

Get a statement attribute for an embedded SQL statement.

Type: Function

Parameters: - Integer iAttribId - Returns String sAttribValue

Parameter Description: - iAttribId: Statement attribute to set - sAttribValue: Attribute value

Syntax

Function SQLGetStmtAttribute Integer iAttribId Returns String

Description

Gets the value of an embedded SQL Statement attribute. The attribute must be retrieved from an opened (with SQLOpen) embedded SQL statement handle.

The following statement attributes can be set:

• SQLSTMTATTRIB_CURSOR_TYPE

Allowed values: - SQL_CURSOR_FORWARD_ONLY - SQL_CURSOR_DYNAMIC - SQL_CURSOR_STATIC - SQL_CURSOR_KEYSET_DRIVEN

Default: SQL_CURSOR_FORWARD_ONLY

Different cursor types have different characteristics. See ‘Embedded SQL cursor types’ for more info.

SQLStmtAttribute - cSQLStatement

Function SQLStmtAttribute is now obsolete.

Replaced by SQLGetStmtAttribute.