ct_cursor

Description

Initiate a Client-Library cursor command.

Syntax

CS_RETCODE ct_cursor(cmd, type, name, namelen, text,
               textlen, option)

CS_COMMAND      *cmd;
 CS_INT                  type;
 CS_CHAR             *name;
 CS_INT                  namelen;
 CS_CHAR             *text;
 CS_INT                  textlen;
 CS_INT                  option;

Parameters

• Declaring a Client-Library cursor is equivalent to associating the cursor name with a select statement. This SQL statement is called the body of the cursor.

• The following rules apply to ct_cursor cursor-declare commands:

  • Only one cursor may be declared for each CS_COMMAND structure. However, another cursor can be declared on a separate CS_COMMAND structure that shares the same connection.

  • All operations on a Client-Library cursor, from its declaration to its deallocation, must reference the command structure with which the cursor was created.

  • When a cursor is declared on a CS_COMMAND structure, the structure can not be used to execute ct_command, ct_dynamic, or ct_sendpassthru server commands until the cursor is deallocated.

  • Cursors associated with a dynamic SQL statement are declared with ct_dynamic, not with ct_cursor.

• The cursor body can either be specified directly as the *text parameter, or indirectly as the text of a stored procedure. In the case of the stored procedure, the *text parameter must be a command to execute the stored procedure. A cursor declared with a stored procedure is called an execute cursor.

  • The following example declares a cursor named title_cursor on rows from the titles table.

        ct_cursor (cmd, CS_CURSOR_DECLARE,
    

             "title_cursor",CS_NULLTERM, 
    

             "select * from titles", CS_NULLTERM,
    

             CS_UNUSED);
    

        ct_send(cmd);
    

  • The following example declares an execute cursor on the stored procedure title_cursor_proc:

        ct_cursor (cmd, CS_CURSOR_DECLARE, 
    

             "mycursor", CS_NULLTERM, 
    

             "exec title_cursor_proc", CS_NULLTERM,
    

             CS_UNUSED);
    

        ct_send(cmd);
    

    In this case, the body of the cursor is the text that makes up the stored procedure. The stored procedure text must contain a single select statement only. In the example above, title_cursor_proc could be created as:

    create proc title_cursor_proc as 
    

       select * from titles for read only
    

    A stored procedure used with an execute cursor must consist of a single select statement. The stored procedure’s return status is not available to the client program. Output parameter values are also not available to the client program.

• A select statement associated with a cursor can contain host variables. If it does, you must describe the format for each variable after declaring the cursor. To describe the format of each host variable, first initialize a CS_DATAFMT structure to describe the variable’s format; then call ct_param with the CS_DATAFMT as a parameter.

  At cursor-declare time, ct_param only provides format information for the host-language variables. At cursor-open time, actual values are provided by calling ct_param with parameter values or ct_setparam with pointers to parameter source variables.

• An execute statement associated with a cursor should not contain host language variables, and you do not need to specify variable formats with ct_param at cursor-declare time. At cursor-open time, supply values for the procedure’s parameters using ct_param or ct_setparam. For execute cursors, the declaration of the stored procedure determines the formats of the procedure’s parameters.

• When declaring a Client-Library cursor, the application must specify whether the cursor is updatable; that is, whether the application intends to update the retrieved cursor rows using ct_cursor update commands. Depending on the destination server, this is done either by the content of the cursor’s body statement or with the ct_cursor option parameter.

  If the server is a Adaptive Server, the select statement associated with the cursor defines whether the table rows can be updated. Applications use the Transact-SQL clauses for update of or for read only to specify whether the cursor is updatable. For example, the statement in the call below specifies that the price column will be updated, and all other columns will not:

  #define TITLE_CUR \
  

   “select title_id, title, price from titles \
  

   for update of price”
  

  ret = ct_cursor(cmd, CS_CURSOR_DECLARE, 
  

            “titles_cursor”, CS_NULLTERM,
  

            TITLE_CUR, CS_NULLTERM, CS_END);
  

  If the server is an Open Server application, it may require that the client use the CS_READ_ONLY and CS_FOR_UPDATE options, or it may parse the select statement. The choice depends on the design of the Open Server application. If the server requires option to determine whether the cursor is updatable, then the ct_cursor usage is as follows:

  • To declare a cursor as “read-only,” an application specifies option as CS_READ_ONLY.

  • To declare a cursor “for update,” an application specifies option as CS_FOR_UPDATE.

    If some of a cursor’s columns are “for update,” an application indicates which columns are “for update” by calling ct_param once for each update column. If all of a cursor’s columns are “for update,” an application does not have to call ct_param to identify the update columns.

    For example, to indicate that the au_id and au_lname columns are “for update”:

        ct_cursor(cmd, CS_CURSOR_DECLARE,
    

    "au_cursor",
    

             CS_NULLTERM, "select * from authors"
    

             CS_NULLTERM, CS_FOR_UPDATE);
    

         format.status = CS_UPDATECOL;
    

         ct_param(cmd, &format, "au_id", 
    

                  CS_NULLTERM, 0);
    

         format.status = CS_UPDATECOL;
    

         ct_param(cmd, &format, "au_lname", 
    

                  CS_NULLTERM, 0);
    

         ct_send(cmd);
    

    To indicate that all columns returned by a cursor are “for update”:

        ct_cursor(cmd, CS_CURSOR_DECLARE,
    

     "au_cursor",
    

                 CS_NULLTERM, "select * from authors"
    

                 CS_NULLTERM, CS_FOR_UPDATE);
    

         ct_send(cmd);
    

• A dynamic SQL application can declare a cursor on a prepared statement. To declare a cursor on a prepared statement, call ct_dynamic(CS_CURSOR_DECLARE); from that point on, use ct_cursor calls to manipulate the cursor.

• The dynamic SQL cursor declare command does not provide a way to specify cursor options. To set options, call ct_cursor(CS_CURSOR_OPTION) after calling ct_dynamic and before calling ct_send.

• If the server is an Adaptive Server, the CS_READ_ONLY and CS_FOR_UPDATE options do not affect the underlying server tables. The select statement associated with the cursor defines whether the table rows can be updated.

• If the server is an Open Server application, the CS_READ_ONLY and CS_FOR_UPDATE options may be used by the server.

  In this case, if some but not all of a cursor’s columns are “for update,” an application must indicate which columns are “for update” by calling ct_param once for each update column. If all of a cursor’s columns are “for update,” an application does not have to call ct_param to identify update columns.

• Cursor options must be specified before the cursor-declare command is sent.

• A ct_cursor(CS_CURSOR_ROWS) command specifies the number of rows that the server returns to Client-Library per internal fetch request. Note that this is not the number of rows returned to an application per ct_fetch call. The number of rows returned to an application per ct_fetch call is determined by the value of the count field in the CS_DATAFMT structures used in binding the cursor result columns.

• An application can set cursor rows only before opening a cursor.

• The cursor rows setting defaults to one row.

You can use implicit cursors with Client-Library version 12.5 and later. Implicit cursors function in the same way as read-only cursors during row-fetching, but they use system resources more efficiently.

This example uses read-only cursors:

ct_cursor(cmd, CS_CURSOR_DECLARE, "cursor_a", CS_NULLTERM, SELECT, CS_READ_ONLY)

ct_cursor(cmd, CS_CURSOR_ROWS, NULL, CS_UNUSED, NULL, CS_UNUSED, CS_INT)5)

ct_cursor(cmd, CS_CURSOR_OPEN, NULL, CS_UNUSED, NULL, CS_UNUSED, CS_UNUSED)

This example uses implicit cursors:

ct_cursor(cmd, CS_CURSOR_DECLARE, "cursor_a", CS_NULLTERM, SELECT, CS_IMPLICIT CURSOR)

ct_cursor(cmd, CS_CURSOR_ROWS, NULL, CS_UNUSED, NULL, CS_UNUSED, CS_INT)5)

ct_cursor(cmd, CS_CURSOR_OPEN, NULL, CS_UNUSED, NULL, CS_UNUSED, CS_UNUSED)

To use implicit cursors, you must set cs_ctx_alloc(CS_VERSION_125, context) or ct_init(*context, CS_VERSION_125). You must set CS_CURSOR_ROWS to a minimum value of 2 for single-row fetches, and a higher value if more rows are to be retrieved.

• When opening a cursor, an application can batch ct_cursor commands to reduce network traffic and improve application performance. All the commands required to declare and open the cursor can be sent with one call to ct_send.

  To batch commands to declare, set rows for, and open a Client-Library cursor, the application:

  1. Calls ct_cursor to declare the cursor

  2. If necessary, calls ct_param or ct_setparam to define the format(s) of host variables.

  3. If desired, calls ct_cursor to set rows for the cursor.

  4. Calls ct_cursor to open the cursor.

  5. If necessary, calls ct_param or ct_setparam to supply value(s) for the host variable(s). The application should use ct_setparam if it will reopen the cursor using the CS_RESTORE_OPEN option. ct_setparam binds program variables to input parameters, allowing the application to change parameter values when resending a command. If the application uses ct_param, the parameter values cannot be changed when the cursor-open command is restored.

  6. Calls ct_send to send the command batch to the server.

     The sequence of calls is:

         ct_cursor(CS_CURSOR_DECLARE)
     

         ct_param or ct_setparam for each parameter
     

         ct_cursor(CS_CURSOR_ROWS)
     

         ct_cursor(CS_CURSOR_OPEN)
     

         ct_param or ct_setparam for each parameter
     

         ct_send
     

         ct_results
     

     Each of the batched commands generates separate results, and several calls to ct_results are required.

• A ct_cursor(CS_CURSOR_UPDATE) command defines new column values for the current cursor row. These new values are used to update an underlying table.

• A cursor update command is always “nested”; that is, the command is sent from within the ct_results loop while the cursor’s rows are being processed by ct_fetch.

  A nested cursor command can be sent after ct_results returns a result_type value of CS_CURSOR_RESULT. At least one row must be fetched before a cursor update command is allowed, and cursor update commands are not allowed after ct_fetch returns CS_END_DATA.

• By default, the last-fetched row is updated. The application can redirect the update to another row in the cursor result set. To redirect the update, specify different key values with ct_keydata before sending the cursor update command.

• When updating an Adaptive Server table, an application must specify the name of the table to update twice: once as the value of ct_cursor’s *name parameter and a second time in the update statement itself (update tablename).

• Text for cursor-open and cursor-update commands can be assembled in pieces with multiple ct_cursor calls. To specify the update statement in pieces, use the CS_MORE and CS_END values for the option parameter. CS_MORE indicates that the application intends to append more text to the update statement. To be specified in pieces, an update statement must update only a single table.

• The text of the update statement can contain host-language variables. If it does, the application must specify values for the variables with ct_param or ct_setparam before calling ct_send. Use ct_setparam if the cursor-update command requires parameters and will be sent to the server more than once.

• A cursor-update command generates results like any other command. The application must process the results before it can fetch from the cursor again.

• Cursor-update commands can be resent by calling ct_send immediately after the results of the previous execution have been handled. A cursor-update command can be resent as long as

  • The application has not initiated a new nested cursor command,

  • The cursor is still open, and

  • ct_fetch has not returned CS_END_DATA.

• A ct_cursor(CS_CURSOR_DELETE) command deletes a row from the cursor result set. The delete is propagated back to the underlying server tables.

• A cursor-delete command is always “nested”; that is, the command is sent from within the ct_results loop while the cursor’s rows are being processed by ct_fetch.

  A nested cursor command can be sent after ct_results returns a result_type value of CS_CURSOR_RESULT. At least one row must be fetched before a cursor delete command is allowed, and cursor delete commands are not allowed after ct_fetch returns CS_END_DATA.

• By default, the last-fetched row is deleted. The application can redirect the deletion to another row in the cursor-result set. To redirect the deletion, specify different key values with ct_keydata before sending the cursor-delete command.

• A cursor-delete command generates results like any other command. The application must process the results before it can fetch from the cursor again.

• Cursor-delete commands can be resent, with the same restrictions as for cursor-update commands.

• A ct_cursor(CS_CURSOR_CLOSE) command abandons the cursor result set that was generated when the cursor was opened. If all the cursor’s rows have been fetched, a cursor-close command must be issued before the application can reopen the cursor.

• An application can reopen a closed cursor.

• A cursor-close command can be “nested ”; that is, a cursor-close command can be sent from within the ct_results loop while the cursor’s rows are being processed by ct_fetch.

  • A nested cursor-close command can be sent after ct_results returns a result_type value of CS_CURSOR_RESULT and before ct_fetch returns CS_END_DATA.

  • After ct_fetch returns CS_END_DATA, the cursor-close command can no longer be nested, and cannot be sent until ct_results has returned CS_END_RESULTS or CS_CANCELED.

A nested cursor-close command is the preferred way to abandon rows returned from a cursor-open command, since ct_cancel can put a connection’s cursors into an undefined state.

• A non-nested cursor-close command must be sent when the CS_COMMAND structure is idle, that is, after ct_results has returned CS_END_RESULTS or CS_CANCELED.

• A ct_cursor(CS_CURSOR_DEALLOC) command deallocates a Client-Library cursor. If a cursor has been deallocated, it cannot be reopened.

• An application cannot deallocate an open cursor.

• To initiate a command to both close and deallocate a Client-Library cursor, call ct_cursor with type as CS_CURSOR_CLOSE and option as CS_DEALLOC.

See also