Using cursors with SQL Connection

This topic includes the following sections:

 

SQL Connection uses three types of cursor:

With SQL Connection, you interact with a cursor (a statement or database cursor) by doing the following:

For SQL Server there are several database cursor types, which are discussed in SQL Server database cursor types below.

For databases other than SQL Server, database cursors are generally forward-only cursors, which cannot scroll (i.e., they only support fetching rows sequentially from the start to the end of the result set). With these databases, forward-only cursors can be used for updates, though when using VTX11 or VTX12_SQLNATIVE we recommend setting cursors to read-only (by setting the %SSC_CMD option SSQL_RO_CURSOR to "yes") whenever possible for improved performance.

Note the following:

SQL Server database cursor types

To create a cursor for SQL Server, SQL Connection sends ODBC API commands to the SQL Server ODBC client. SQL Server then creates the database cursor based on these commands. The following summarizes how SQL Connection works with SQL Server cursors. See SQL Server documentation for details.

For SQL Server, there are several cursor types: forward-only, dynamic, keyset-driven, and static. By default, SQL Connection creates dynamic database cursors for SQL Server (this is for backward compatibility), but we recommend that you use read-only forward-only cursors unless you want to update data. To determine which cursor type is in use for a statement, use ODBC Driver Manager trace logging; see ODBC trace logging (Windows).

The %SSC_CMD and %SSC_OPEN routines enable you to set the cursor type and to set cursor scrolling options for certain cursor types. Scrolling options enable you to determine which row will be retrieved with the next fetch. (Non-scrolling database cursors always retrieve the next row.)

Forward-only

A forward-only cursor supports fetching rows sequentially from the start to the end of the result set. Forward-only cursors are generally the most efficient cursors for read operations. In some cases, an entire result set may be cached on the client or in a network buffer, so changes made to the database after the result set is established are not reflected in the result set. We recommend using forward-only cursors whenever possible. For SQL Server, these cursors

To create a forward-only cursor, call %SSC_CMD with the SSQL_CURSOR_FORWARD_ONLY and SSQL_RO_CURSOR options before calling %SSC_OPEN. Make sure the %SSC_OPEN call does not include an SSQL_SCROLL option.

Note that forward-only cursors generally don’t support multiple concurrently active statements because SQL Server automatically returns a default result set. This default result set must be processed before another statement can be issued, which limits the client to one active SQL statement at a time. (If you attempt to issue an SQL statement while a previous statement is still active, you may get a "Connection is busy with results for another hstmt" error.) This may not be a limitation when using multiple active result sets (MARS).

Dynamic

Dynamic (also known as "sensitive") is a SQL Server cursor type that reflects all changes made to the rows in its result set by other users while the cursor is open. The data values, order, and rows included in the result set can change with each fetch.

A dynamic cursor may be better than a forward-only cursor for a large result set if only part of the result set will be read, or if the result set is too large for the network buffer used for a forward-only cursor. Additionally, dynamic cursors

Dynamic is the default SQL Server cursor type for SQL Connection (for backward compatibility), but we recommend using forward-only read-only cursors whenever possible. If you use a dynamic cursor, be sure to change the cursor type back to forward-only as soon as possible.

SQL Server uses pessimistic locking when using dynamic cursors and the UPDLOCK and ROWLOCK hints. See Row locking for more information.

To create a dynamic cursor, do one of the following:

Keyset-driven

Keyset-driven cursors reflect changes made by other users to non-key columns. Row membership (rows included in the result set), order of rows, and key columns are fixed when the cursor is opened and remain unchanged for the life of the cursor. Keyset-driven cursors are usually the least efficient cursors. These cursors

To create a keyset-driven cursor, do one of the following:

If you use a keyset-driven cursor, be sure to change the cursor type back to forward-only as soon as possible.

Static

Static (also known as insensitive) is a SQL Server cursor type that displays the result set as it was when the cursor was opened. A static cursor does not reflect changes made to the database after the result set was established. Static cursors

Each static cursor is built as a work table in tempdb when the cursor is opened (before the first row is returned), so the size of the cursor result set cannot exceed the maximum row size allowed by SQL Server.

To create a static cursor, do one of the following:

If you use a static cursor, be sure to change the cursor type back to forward-only as soon as possible.

Important notes on SQL Server cursors

Closing cursors

Reusing cursors can improve performance, but cursors take up resources that you’ll generally want to release as soon as possible. SQL Connection includes two ways to close cursors: soft closing (%SSC_SCLOSE) and hard closing (%SSC_CLOSE).

In general, consider soft closing the cursor if your program will redo the same operation soon or frequently, or if your program just retrieves one row or keeps retrieving until the database has no more data for the statement. See Reusing cursors for more information.

Reusing cursors

For optimal performance, reuse a cursor if the statement will be reused soon or frequently. Cursor reuse can significantly improve database and network performance. It saves time opening the cursor, and it uses less memory. SQL Connection enables you to reuse cursors if

In other words, reuse a cursor if you are processing the same SQL statement several times with the same or different bind data.

In brief, these are the steps that an SQL Connection application and the database take to process an SQL statement if you don’t reuse a cursor:

1. Open cursor.
2. Process the SQL statement:
3. Bind parameters.
4. Execute the statement (e.g., fetch data).
5. Close the cursor.

When a cursor is reused, however, the application skips the initial step of processing the SQL statement. This alone saves a great deal of overhead since initial processing is typically a very expensive process, using as much as 10 times the resources used for other steps. The reused cursor rebinds only the variables containing new data and eliminates the need for re-parsing the entire statement. You can then fetch new data and rebind the host variables as many times as necessary.

SQL Connection reuses cursors in conjunction with the %SSC_OPEN, %SSC_SCLOSE (as mentioned in Closing cursors above), %SSC_REBIND, %SSC_EXECUTE, and %SSC_EXECIO functions. For an example, see the example section for %SSC_CLOSE.

Note the following: