Declares a cursor where the SELECT statement is supplied at run
time in a parameter.
Refer to the DECLARE CURSOR for a detailed description of
statement elements that apply to both dynamic and nondynamic
DECLARE CURSOR statements.
1 – Environment
You can use the dynamic DECLARE CURSOR statement:
o Embedded in host language programs to be precompiled
o As part of the DECLARE statement section in an SQL module
2 – Format
DECLARE <cursor-name> ----------------------+
+-------------------------------------------+
+-+-+----------------+-> TABLE CURSOR -+----------------+-+
| +-> INSERT ONLY -+ +-> with-clause -+ |
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +-----------------------------------------------------+
| +-> FOR -> <statement-name> ------------------------+-->
++--------------++----------++-> LIST CURSOR ------+ |
+-> READ ONLY -++> SCROLL -+| | |
+-> INSERT ONLY ------------+ | |
+-------------------------------------------------+ |
+-> FOR -> <statement-name> -------------------------+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
3 – Arguments
3.1 – cursor-name
The name of the cursor you want to declare. Use a name that is
unique among all the cursor names in the module. Use any valid
SQL name. See the User_Supplied_Names HELP topic for more
information on identifiers.
3.2 – FOR statement name
A name that identifies a prepared SELECT statement that is
generated at run time.
3.3 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
3.4 – LIST_CURSOR
Specifies that you are declaring a cursor to access the elements
in a list.
3.5 – preserve-clause
Syntax options:
PRESERVE ON COMMIT
PRESERVE ON ROLLBACK
PRESERVE ALL
PRESERVE NONE
Specifies when a cursor remains open.
o PRESERVE ON COMMIT
On commit, all cursors close except those defined with the
WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors
close including those defined with the WITH HOLD PRESERVE ON
COMMIT syntax.
This is the same as specifying the WITH HOLD clause without
any preserve options.
o PRESERVE ON ROLLBACK
On rollback, all cursors close except those defined with the
WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors
close including those defined with the WITH HOLD PRESERVE ON
ROLLBACK syntax.
o PRESERVE ALL
All cursors remain open after commit or rollback. Cursors
close with the CLOSE statement or when the session ends.
o PRESERVE NONE
All cursors close after a CLOSE, COMMIT, or ROLLBACK
statement, when the program stops, or when you exit from
interactive SQL.
This is the same as not specifying the WITH HOLD clause at
all.
3.6 – READ_ONLY
Specifies that the cursor is not used to update the database.
3.7 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random.
3.8 – TABLE_CURSOR
Specifies that you are declaring a cursor to access the rows in a
table.
3.9 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
3.10 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
4 – Examples
Example 1: Using a parameter for a statement name
.
.
.
* This program prepares a statement for dynamic execution from the string
* passed to it, and uses a dynamic cursor to fetch a row from a table.
*
*/
#include <stdio.h>
#include <descrip.h>
struct SQLDA_STRUCT {
char SQLDAID[8];
int SQLDABC;
short SQLN;
short SQLD;
struct {
short SQLTYPE;
short SQLLEN;
char *SQLDATA;
short *SQLIND;
short SQLNAME_LEN;
char SQLNAME[30];
} SQLVAR[];
} *SQLDA;
main()
{
/*
* General purpose locals
*/
int i;
long sqlcode;
char command_string[256];
/*
* Allocate SQLDA structures.
*/
SQLDA = malloc(500);
SQLDA->SQLN = 20;
/* Get the SELECT statement at run time. */
printf("\n Enter a SELECT statement.\n");
printf("\n Do not end the statement with a semicolon.\n");
gets(command_string);
/* Prepare the SELECT statement. */
PREP_STMT( &sqlcode, &command_string, SQLDA );
if (sqlcode != 0)
goto err;
/* Open the cursor. */
OPEN_CURSOR( &sqlcode );
if (sqlcode != 0)
goto err;
/* Allocate memory. */
for (i=0; i < SQLDA->SQLD; i++) {
SQLDA->SQLVAR[i].SQLDATA = malloc( SQLDA->SQLVAR[i].SQLLEN );
SQLDA->SQLVAR[i].SQLIND = malloc( 2 );
}
/* Fetch a row. */
FETCH_CURSOR( &sqlcode, SQLDA );
if (sqlcode != 0)
goto err;
/* Use the SQLDA to determine the data type of each column in the row
and print the column. For simplicity, test for only two data types.
CHAR and INT. */
for (i=0; i < SQLDA->SQLD; i++) {
switch (SQLDA->SQLVAR[i].SQLTYPE) {
case SQLDA_CHAR; /* Character */
printf( "%s", SQLDA->SQLVAR[i].SQLDATA );
break;
case SQLDA_INTEGER: /* Integer */
printf( "%d", SQLDA->SQLVAR[i].SQLDATA );
break;
default:
printf( "Some other datatype encountered\n");
}
}
/* Close the cursor. */
CLOSE_CURSOR( &sqlcode );
ROLLBACK(&sqlcode );
return;
.
.
.
}
Example 2: SQL module file that the preceding program calls
-- This program uses dynamic cursors to fetch a row.
--
--
MODULE C_MOD_DYN_CURS
LANGUAGE C
AUTHORIZATION RDB$DBHANDLE
DECLARE ALIAS FOR FILENAME personnel
-- Declare the dynamic cursor. Use a statement name to identify a
-- prepared SELECT statement.
DECLARE CURSOR1 CURSOR FOR STMT_NAME
-- Prepare the statement from a statement entered at run time
-- and specify that SQL write information about the number and
-- data type of select list items to the SQLDA.
PROCEDURE PREP_STMT
SQLCODE
COMMAND_STRING CHAR (256)
SQLDA;
PREPARE STMT_NAME SELECT LIST INTO SQLDA FROM COMMAND_STRING;
PROCEDURE OPEN_CURSOR
SQLCODE;
OPEN CURSOR1;
PROCEDURE FETCH_CURSOR
SQLCODE
SQLDA;
FETCH CURSOR1 USING DESCRIPTOR SQLDA;
PROCEDURE CLOSE_CURSOR
SQLCODE;
CLOSE CURSOR1;
PROCEDURE ROLLBACK
SQLCODE;
ROLLBACK;