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

Introduction
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
1-9
Processing Exception Conditions
•
If you want to get all the results from the SELECT statement, use a rowset cursor.
See Selecting Rowsets With a Cursor on page 7-12.
You must use a cursor when the maximum number of result rows cannot be estimated
or when the memory requirements are too large to store the result table of the query.
Processing Exception Conditions
Your host language program can detect exception conditions and diagnostics
information after the execution of each SQL statement. For more details, see
Section 13, Exception Handling and Error Conditions.
To process exception conditions and diagnostics information:
•
Check the SQLSTATE variable.
•
Use the WHENEVER declaration.
•
Use the GET DIAGNOSTICS statement.
Checking SQLSTATE
Check the value of SQLSTATE after the execution of an SQL statement. NonStop
SQL/MX returns a value to SQLSTATE to indicate the results of the execution. Your
program can then use conditional statements to test the value and take appropriate
action.
Example
char SQLSTATE_OK[6] = "00000";
char SQLSTATE_NODATA[6] = "02000";
...
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT ... ; /* SELECT INTO statement */
if (strcmp(SQLSTATE, SQLSTATE_NODATA) == 0) handle_nodata();
...
Using WHENEVER
Use the WHENEVER declaration to specify an action when an error, warning, or no-
rows-found condition occurs. Place the WHENEVER declaration anywhere in your
program. The preprocessor inserts code after every SQL statement that follows a
WHENEVER declaration to check values of SQLSTATE and take appropriate action.
Note. Although NonStop SQL/MX supports the SQLCODE variable, use the SQLSTATE
variable, which complies with the SQL:1999 standard, as the preferred status code for
NonStop SQL/MX.
C