A cursor row result set is generated when an application executes a Client-Library cursor open command.
A 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.)
In 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.
