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-7
Using Date-Time and INTERVAL Data Types
Suppose that you are finished with the dynamic SQL statement that specifies the
cursor. Deallocate the resources used by the prepared cursor specification. The
module that contains the DEALLOCATE PREPARE statement must also contain a
PREPARE statement for statement-name.
DEALLOCATE PREPARE statement-name
For complete syntax, see the DEALLOCATE PREPARE statement in the SQL/MX
Reference Manual.
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 of the target host variable (or the length–1 in the case of a C
program) as part of the CAST conversion.
The data type of an input parameter can be either numeric or character. If a column (in
the WHERE clause of the cursor specification) to be compared to an input parameter
has an INTERVAL or standard date-time data type, the parameter in the USING clause
of the OPEN statement must have an INTERVAL or compatible date-time data type. In
the WHERE clause, you must cast the parameter to a date-time or INTERVAL data
type.
If a column (in the WHERE clause of the cursor specification) to be compared to an
input parameter has a nonstandard SQL/MP date-time data type, the parameter in the
USING clause of the OPEN statement must have a character data type. In the WHERE
clause, you must first specify the data type of the parameter as CHAR in the AS clause
of the CAST function, and then cast it to a date-time data type.
Standard Date-Time Example
This example uses a typical context for a standard date-time input parameter for a
cursor specification:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
unsigned NUMERIC (4) hv_projcode;
VARCHAR hv_projdesc[19];
DATE hv_start_date;
DATE in_start_date;
char curspec[256]; /* Dynamic cursor spec */
EXEC SQL END DECLARE SECTION;
...
strcpy(curspec,
"SELECT projcode, projdesc, start_date)"
C