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

Exception Handling and Error Conditions
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
13-14
Getting Statement and Condition Items
The statement-info is defined as:
target = stmt-item-name [,target = stmt-item-name]...
The condition-info is defined as:
EXCEPTION condition-number
target = condtn-item-name [,target = condtn-item-name]...
The target is a host variable that receives the requested diagnostics information.
target must have the same data type as the stmt-item-name or condtn-item-
name you are requesting. The condition-number specifies the number of an
exception condition. It can be a literal or host variable with exact numeric data type.
Getting Statement and Condition Items
The next example uses the GET DIAGNOSTICS statement to display condition
information after an INSERT statement. The first GET DIAGNOSTICS obtains the
number of condition items. The second GET DIAGNOSTICS loops through the
individual condition items and prints information for each condition.
See Statement Items-GET DIAGNOSTICS and Condition Items-GET DIAGNOSTICS
in the SQL/MX Reference Manual.
You can retrieve the message text of the exception condition for SQLSTATE and
SQLCODE. To provide a log of exception conditions, you can write the SQLSTATE
and SQLCODE values, along with the message text, to a file for future reference.
Examples
/* Set new_jobcode and new_jobdesc host variables. */
EXEC SQL INSERT INTO sales.job (jobcode,jobdesc)
VALUES (:new_jobcode,:new_jobdesc);
...
if (strcmp(SQLSTATE, SQLSTATE_OK) == 0)
{ printf ("\nValues were inserted!");
EXEC SQL COMMIT WORK; }
else get_diagnostics();
...
void get_diagnostics(void) {
EXEC SQL GET DIAGNOSTICS :num = NUMBER;
...
for (i = 1; i <= num; i++) {
EXEC SQL GET DIAGNOSTICS EXCEPTION :i
:hv_tabname = TABLE_NAME,
:hv_colname = COLUMN_NAME,
:hv_sqlstate = RETURNED_SQLSTATE,
:hv_sqlcode = SQLCODE,
:hv_msgtxt = MESSAGE_TEXT;
...
printf("Table : %s\n", hv_tabname);
printf("Column : %s\n", hv_colname);
printf("SQLSTATE: %s\n", hv_sqlstate);
printf("SQLCODE : %d\n", hv_sqlcode;
printf("Message : %s\n", hv_msgtxt);
C