You can declare cursors on a prepared dynamic SQL statement that executes a single select statement. For example, you could prepare a statement to execute the select statement below:
SELECT title_id, title, price FROM titles
WHERE total_sales > ? FOR READ ONLY
The “?” character (the dynamic parameter marker) is a placeholder for a parameter value that will be provided when the cursor is opened. Dynamic SQL statements are created by sending a ct_dynamic(CS_PREPARE) command to the server and handling the results. See “Step 1: Prepare the statement” for details.
After preparing the statement, the application can call ct_dynamic with type as CS_CURSOR_DECLARE and id as the statement identifier.
Use the for read only or for update of clauses in the select statement to specify which columns, if any, to be updated. If the statement does not have one of these clauses, the application can call ct_cursor(CS_CURSOR_OPTION) immediately after calling ct_dynamic to initiate the cursor-declare command.
You cannot batch the ct_dynamic cursor-declare command c with ct_cursor cursor-rows or ct_cursor cursor-open commands.
The following example fragment shows how to declare and open a cursor with a prepared statement:
/*
** Prepare the statement.
*/
strcpy(body, “SELECT title_id, title, price FROM titles
WHERE price > ? FOR READ ONLY”);
strcpy(stmt_id, "dyn_a");
retcode = ct_dynamic(cmd, CS_PREPARE, stmt_id, CS_NULLTERM,
body, CS_NULLTERM);
if (retcode != CS_SUCCEED)
{
ex_error("DoCursor: ct_dynamic(prepare) failed");
return retcode;
}
if ((retcode = ct_send(cmd)) != CS_SUCCEED)
{
ex_error("DoCursor: ct_send() failed");
return retcode;
}
... ct_results() loop goes here. No fetchable results are
returned ...
/*
** Declare the cursor
*/
retcode = ct_dynamic(cmd, CS_CURSOR_DECLARE,
stmt_id, CS_NULLTERM,
"cursor_a", CS_NULLTERM);
if (retcode != CS_SUCCEED)
{
ex_error("DoCursor: ct_dynamic(cursor declare) failed");
return retcode;
}
if ((retcode = ct_send(cmd)) != CS_SUCCEED)
{
ex_error("DoCursor: ct_send() failed");
return retcode;
}
... ct_results() loop goes here. No fetchable results are
returned by the cursor-declare command ...
