Using cursors with SQL Connection

This topic includes the following sections:

 

SQL Connection uses two types of cursor: database cursors and a special SQL Connection mechanism called logical cursors. A database cursor is a processed SQL statement (one that the database has parsed, optimized, and so forth—i.e., a cached execution plan) and/or the database mechanism for traversing and maintaining a position on a row in the result set. Logical cursors, on the other hand, are SQL Connection mechanisms for accessing cursors, including soft-closed cursors. You’ll allocate logical cursors when you initialize a database channel (%SSC_INIT), but otherwise, consider all discussions of cursors in this manual to be discussions of database cursors. (Behind the scenes, however, SQL Connection uses logical cursors to streamline data access.)

With SQL Connection, you interact with cursors by

Closing cursors

Reusing cursors can improve performance, but cursors take up resources that you’ll generally want to release as soon as possible. So 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 and 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:

Cursor types

The options argument for %SSC_OPEN and some of the %SSC_CMD options enable you to create a number of different cursor types (such as static, dynamic, and read-only), though not all databases support all of these types. Additionally, in some cases a cursor can be a scrolling cursor, which enables you to determine which row will be retrieved with the next fetch (by setting an SSQL_CMD_SCROLL option in a call to %SSC_CMD). Non-scrolling cursors, on the other hand, always retrieve the next row. Supported cursor types are briefly discussed below, but see your database documentation for more information, and see Specifying a cursor type below for information on how to select the cursor type for a statement. Note the following:

Forward-only

Forward-only cursors are generally the most efficient cursors for read operations because for some databases, an entire result set may be cached on the client or in a network buffer. (Changes made to the database after the result set is established are not reflected in the result set.) Additionally, forward-only cursors

Dynamic

Dynamic (also known as sensitive) is a SQL Server cursor type that reflects all changes made to the database by other users. 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 forward‑only cursors. Additionally, dynamic cursors

Keyset-driven

This is a SQL Server cursor type that is updated only with changes made to non-key columns in rows that existed when the statement was executed (not rows inserted after the result set was established). Keyset-driven cursors are usually the least efficient cursors. Additionally, keyset-driven cursors

Static

Static (also known as insensitive) is a SQL Server cursor type that does not reflect any changes made to the database after the result set was established. Static cursors are the most efficient scrolling cursors. Additionally, static cursors

Specifying a cursor type

To specify the type of cursor you want, use cursor options for %SSC_OPEN, %SSC_CMD, or both.

To create a forward-only cursor (a static cursor that cannot be made to scroll), call %SSC_CMD with the SSQL_CURSOR_FORWARD_ONLY option (or the SSQL_CURSOR_DEFAULT option) before calling %SSC_OPEN. Then make sure the %SSC_OPEN call does not include any of the SSQL_SCROLL options. Note the following:

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