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 Fortran 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 character 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:
tlist = '%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:
UNIX:
program cursor
## declare
C Declare variables to be used for supplying data
C to the database
## character*25 chvar
## integer intvar
## real*8 rvar
## integer*2 nulind
C Declare variables for the various target lists and
C the arrays of variable addresses
## character*100 delist
## character*100 rtlist
## character*100 rplist
## integer*4 rtvadr(10)
## integer*4 rpvadr(5)
## integer nomore, ingerr
character*10 newpay
nomore = 0
ingerr = 0
## ingres 'personnel'
C Assign values of target lists for DECLARE CURSOR,
C RETRIEVE CURSOR, and REPLACE CURSOR. The second and
C third of these have PARAM clauses. The first doesn't
C need one, as it transfers no data. In the target
C list for RETRIEVE CURSOR, a null indicator
C is included for the floating-point value.
delist = 'employee.empname, employee.age,
employee.salary'
rtlist = '%c, %i4, %f8:%i2'
rplist = 'salary=%f8'
C Assign pointer values to the address array for
C the RETRIEVE CURSOR statement.
rtvadr(1) = IIstr(chvar)
rtvadr(2) = IInum(intvar)
rtvadr(3) = IInum(rvar)
rtvadr(4) = IInum(nulind)
## declare cursor cursor1 For Retrieve (delist)
## For Direct Update of (salary)
## open cursor cursor1
10 continue
if ((ingerr .eq. 0) .and. (nomore .eq. 0)) then
## retrieve cursor cursor1 (param(rtlist, rtvadr))
## inquire ingres (ingerr = ERRORNO, nomore = ENDQUERY)
C If an Ingres error occurred, or if no more rows
C found for the cursor, break loop.
if ((ingerr .eq. 0) .and. (nomore .eq. 0)) then
C If salary for this record is null, print name and age,
C prompt the user to enter the salary, and replace the
C value in that row. If salary is not null, print name,
C age, and salary.
if (nulind .eq. -1) then
print *, chvar, intvar
write (*,50)
50 format (' Enter Salary: ',$)
accept 51, rvar
51 format (f8.2)
if (rvar .gt. 0) then
rpvadr(1) = IInum(rvar)
## replace cursor cursor1 (param
(rplist, rpvadr))
endif
else
print *, chvar, intvar, rvar
endif
else if (ingerr .eq. 1) then
print *, 'Error occurred, exiting ...'
else if (nomore .eq. 1) then
print *, 'No more rows'
endif
goto 10
## close cursor cursor1
## exit
end
VMS:
program param_cursor
## declare
C Declare variables to be used for supplying data
C to the database
## character*25 ch_var
## integer int_var
## real*8 real_var
## integer*2 null_ind
C Declare variables for the various target lists and
C the arrays of variable addresses
## character*100 decl_cursor_list
## character*100 ret_cursor_list
## character*100 repl_cursor_list
## integer*4 ret_varaddr(10)
## integer*4 repl_varaddr(5)
## integer thatsall, ingerror
character*10 newsalary
thatsall = 0
ingerror = 0
## ingres 'personnel'
C Assign values of target lists for DECLARE CURSOR,
C RETRIEVE CURSOR, and REPLACE CURSOR. The second and
C third of these have PARAM clauses. The first doesn't
C need one, as it transfers no data. In the target
C list for RETRIEVE CURSOR, a null indicator is
C included for the floating-point value.
decl_cursor_list
='employee.empname,employee.age,employee.salary'
ret_cursor_list = '%c, %i4, %f8:%i2'
repl_cursor_list = 'salary=%f8'
C Assign pointer values to the address array for
C the RETRIEVE CURSOR statement.
ret_varaddr(1) = IIdesc(ch_var)
ret_varaddr(2) = %loc(int_var)
ret_varaddr(3) = %loc(real_var)
ret_varaddr(4) = %loc(null_ind)
## declare cursor cursor1 for retrieve
## (decl_cursor_list)
## for direct update of (salary)
## OPEN CURSOR cursor1
do while ((ingerror .eq. 0) .and. (thatsall .eq. 0))
## retrieve cursor cursor1 (Param(ret_cursor_list,
## ret_varaddr))
## inquire ingres (ingerror = ERRORNO,
## thatsall = ENDQUERY)
C If an Ingres error occurred, or if no more rows
C found for the cursor, break loop.
if ((ingerror .eq. 0) .and. (thatsall .eq. 0)) then
C If salary for this record is null, print name and
C age, prompt the user to enter the salary, and
C replace the value in that row. If salary is not
C null, print name, age, and salary.
if (null_ind .eq. -1) then
print *, ch_var, int_var
write (*,50)
50 format (' Enter Salary: ',$)
accept 51, real_var
51 format (f8.2)
if (real_var .gt. 0) then
repl_varaddr(1) = %loc(real_var)
## REPLACE CURSOR cursor1 (PARAM
(repl_cursor_list, repl_varaddr))
endif
else
print *, ch_var, int_var, real_var
endif
else if (ingerror .eq. 1) then
print *, 'Error occurred, exiting ...'
else if (thatsall .eq. 1) then
print *, 'No more rows'
endif end do
## close cursor cursor1
## exit
end
Windows:
program param_cursor
## declare
C Declare variables to be used for supplying data
C to the database
## character*25 ch_var
## integer int_var
## real*8 real_var
## integer*2 null_ind
C Declare variables for the various target lists and
C the arrays of variable addresses
## character*100 decl_cursor_list
## character*100 ret_cursor_list
## character*100 repl_cursor_list
## integer*4 ret_varaddr(10)
## integer*4 repl_varaddr(5)
## integer thatsall, ingerror
character*10 newsalary
thatsall = 0
ingerror = 0
## ingres 'personnel'
C Assign values of target lists for DECLARE CURSOR,
C RETRIEVE CURSOR, and REPLACE CURSOR. The second and
C third of these have PARAM clauses. The first doesn''t
C need one, as it transfers no data. In the target
C list for RETRIEVE CURSOR, a null indicator is
C included for the floating-point value.
decl_cursor_list ='employee.empname,employee.age,employee.salary'
ret_cursor_list = '%c, %i4, %f8:%i2'
repl_cursor_list = 'salary=%f8'
C Assign pointer values to the address array for
C the RETRIEVE CURSOR statement.
ret_varaddr(1) = IIdesc(ch_var, 20, LEN(ch_var))
ret_varaddr(2) = %loc(int_var)
ret_varaddr(3) = %loc(real_var)
ret_varaddr(4) = %loc(null_ind)
## declare cursor cursor1 for retrieve
## (decl_cursor_list)
## for direct update of (salary)
## OPEN CURSOR cursor1
do while ((ingerror .eq. 0) .and. (thatsall .eq. 0))
## retrieve cursor cursor1 (Param(ret_cursor_list,
## ret_varaddr))
## inquire_ingres (ingerror = ERRORNO,
## thatsall = ENDQUERY)
C If an Ingres error occurred, or if no more rows
C found for the cursor, break loop.
if ((ingerror .eq. 0) .and. (thatsall .eq. 0)) then
C If salary for this record is null, print name and
C age, prompt the user to enter the salary, and
C replace the value in that row. If salary is not
C null, print name, age, and salary.
if (null_ind .eq. -1) then
print *, ch_var, int_var
write (*,50)
50 format (' Enter Salary: ',$)
accept 51, real_var
51 format (f8.2)
if (real_var .gt. 0) then
repl_varaddr(1) = %loc(real_var)
## REPLACE CURSOR cursor1 (PARAM
## (repl_cursor_list, repl_varaddr))
endif
else
print *, ch_var, int_var, real_var
endif
else if (ingerror .eq. 1) then
print *, 'Error occurred, exiting ...'
else if (thatsall .eq. 1) then
print *, 'No more rows'
endif
end do
## close cursor cursor1
## exit
end
