Declaring a cursor to directly execute a select statement

To create a cursor or scrollable cursor that directly executes a select statement, call ct_cursor with type as CS_CURSOR_DECLARE and text as a select statement.

A simple cursor declaration

The following code declares a Client-Library cursor. Return code checking is omitted for simplicity:

CS_CHAR body[1024];
strcpy(body, “select * from titles for read only”);
 ret = ct_cursor(cmd, CS_CURSOR_DECLARE, 
          “a cursor”, CS_NULLTERM, 
          body, CS_NULLTERM, CS_UNUSED);

The following code declares a Client-Library scrollable cursor. Return code checking is omitted for simplicity:

CS_CHAR body[1024];
strcpy(body, “select * from titles”);
 ret = ct_cursor(cmd, CS_CURSOR_DECLARE, 
          “s cursor”, CS_NULLTERM, 
          body, CS_NULLTERM, CS_SCROLL_CURSOR);

Declaring a cursor that takes parameters

The select statement can also contain host language variables of the form @variable_name to indicate where parameters will be substituted in the statement when the cursor is opened. Adaptive Server Enterprise allows variables to substitute for values in the cursor’s where clause. For example, the following statement could be used to declare a cursor that takes a variable int value:

SELECT title_id, title, price FROM titles 
 WHERE total_sales > @sales_val

In this case, you must specify the parameter format by calling ct_param or ct_setparam with a NULL data pointer after declaring the cursor. Each time the cursor is opened, the application supplies parameter values by calling ct_param or ct_setparam again. This case is demonstrated by the example below:

 CS_CHAR     body[1024];
 CS_DATAFMT  intfmt;
 CS_INT      sales_val;
  strcpy(body, “select title_id, title, price from 
       titles where total_sales > @sales_val 
       for read only”);
 ret = ct_cursor(cmd, CS_CURSOR_DECLARE, 
          “a cursor”, CS_NULLTERM, 
          body, CS_NULLTERM, CS_UNUSED);
 ... error checking deleted ...
(CS_VOID)memset(&intfmt, 0,  sizeof(intfmt));
/*
 ** Define the format of @sales_val.
 */
 intfmt.datatype = CS_INT_TYPE;
 intfmt.maxlength = CS_SIZEOF(CS_INT);
 intfmt.status = CS_INPUTVALUE;
ret = ct_param(cmd, &intfmt, (CS_VOID *)NULL,
              CS_UNUSED, 0);
 ... error checking deleted ...
ret = ct_cursor(cmd, CS_CURSOR_OPEN, NULL,
               CS_UNUSED, NULL, CS_UNUSED,
               CS_UNUSED);
 ... error checking deleted ...
/*
 ** Supply a value for @sales_val. intfmt fields
 ** were set above.
 */
sales_val = 1;
 ret = ct_param(cmd, &intfmt, 
              (CS_VOID *)&sales_val, CS_UNUSED, 0);
 ... error checking deleted ...
/*
 ** Send the batched cursor declare and open
 ** commands.
 */
 ret = ct_send(cmd);
 ... error checking deleted ...

Specifying which columns can be updated

For applications that connect to Adaptive Server Enterprise, use the for read only or for update of clauses in the select statement to specify which columns, if any, will be updated. In the ct_cursor(CS_CURSOR_DECLARE) call, pass the ct_cursor option parameter as CS_UNUSED to indicate that the server should decide which columns can be updated. For example, a cursor declared with this following statement allows updates of the price column:

SELECT title_id, title, price FROM titles 
 FOR UPDATE OF price

Other servers, such as custom Open Servers, may not recognize or use the for read only or for update of clauses in the select statement. These servers require the client application to indicate which columns are to be updated with separate calls to ct_param or ct_setparam. For details, see the reference page for ct_cursor in the Open Client Client-Library/C Reference Manual.