Param Versions of Cursor Statements
There are param versions for cursor versions of the retrieve and replace statements. In the case of the cursor retrieve, the param target list is used in the retrieve cursor statement, not in the declare cursor statement. The non-param retrieve cursor target list is simply a comma-separated list of C variables corresponding to the result columns identified in the declare cursor statement. Therefore, the target string in the param version is a comma-separated list of type indicators, optionally with associated type indicators for the null indicator variables.
When you code the declare cursor statement for use with the param version of retrieve cursor, you should take advantage of the fact that the entire target list in declare cursor can be replaced by a host string variable. This, in effect, allows the whole retrieve statement in declare cursor to be determined at runtime. Then, the components of the param retrieve cursor can be built dynamically for the associated declare cursor statement.
The target string for a retrieve cursor statement might look something like the following:
targlist = "%c:%i2,%f8:%i2";
This target list is appropriate for a retrieve cursor where the associated declare cursor retrieved two nullable columns—one character string and one floating-point value.
The replace cursor statement also supports a param version. Its target list looks the same as in the non-cursor version of replace.
The following is a somewhat expanded example, showing both the declare cursor, retrieve cursor, and replace cursor:
# include <stdio.h>
main()
{
double atof();
/*
** Declare variables to be used for supplying
** data to the database
*/
## char ch_var[27];
## int int_var;
## double doub_var;
## short null_ind;
/*
** Declare variables for the various target
** lists and the arrays of variable addresses
*/
## char decl_cursor_list[100];
## char ret_cursor_list[100];
## char repl_cursor_list[100];
## char *ret_varaddr[10];
## char *repl_varaddr[5];
## int thatsall, ingerror;
char newsalary[20];
thatsall = ingerror = 0;
## ingres "personnel"
/*
** Assign values of target lists for DECLARE CURSOR,
** RETRIEVE CURSOR, and REPLACE CURSOR. The second and
** third of these have PARAM clauses. The first
** doesn't need one, as it transfers no data. In the
** target list for RETRIEVE CURSOR, a null indicator
** is included for the floating-point value.
*/
strcpy (decl_cursor_list,
"employee.empname,employee.age,employee.salary");
strcpy (ret_cursor_list, "%c26, %i4, %f8:%i2");
strcpy (repl_cursor_list, "salary=%f8");
/*
** Assign pointer values to the address array
** for the RETRIEVE CURSOR statement.
*/
ret_varaddr[0] = (char *) ch_var;
ret_varaddr[1] = (char *) &int_var;
ret_varaddr[2] = (char *) &doub_var;
ret_varaddr[3] = (char *) &null_ind;
## declare cursor cursor4 for
## retrieve (decl_cursor_list)
## for direct update of (salary)
## open cursor cursor4
while (ingerror == 0 && thatsall == 0)
{
## retrieve cursor cursor4 (param(ret_cursor_list,
## ret_varaddr))
## inquire_ingres (ingerror = errorno,
## thatsall = endquery)
/*
** If an Ingres error occurred, or if no
** more rows found for the cursor, break loop
*/
if (ingerror 0)
{
printf ("Error occurred, exiting ...\n");
break;
}
if (thatsall == 1)
{
printf ("No more rows\n");
break;
}
/* If salary for this record is null, print name
** and age, prompt the user to enter the salary,
** and replace the value in that row. If salary
** is not null, print name, age, and salary.
*/
if (null_ind == -1)
{
printf ("%s, %d\n", ch_var,int_var);
printf ("Enter Salary: ");
gets (newsalary);
doub_var = atof(newsalary);
if (doub_var 0)
{
repl_varaddr[0] = (char *) &doub_var;
## replace cursor cursor4
## (param(repl_cursor_list,repl_varaddr))
}
}
else
{
printf ("%s, %d, %10.2f\n", ch_var, int_var,
doub_var);
}
} /* end "while" loop */
## close cursor cursor4
## exit
exit (0);
}
Last modified date: 01/30/2023