SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Dynamic SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
11-9
Nonstandard SQL/MP DATETIME Example
* Declare the dynamic cursor from the prepared statement.
EXEC SQL
DECLARE get_by_projcode CURSOR FOR cursor_spec
END-EXEC.
* Initialize the parameter in the WHERE clause.
DISPLAY "Enter the minimum estimated number of days: ".
ACCEPT in-est-complete.
* Open the cursor using the values of the dynamic parameter.
EXEC SQL
OPEN get_by_projcode USING :in-est-complete
END-EXEC.
* Fetch the first row of result from table.
EXEC SQL
FETCH get_by_projcode
INTO :hv-projcode, :hv-projdesc, :hv-est-complete
END-EXEC.
* Fetch rest of the results from table.
PERFORM UNTIL sqlstate = sqlstate-nodata
...
EXEC SQL FETCH get_by_projcode
INTO :hv-projcode, :hv-projdesc, :hv-est-complete
END-EXEC.
END-PERFORM.
* Close the cursor.
EXEC SQL CLOSE get_by_projcode END-EXEC.
* Deallocate the prepared cursor specification. */
EXEC SQL DEALLOCATE PREPARE cursor_spec END-EXEC.
Nonstandard SQL/MP DATETIME Example
This example uses a typical context for a nonstandard date-time input parameter,
DATETIME MONTH TO DAY (mm-dd), for a cursor specification:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
unsigned NUMERIC (4) hv_projcode;
VARCHAR hv_projdesc[19];
char hv_start_date[6];
char in_start_date[6];
char curspec[256]; /* Dynamic cursor spec */
EXEC SQL END DECLARE SECTION;
...
strcpy(curspec,
"SELECT projcode, projdesc, CAST(start_date AS CHAR(5))"
" FROM samdbcat.persnl.project"
" WHERE start_date <= "
" CAST(CAST(? AS CHAR(5)) AS DATETIME MONTH TO DAY)");
/* Prepare the cursor specification. */
EXEC SQL PREPARE cursor_spec FROM :curspec;
C