Processing cursor results

A cursor row result set is generated when an application executes a Client-Library cursor open command.

NoteA cursor row result set is not generated when an application executes a language command containing a Transact-SQL open statement. The open statement opens an Adaptive Server Enterprise language cursor, which returns regular rows each time the application executes a Transact-SQL fetch statement. See “Language cursors versus Client-Library cursors”.

A cursor row result set contains zero or more rows of tabular data.

In general, when an application sends a command to a server, it cannot send another command on the same connection until ct_results indicates that the results of the first command have been completely processed (by returning CS_END_RESULTS, CS_CANCELED, or CS_FAIL).

An exception to this rule occurs when ct_results indicates cursor results. In this case, an application can call ct_cursor and ct_send to send cursor-update, cursor-delete, or cursor-close commands while processing the cursor result set. Using a a different CS_COMMAND structure, the application can also send new commands over the same connection to the server. See “Benefits of Client-Library cursors”.

In addition to ct_res_info, ct_describe, ct_bind, and ct_fetch, an application can call ct_keydata, ct_cursor, ct_param, ct_send, ct_results, and ct_cancel while processing a cursor result set.

Most synchronous applications use a program structure similar to the following one to process a cursor result set:

case CS_CURSOR_RESULT
ct_res_info(CS_NUMDATA) to get the number of columns
     for each column:
         ct_describe to get a description of the column
         ct_bind to bind the column to a program variable
     end for
    while ct_fetch returns CS_SUCCEED or CS_ROW_FAIL 
           and cursor has not been closed
         if CS_SUCCEED
             process the row
         else if CS_ROW_FAIL
             handle the row failure
         end if
       /* For update or delete only:                  */
         if target row is not the row just fetched
             ct_keydata to specify the target row key
         end if
         /* End for update or delete only               */
       /* To send a nested cursor update, delete, or
close command: */
         ct_cursor to initiate the cursor command
         /* For updates/deletes whose “where” clause
contains variables */
         ct_param or ct_setparam for each parameter
         /* End for updates/deletes whose ... */
         ct_send to send the command
         while ct_results returns CS_SUCCEED
             (...process results...)
         end while
         /* End to send a nested cursor command */
   end while
    switch on ct_fetch’s final return code
         case CS_END_DATA...
         case CS_CANCELED...
         case CS_FAIL...
     end switch
    if cursor was closed
         break out of outer ct_results loop
     end if
 
 end case

Calls to ct_results are nested within a ct_fetch loop and a larger ct_results loop (not shown).

For nested cursor-update or cursor-delete commands, after the inner ct_results indicates that the results from the nested command have been completely processed (by returning CS_END_RESULTS, CS_FAIL, or CS_CANCELED), any subsequent calls to ct_results will operate on results generated by the original cursor command.

For nested cursor-close commands, there are no results remaining after the cursor is closed. In this case, the application breaks out of the outer ct_results loop after the results of the nested cursor-close command have been processed.

To cancel the cursor rows returned by the cursor-open command, an application can call ct_cancel with type as CS_CANCEL_CURRENT. However, it is more efficient to close the cursor with a nested cursor-close command. A CS_CANCEL_CURRENT ct_cancel call retrieves the unwanted rows and discards them. (It is equivalent to clearing all binds, then calling ct_fetch until ct_fetch returns CS_END_DATA.)

NoteIn your cursor application, do not use any other type of cancel besides CS_CANCEL_CURRENT on a connection that has an open cursor—CS_CANCEL_ALL or CS_CANCEL_ATTN can put a connection’s cursors into an undefined state. Instead of canceling, the application can simply close the cursor.