Cursor Types in Embedded SQL

DataFlex SQL drivers support changing the cursor type for an embedded SQL statement.

For SQL Server this allows to bypass 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 that is 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 the time.

There are different cursor implementations that have different 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, variable length columns must 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 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

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:

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

SQL_CURSOR_STATIC

Static cursors always return the results 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 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.