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

C Sample Programs
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
A-16
Using a Dynamic SQL Cursor
SQLSTATE[5]='\0';
SQLSTATE_OK[5]='\0';
SQLSTATE_NODATA[5]='\0';
printf("\n\nThis example uses a dynamic cursor. \n\n");
EXEC SQL WHENEVER SQLERROR GOTO end_prog;
strcpy(curspec,"SELECT partnum, partdesc, price, qty_available"
" FROM samdbcat.sales.parts "
" WHERE qty_available <= CAST(? AS NUMERIC(5))");
/* Prepare the cursor specification. */
EXEC SQL PREPARE cursor_spec FROM :curspec;
/* Declare the dynamic cursor from the prepared statement. */
EXEC SQL DECLARE get_by_partnum CURSOR FOR cursor_spec;
/* Initialize the parameter in the WHERE clause. */
printf("Enter the quantity to initiate reorder: ");
scanf("%d", &in_qty_available);
/* Open the cursor using the value of the dynamic parameter. */
EXEC SQL OPEN get_by_partnum USING :in_qty_available;
/* Fetch the first row of the result table. */
EXEC SQL FETCH get_by_partnum
INTO :hv_partnum,:hv_partdesc,:hv_price,:hv_qty_available;
while (strcmp (SQLSTATE, SQLSTATE_NODATA) != 0) {
printf("\nOrder part number: %hu, Current qty: %d",
hv_partnum, hv_qty_available);
/* Fetch the next row of the result table. */
EXEC SQL FETCH get_by_partnum
INTO :hv_partnum,:hv_partdesc,:hv_price,:hv_qty_available;
}
/* Close the cursor. */
EXEC SQL CLOSE get_by_partnum;
end_prog:
EXEC SQL WHENEVER SQLERROR CONTINUE;
Example A-6. Using a Dynamic SQL Cursor (page 2 of 3)