Skip to content

Cursor Types in Embedded SQL

DataFlex SQL drivers support changing the cursor type for an embedded SQL statement. For SQL Server, this allows bypassing the ‘variable length columns must be last in the select’ rule when using embedded SQL statements.

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, each with distinct 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 DataFlex SQL drivers 6.3 and later support setting the cursor type of an embedded SQL statement. The cursor type of an embedded SQL statement can be set with SQLSetStmtAttribute using SQLSTMTATTRIB_CURSOR_TYPE 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 Values

The 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

Cursor Type Characteristics

SQL_CURSOR_FORWARD_ONLY

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 and will fetch changes made by others.

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

SQL_CURSOR_STATIC

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.

SQL_CURSOR_DYNAMIC

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.

SQL_CURSOR_KEYSET

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 the 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 or applications.