|
|
|
The following sections describe how the DBMS returns error information.
|
|
|
Three types of error codes are returned to applications:
Local errors are error codes specific to the DBMS.
Generic errors are a set of error codes that are mapped to both the DBMS and to error codes returned through Enterprise Access products from other relational and non-relational databases. Generic errors allow portable applications to be written.
SQLSTATE and SQLCODE are ANSI/ISO-compliant error code variables. (SQLCODE is supported by Ingres but designated by ANSI/ISO Entry SQL-92 as a depreciated feature. SQLSTATE is the ANSI/ISO Entry SQL-92-compliant method for returning errors.)
By default, the DBMS returns generic and local errors as follows:
Returned in sqlcode (an SQLCA variable) as a negative value. (Also in the SQLCODE standalone variable.)
Returned when your application issues the inquire_sql(errorno) statement.
Returned in sqlerrd(1), the first element of the SQLCA sqlerrd array.
Returned when your application issues the inquire_sql(dbmserror) statement.
To reverse this arrangement (so that local error numbers are returned to errorno and sqlcode and generic errors to dbmserror and sqlerrd(1)), use the set_sql(errortype) statement.
To obtain the text of error messages, use the inquire_sql(errortext) statement or check the SQLCA variable sqlerrm.
|
|
|
Every Ingres error message consists of an error code and the accompanying error message text.
All Ingres error codes begin with E_, followed by one or two letters plus a 4-digit hexadecimal number, and, optionally, descriptive text or the decimal equivalent of the hex error code. For example:
E_GEC2EC_SERIALIZATION
indicates a serialization failure (deadlock).
If the error is a local error, the two letters following E_ indicate which Ingres facility issued the error. If the error is a generic error number, the two letters are GE. The hexadecimal error code is unique for each error.
Local error codes are stored in $II_SYSTEM/ingres/files/english/messages/message.text
Generic error codes are stored in $II_system/ingres/files/generr.h
|
|
|
When working in one of the forms-based user interfaces (such as Query-By-Forms (QBF) or the forms-based Terminal Monitor), error messages appear on a single line across the bottom of your terminal screen. The text appears first, followed by the error code. If the text is longer than one line, press the Help key to see the rest of the message. To clear the error message from the screen, press the Return key.
When not working in a forms-based user interface, the DBMS displays the error code followed by the entire message text.
If an SQLCA is included in an embedded SQL application, automatic display of error messages is disabled. Program code that displays errors must be provided.
|
|
|
SQL provides a variety of tools for trapping and handling errors in embedded SQL applications, including:
The following sections describe the error handling tools and how they are used.
|
|
|
The SQL Communications Area (SQLCA) is a collection of host language variables whose values provide status and error information about embedded SQL database statements. (The status of forms statements is not returned in SQLCA variables.) If your application does not have an SQLCA, the default is to display errors and continue with the next statement if possible.
Two variables in the SQLCA contain error information: sqlcode and sqlerrm. The value in sqlcode indicates one of three conditions:
Sqlcode contains a value of zero.
Sqlcode contains the error number as a negative value.
Set when the statement executed successfully but an exceptional condition occurred. Sqlcode contains either +100, indicating that no rows were processed by a delete, fetch, insert, update, modify, copy, or create table...as statement, or +700, indicating that a message statement inside a database procedure has just executed.
The sqlerrm variable is a varying length character string variable that contains the text of the error message. The maximum length of sqlerrm is 70 bytes. If the error message exceeds that length, the message is truncated when it is assigned to sqlerrm. To retrieve the full message, use the inquire_sql statement. In some host languages, this variable has two parts: sqlerrml, a 2-byte integer indicating how many characters are in the buffer, and sqlerrmc, a 70-byte fixed length character string buffer.
The SQLCA also contains eight 1-byte character variables, sqlwarn0 - sqlwarn7, that are used to indicate warnings. For a complete listing of these variables, see the table titled SQLCA Variables.
The SQLCA is often used in conjunction with the whenever statement, which defines a condition and an action to take whenever that condition is true. The conditions are set to true by values in the sqlcode variable. For example, if sqlcode contains a negative error number, the sqlerror condition of the whenever statement is true and any action specified for that condition is performed. For details, see Trapping Errors Using Whenever Statement.
The SQLCA variables can also be accessed directly. For information about using the SQLCA in an application, see the Embedded SQL Companion Guide.
|
|
|
SQLSTATE is a variable in which the DBMS returns error codes as prescribed by the ANSI/ISO Entry SQL-92 standard. For a list of the values returned in SQLSTATE and the corresponding generic error, see the appendix "Generic Error Codes and SQLSTATE."
|
|
|
The whenever statement specifies a particular action to be performed whenever a particular condition is true. Because conditions are set to true by values in the SQLCA sqlcode, the whenever statement responds only to errors generated by embedded SQL database statements. Forms statements do not set sqlcode.
The following conditions indicate errors or warnings:
Warnings/Error |
Explanation |
|---|---|
sqlwarning |
Indicates that the executed SQL database statement produced a warning condition. Sqlwarning becomes true when the SQLCA sqlwarn0 variable is set to W. |
sqlerror |
Indicates that an error occurred in the execution of the database statement. Sqlerror becomes true when the SQLCA sqlcode variable contains a negative number. |
For a complete discussion of all the conditions, see Whenever in the chapter "Using SQL Statements."
The actions that can be specified for these conditions are listed in the following table:
Action |
Explanation |
|---|---|
continue |
Execution continues with the next statement. |
stop |
Prints an error message and terminates the program's execution. Pending updates are not committed. |
goto label |
Performs a host language go to. |
call procedure |
Calls the specified host language procedure. If call sqlprint is specified, a standard sqlprint procedure is called. This procedure prints the error or warning message and continues with the next statement. A database procedure cannot be specified. |
In an application program, a whenever statement is in effect until the next whenever statement (or the end of the program). For example, if you put the following statement in your program:
exec sql whenever sqlerror call myhandler;
The DBMS traps errors for all database statements in your program that (physically) follow the whenever statement, to the "myhandler" procedure. A whenever statement does not affect the statements that physically precede it.
The following diagram illustrates the scope of the whenever statement:
If your program includes an SQLCA, error and database procedure messages are not displayed unless your application issues a whenever ... sqlprint statement, or II_EMBED_SET is set to sqlprint. For details about II_EMBED_SET, see the System Administrator Guide.
|
|
|
An error handling function can be defined to be called when SQL errors occur. To do this, perform the following:
exec sql set_sql(errorhandler = error_routine);
where
error_routine is the name of the error-handling routine that was created. Do not declare error_routine in an SQL declare section, and do not precede error_routine with a colon; the error_routine argument must be a function pointer.
When this form of error trapping is enabled, all SQL errors are trapped to your routine until error trapping is disabled (or until the application terminates). Forms errors are not trapped.
To disable the trapping of errors to your routine, your application must issue the following set statement:
exec sql set_sql(errorhandler = 0 | error_var)
where error_var is a host integer variable having a value of 0.
Your error-handling routine must not issue any database statements in the same session in which the error occurred. If it is necessary to issue database statements in an error handler, open or switch to another session.
To obtain error information, your error-handling routine must issue the inquire_sql statement.
|
|
|
In addition to error-handling routines, routines can be defined that enable embedded SQL applications to trap the following:
To enable or disable an event-handling routine, issue the following set_sql statement:
exec sql set_sql(dbeventhandler = event_routine | 0)
When an event notification is received by your application, the specified routine is automatically called. To obtain the event notification information, the event handler routine must use the inquire_sql statement.
To enable or disable a message handling routine, issue the following set_sql statement:
exec sql set_sql(messagehandler = message_routine | 0)
The message handler routine traps all messages from database procedures, including messages from procedures that are executed when rules are fired.
Specify the routine as a function pointer. For more information about specifying function pointers, see the Embedded SQL Companion Guide.
|
|
|
There are two inquire statements that can be used to perform error checking: inquire_sql and inquire_frs. Both statements return error numbers and messages using the constants errorno and errortext. Inquire_sql returns the error number and text for the last executed SQL database statement. Inquire_frs return the same information about the last executed forms statement. Unlike the whenever statement, an inquire statement must be executed immediately after the database or forms statement in question. By default, inquire_sql(errorno) returns a generic error number, but the set_sql statement can be used to specify that local errors are returned. For a discussion of local and generic errors, see Types of Errors.
Neither of the inquire statements suppress the display of error messages. Both of the inquire statements return a wide variety of information in addition to error numbers and text.
|
|
|
The set_sql(programquit) statement specifies how an embedded SQL application handles the following types of errors:
By default, when these types of errors occur, the DBMS issues an error but lets the program continue. To force an application to abort when one of these errors occur, issue the following set_sql statement:
exec sql set_sql (programquit = 1);
If an application aborts as the result of one of the previously listed errors, the DBMS issues an error and rolls back open transactions and disconnects all open sessions. To disable aborting and restore the default behavior, specify
programquit = 0.
Errors affected by the programquit setting belong to the generic error class GE_COMM_ERROR, which is returned to errorno as 37000, and to sqlcode (in the SQLCA) as -37000. An application can check for these errors and, when detected, must disconnect from the current session. After disconnecting from the current session, the application can attempt another connection, switch to another session (if using multiple sessions), or perform clean-up operations and quit.
Programquit can also be specified by using II_EMBED_SET. (For details about II_EMBED_SET, see the System Administrator Guide.)
To determine the current setting for this behavior, use the inquire_sql statement:
exec sql inquire_sql (int_variable = programquit);
This returns a 0 if programquit is not set (execution continues on any of the errors) or 1 if programquit is set (the application exits after these errors).
|
|
|
A deadlock occurs when two transactions are each waiting for the other to release a part of the database to enable it to complete its update. Transactions that handle deadlocks in conjunction with other errors can be difficult to code and test, especially if cursors are involved.
|
|
|
The following example assumes your transactions do not contain a cursor:
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err; /* branch
on error */
exec sql commit;
start:
exec sql insert into ...
exec sql update ...
exec sql select ...
exec sql commit;
goto end;
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end:
|
|
|
The following example assumes your transactions contain a single cursor:
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
exec sql declare c1 cursor for ...
exec sql commit;
start:
exec sql open c1;
while more rows loop
exec sql fetch c1 into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close c1;
exec sql commit;
goto end;
end if;
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop;
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end:
|
|
|
The following example assumes your transactions contains two cursors(two cursors with a master/detail relationship):
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
exec sql declare master cursor for ...
exec sql declare detail cursor for ...
exec sql commit;
start:
exec sql open master;
while more master rows loop
exec sql fetch master into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close master;
exec sql commit;
goto end;
end if;
/* ...queries using master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
exec sql open detail;
while more detail rows loop
exec sql fetch detail into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close detail;
end loop;/* drops out of detail
fetch loop */
end if;
/* ...queries using detail & master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop; /* end of detail fetch loop */
/* ...more queries using master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop;/* end of master fetch loop */
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end: