Declaring a DSQL Cursor

Go Up to Selecting Multiple Rows in DSQL

DSQL must declare a cursor based on a user-defined SELECT statement. Usually, DSQL programs:

• Prompt the user for a query (SELECT).
• Store the query in a host-language variable.
• Issue a PREPARE statement that uses the host-language variable to describe the query results in an XSQLDA.
• Declare a cursor using the query alias.

The complete syntax for DECLARE CURSOR in DSQL is:

DECLARE cursorname CURSOR FOR queryname;

For example, the following C code fragment declares a string variable,
querystring, to hold the user-defined query, gets a query from the user and stores it in querystring, uses querystring to PREPARE a query called QUERY, then declares a cursor, C, that uses QUERY:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char querystring [512];
XSQLDA *InputSqlda, *OutputSqlda;
EXEC SQL
END DECLARE SECTION;
. . .
printf("Enter query: "); /* prompt for query from user */
gets(querystring); /* get the string, store in querystring */
. . .
EXEC SQL
PREPARE QUERY INTO OutputSqlda FROM :querystring;
. . .
EXEC SQL
DECLARE C CURSOR FOR QUERY;

For more information about creating and filling XSQLDA structures, and preparing DSQL queries with PREPARE, see Using Dynamic SQL.