Open Client Client-Library/C Reference Manual

Home

Open Client Client-Library/C Reference Manual

Chapter 3 Routines

ct_cursor

Usage

Summary of ct_cursor parameters
Value of type	Command initiated	name value	text value	option value
CS_CURSOR_ DECLARE	A cursor declare command.	A pointer to the cursor name.	A pointer to the SQL text that is the body of the cursor.	CS_UNUSED, or a bitwise OR of the values in Table 3-15.
CS_CURSOR_ OPTION	A cursor set options command.	NULL	NULL	CS_FOR_UPDATE to indicate that the cursor is "for update." CS_READ_ONLY to indicate that the cursor is "read-only." CS_UNUSED to indicate that the server should decide whether a cursor is updatable. See Specifying updatability on page 373 for more information.
CS_CURSOR_ROWS	A cursor set rows command.	NULL	NULL	An integer representing the number of rows to be returned with a single fetch request.
CS_CURSOR_OPEN	A cursor open command.	NULL	NULL	CS_RESTORE_OPEN restores parameter-binding information for a previously sent cursor-open command. See "Restoring a cursor-open command" for an explanation. CS_UNUSED should be passed the first time a cursor is opened.
CS_CURSOR_ UPDATE	A cursor update command.	A pointer to the name of the table to update.	A pointer to the SQL update statement.	CS_UNUSED if text is the entire update statement. CS_MORE if text is part of the update statement. CS_END if *text is the last piece of the update statement.
CS_CURSOR_ DELETE	A cursor delete command.	A pointer to the name of the table to delete from.	NULL	CS_UNUSED
CS_CURSOR_ CLOSE	A cursor close command.	NULL	NULL	CS_DEALLOC to close and deallocate the cursor. CS_UNUSED to close the cursor without deallocating it.
CS_CURSOR_ DEALLOC	A deallocate cursor command.	NULL	NULL	CS_UNUSED
CS_IMPLICIT_CURSOR				cursor is marked read only. new rows inserted after last row fetch are not seen by subsequent fetches.

Initiating a command is the first step in sending it to a server. Client-Library cursor commands include commands to declare, open, set cursor rows, close, and deallocate a cursor as well as commands to update and delete rows in an underlying table. Chapter 7, "Using Client-Library Cursors," in the Open Client Client-Library/C Programmer's Guide contains additional information on Client-Library cursors.
To send a cursor command to a server, an application must:
1. Initiate the command by calling ct_cursor. This sets up internal structures that are used in building a command stream to send to the server.
2. Pass parameters for the command (if required) by calling ct_param or ct_setparam once for each parameter that the command requires.
3. Cursor-declare, cursor-open, and cursor-update commands may require parameters. Other cursor commands do not.
4. Send the command to the server by calling ct_send.
5. Handle the results of the command by calling ct_results until it returns CS_END_RESULTS, CS_CANCELED, or CS_FAIL. A cursor-open command returns a CS_CURSOR_RESULT result type (and possibly other result types indicating status information). Other cursor commands do not return fetchable results, but they do return result types that indicate command status. See "Results" for a discussion of processing results.
Client-Library allows an application to resend commands by calling ct_send immediately after the results of the previous execution have been processed. An application can resend any command that was initiated with ct_cursor. However, only cursor-update and cursor-delete commands can be reexecuted successfully on the server. Other cursor commands must be executed in a specific sequence and resending them can cause server processing errors.

Sequencing cursor commands

Servers require cursor commands to be executed in the sequence described below. Each step below is a separate server command which generates distinct results:
1. Declare the cursor. This step identifies the source query for the cursor and optionally identifies which (if any) columns in the cursor's result set can be updated. Cursors can be declared with ct_cursor or ct_dynamic. ct_cursor details for this step are under "Cursor-declare commands". For ct_dynamic cursor declarations, see "Declaring a cursor on a prepared statement".
2. Specify cursor options (only for cursors declared with ct_dynamic). For details, see "Dynamic SQL cursor option".
3. Specify the cursor rows setting. For details, see "Cursor-Rows commands".
4. Open the cursor. The first time a cursor is opened, the commands in steps 1-4 can be batched to reduce the number of network round-trips to the server and back. For details, see "Cursor-open commands" and "Batching cursor-open commands".
5. Process the cursor-open results with ct_results and ct_fetch. Each time ct_fetch returns CS_SUCCEED or CS_ROW_FAIL, the application can issue nested cursor-update or cursor-delete commands on the same CS_COMMAND structure. The application can also send new commands (unrelated to the cursor), as long as the application uses a different CS_COMMAND structure and processes the results of the command before fetching from the cursor again. Results processing is described in "Results". For details on nested cursor commands, see "Cursor-update commands" and "Cursor-delete commands".
6. Close the cursor as described by "Cursor-close commands". Closed cursors can be reopened: steps 3- 6 can be repeated indefinitely. A cursor can be reopened by initiating a new cursor-open command or by restoring the previously initiated cursor-open command. For details, see "Restoring a cursor-open command".
7. Deallocate the cursor. For details, see "Cursor-deallocate commands".

Cursor-declare commands

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 via ct_param or ct_setparam. For execute cursors, the declaration of the stored procedure determines the formats of the procedure's parameters.

Option values for ct_cursor(CS_ CURSOR_DECLARE)

The following values can be passed for the ct_cursor option parameter when initiating a cursor-declare command:

option values for ct_cursor(CS_CURSOR_DECLARE)
Value of option	Meaning
CS_MORE	Indicates that text is only part of the cursor body, with the rest to be supplied in subsequent calls. If this bit is set, all other options are ignored. If this bit is not set, then text is taken to be the entire cursor body.
CS_END	Indicates that *text is the last piece of the cursor body.
CS_FOR_UPDATE	Indicates that the cursor is "for update." Can be used with CS_END or by itself. When this option appears by itself, the entire cursor body must be specified with one call. For Adaptive Server connections, use the for update of or for read only clauses in the cursor body to specify whether the cursor rows can be updated. Adaptive Server does not recognize option values.
CS_READ_ONLY	Indicates that the cursor is read-only. Can be used with CS_END or by itself.
CS_UNUSED	Equivalent to setting the CS_END bit (only).

To build the cursor's text value in pieces, use the CS_MORE and CS_END option values. A sequence of one or more ct_cursor calls that use CS_MORE must be ended with a call that specifies CS_END, as demonstrated below:
```
ct_cursor(cmd, CS_CURSOR_DECLARE, 
```
```
       "select title_id, contract ", ..., CS_MORE);
```
```
 ct_cursor(cmd, CS_CURSOR_DECLARE, 
```
```
        "from titles ", ... , CS_MORE);
```
```
 ct_cursor(cmd,  CS_CURSOR_DECLARE, 
```
```
        "where contract=FALSE ", ...., CS_MORE);
```
```
 ct_cursor(cmd, CS_CURSOR_DECLARE, 
```
```
         "for update of contract", ...., CS_END);
```
The last cursor-declare call must specify CS_END. Note that CS_READ_ONLY and CS_FOR_UPDATE are illegal with CS_MORE. If you need to set either of these option bits, set it in the last call (for example, use CS_END | CS_READONLY).
Client-Library does not add white space when appending the *text values.
The CS_FOR_UPDATE and CS_READ_ONLY options are passed to the server. If neither option is set, then the server decides whether the cursor is updatable based on the content of the cursor body specified by *text.

Specifying updatability

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);
```

Dynamic SQL cursor option

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.

Cursor-Rows commands

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.

Cursor-open commands

A ct_cursor(CS_CURSOR_OPEN) command executes the body of a Client-Library cursor, generating a CS_CURSOR_RESULT result set.
- To access the cursor rows, an application processes the cursor result set by calling ct_results, ct_bind, and ct_fetch.
- While fetching rows in a cursor result set, the application can send nested cursor commands (cursor update, cursor delete, cursor close) using the same CS_COMMAND structure.
- While fetching rows in a cursor result set, the application can also send non-cursor commands to the server (or declare and open another cursor) by using a separate CS_COMMAND structure.
The cursor must have been declared with ct_cursor(CS_DECLARE) or ct_dynamic(CS_CURSOR_DECLARE) before it can be opened. A closed cursor can be reopened.
If the cursor is declared with ct_cursor, the declare and open commands can be batched. For more information, see "Batching cursor-open commands".
Cursors may require parameter values at cursor-open time. An application can pass input parameter values for a cursor-open command by calling ct_param or ct_setparam after calling ct_cursor. A cursor-open command requires parameters if any of the following conditions is true:
- The body of the cursor is a SQL statement that contains host variables.
- The body of the cursor is a stored procedure that requires input parameter values.
- The body of the cursor is a dynamic SQL statement that contains dynamic parameter markers.
To open a cursor on a dynamic SQL prepared statement, specify the same command structure used to dynamically declare the cursor (ct_dynamic(CS_CURSOR_DECLARE)).
The first time a cursor is opened, all the server commands to declare the cursor, set cursor rows, and open the cursor can be sent with a single call to ct_send. For subsequent cursor-open commands, the application can use the CS_RESTORE_OPEN option to eliminate redundant ct_cursor(CS_CURSOR_ROWS) and ct_param calls. For a description of these features, see:
- "Batching cursor-open commands", and
- "Restoring a cursor-open command".
- Text for cursor-open commands can be assembled in pieces with multiple ct_cursor calls. To specify the open statement in pieces, use the CS_MORE and CS_END values for the option parameter.

Batching cursor-open commands

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.

Restoring a cursor-open command

When reopening a cursor, an application can use the CS_RESTORE_OPEN option to restore the most recently sent cursor-open command.
If the application used ct_param to supply parameter values for the original cursor-open command, then the restored cursor-open command will use the same parameter values. If the application used ct_setparam, then the application can change the parameter values for the restored cursor-open command.
If the application batched a cursor-rows command with the previous cursor-open command, then Client-Library resends the cursor-rows command with the cursor-open command. The cursor is reopened with the same cursor-rows setting.

The sequence of calls for restoring a cursor-open command is:

/*
 ** Assign new variables in the program variables

 ** bound with ct_setparam.

*/

  ... assignment statement for each parameter

      source value ...

ct_cursor(CS_CURSOR_OPEN, CS_RESTORE_OPEN)

ct_send

... handle cursor results ...

An application cannot restore a cursor that has been deallocated.
An application can check the CS_HAVE_CUROPEN property to see whether a restorable cursor-open command exists for a command structure. See "Have restorable cursor-open command" for a description.
Applications that restore cursor-open commands may benefit from setting the CS_STICKY_BINDS command property. When CS_TRUE, this property allows the application to reuse the original cursor result bindings and eliminate redundant ct_bind calls. See "Persistent result bindings " for a description of this property.

Cursor-update commands

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.

Cursor-delete commands

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.

Cursor-close 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.

Cursor-deallocate commands

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.


	Expand Search