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

Static SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-12
Using Date-Time and INTERVAL Data Types
Using Date-Time and INTERVAL Data Types
If a column in the select list of a cursor specification has an INTERVAL or standard
date-time (DATE, TIME, or TIMESTAMP, or the SQL/MP DATETIME equivalents) data
type, use the INTERVAL or date-time type.
If a column in the select list of a cursor specification has a nonstandard SQL/MP
DATETIME data type that is not equivalent to DATE, TIME, or TIMESTAMP, you must
use the CAST function to convert the column to a character string. You must also
specify the length as the length of the target host variable (or the length–1 in the case
of a C program) as part of the CAST conversion. Furthermore, if the column in the
WHERE clause to be compared to the input value has a nonstandard SQL/MP
DATETIME data type, you must use the CAST function to convert the character input
value to the appropriate data type.
Standard Date-Time Example
This example shows a typical context for a date-time input parameter for a cursor
specification:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
unsigned NUMERIC (4) hv_projcode;
char hv_projdesc[19];
DATE hv_start_date;
DATE in_start_date;
EXEC SQL END DECLARE SECTION;...
EXEC SQL DECLARE get_project CURSOR FOR
SELECT projcode, projdesc, start_date
FROM samdbcat.persnl.project
WHERE start_date <= :in_start_date;
/* Initialize the value in the WHERE clause. */
printf("Enter latest start date in form yyyy-mm-dd: ");
scanf("%s", in_start_date);
/* Open the cursor using this value. */
EXEC SQL OPEN get_project;
/* Fetch the first row of the result table. */
EXEC SQL FETCH get_project
INTO :hv_projcode,:hv_projdesc,:hv_start_date;
while (strcmp (SQLSTATE, SQLSTATE_NODATA) != 0) {
/* Process the row in some way. */
...
/* Fetch the next row of the result table. */
EXEC SQL FETCH get_project
INTO :hv_projcode,:hv_projdesc,:hv_start_date;
}
/* Close the cursor. */
EXEC SQL CLOSE get_project;
C