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-8
Interval Example
" FROM samdbcat.persnl.project"
" WHERE start_date <= CAST(? AS DATE)";
/* Prepare the cursor specification. */
EXEC SQL PREPARE cursor_spec FROM :curspec;
/* Declare the dynamic cursor from the prepared statement. */
EXEC SQL DECLARE get_by_projcode CURSOR FOR cursor_spec;
/* Initialize the parameter in the WHERE clause. */
printf("Enter the latest start date in the form yyyy-mm-dd: ");
scanf("%s", in_start_date);
/* Open the cursor using the value of the dynamic parameter. */
EXEC SQL OPEN get_by_projcode USING :in_start_date;
/* Fetch the first row of the result table. */
EXEC SQL FETCH get_by_projcode
INTO :hv_projcode,:hv_projdesc,:hv_start_date;
while (strcmp (SQLSTATE, SQLSTATE_NODATA) != 0) {
hv_start_date[10]='\0';
printf("\nProject Code: %hu, Start Date: %s",
hv_projcode, hv_start_date);
/* Fetch the next row of the result table. */
EXEC SQL FETCH get_by_projcode
INTO :hv_projcode,:hv_projdesc,:hv_start_date;
}
/* Close the cursor. */
EXEC SQL CLOSE get_by_projcode;
/* Deallocate the prepared cursor specification. */
EXEC SQL DEALLOCATE PREPARE cursor_spec;
Interval Example
This example uses a typical context for an interval input parameter for a cursor
specification:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 hv-projcode pic 9(4) comp.
01 hv-projdesc pic x(18).
01 hv-est-complete INTERVAL DAY(3).
01 curspec pic x(255).
01 in-est-complete INTERVAL DAY(3).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
MOVE "SELECT projcode, projdesc, est_complete
- " FROM samdbcat.persnl.project
- " WHERE est_complete >=
- " CAST(? AS INTERVAL DAY(3))"
TO curspec.
* Prepare cursor specification.
EXEC SQL PREPARE cursor_spec FROM :curspec END-EXEC.
COBOL