7. Handling Runtime Errors#
Overview#
Applications must be able to handle internal runtime errors. APRE provides the programmer with numerous methods for handling runtime errors, including variables such as SQLCODE and SQLSTATE and the WHENEVER statement.
Return Values#
After an embedded SQL statement is executed, the result of execution is stored in sqlca.sqlcode. This variable can have the following values:
-
SQL_SUCCESS
This value indicates that the embedded SQL statement was executed successfully. -
SQL_SUCCESS_WITH_INFO
This value indicates that the embedded SQL statement was executed but that a warning was detected. -
SQL_NO_DATA
This value indicates that no records were returned by an executed SELECT or FETCH statement. -
SQL_ERROR
This value indicates that an error occurred during execution of the embedded SQL statement.
sqlca#
sqlca is an instance of the ulpSqlca structure that is declared during the precompile operation. ulpSqlca is an internal structure that is used to store the results of execution of an embedded SQL statement, and is defined in the ulpLibInterface.h file. Developers can use the sqlca variable in their applications to check the result of execution of embedded SQL statements.
ulpSqlca Data Structure Definition#
typedef struct ulpSqlca
{
char sqlcaid[8]; /* not used */
int sqlcabc; /* not used */
int sqlcode;
struct
{
short sqlerrml;
char sqlerrmc[2048];
}sqlerrm;
char sqlerrp[8]; /* not used */
int sqlerrd[6];
char sqlwarn[8]; /* not used */
char sqlext[8]; /* not used */
} ulpSqlca;
sqlca Elements#
The ulpSqlca structure consists of numerous constituent elements. Some of these elements are reserved for future use, and are thus not described here.
The meaning of each element is as follows:
sqlcode#
This element is used to store the result of execution of an embedded SQL statement. The value stored herein will be one of the following, which were described above:
-
SQL_SUCCESS
-
SQL_SUCCESS_WITH_INFO
-
SQL_NO_DATA
-
SQL_ERROR
sqlerrm.sqlerrmc#
This element is used to store error messages. The maximum error message length that can be saved herein is 2048 bytes.
sqlerrm.sqlerrml#
This element is used to store the length of the returned error message.
sqlerrd[2]#
This element is used to store the number of records that were affected by the execution of an INSERT, UPDATE, or DELETE statement.
When a SELECT or FETCH statement is executed and the output host variable is an array, this element is used to store the number of records that were returned. This is only the number of records that were returned by the most recently executed SELECT or FETCH statement, not a cumulative number of records returned by multiple statements. Therefore, this value will not be larger than the array size.
sqlerrd[3]#
The value of this variable can be checked after an array-type input host variable is used to execute an embedded SQL statement. This value indicates the number of elements of the array-type host variable for which the operation was successfully performed. Therefore, this value cannot be larger than the array size.
For example, if an array-type input host variable whose size is 3 is used to execute an UPDATE statement, and the respective update operations are successful for the 0th array element, unsuccessful for the 1st array element, and successful for the 2nd array element, a value of 2 will be stored in this variable. Meanwhile, the number of records that were actually updated will be stored in sqlca.sqlerrd[2], which means that a value higher than 2 might be stored in sqlca.sqlerrd[2].
Precautions#
In order to ensure that errors are reliably detected and handled, it is necessary to check sqlca.sqlcode every time an embedded SQL statement is executed.
When a SELECT statement is used to retrieve a value from a character type column, and the size of the corresponding output variable is smaller than (the size of the column + 1), the character column data will be truncated to the length of the host variable - 1 so that it can be stored therein. At this time, the contents of sqlca.sqlcode will be SQL_SUCCESS_WITH_INFO.
If no records are affected by an UPDATE or DELETE operation, the contents of sqlca.sqlcode will be SQL_NO_DATA. To check the number of records that were affected by an UPDATE or DELETE operation, check the value of sqlca.sqlerrd[2].
SQLCODE#
If the result of execution of an embedded SQL statement is SQL_ERROR, the error code will be stored in SQLCODE.
Data Structure Definition#
int SQLCODE;
SQLCODE Return Values#
- 0
This is returned upon successful execution of an embedded SQL statement. At this time, the value of sqlca.sqlcode will be SQL_SUCCESS. - 1
This is returned when the embedded SQL statement was executed but a warning was detected. At this time, the value of sqlca.sqlcode will be SQL_SUCCESS_WITH_INFO. - 100
This is returned when no records were returned by an executed SELECT or FETCH statement. At this time, the value of sqlca.sqlcode will be SQL_NO_DATA. - -1
This is returned when an error occurred during execution of the embedded SQL statement, but there is no error code corresponding to the error that occurred. At this time, the value of sqlca.sqlcode will be SQL_ERROR. - -Other negative values indicate that an error occurred during execution of the embedded SQL statement. In this case, the return value is the actual error code.
Error Codes#
Depending on the origin of the error, the error code that is returned when an error occurs during the execution of an embedded SQL statement will be either an APRE error code or a database server error code.
Embedded SQL Statement Error Codes#
Errors that are raised by embedded SQL statements at runtime will return APRE error codes. These include the following. (For information on errors other than the following, please refer to Error Message Reference.)
- 589826: This code is returned when a connection with the same name already exists.
- -589841: This code is returned when the name of the connection exceeds 50 bytes.
- -589857: This code is returned when an attempt is made to execute a SQL statement for handling a cursor, and the name of the cursor has not been declared.
- -589858: This code is returned when an attempt is made to execute a dynamic SQL statement using an identifier for a SQL statement that has not been prepared.
Database Server Error Codes#
When an error that occurs during the execution of a SQL statement originates in the database server, a database server error code will be returned. For complete information about all Altibase error codes, please refer to the Altibase Error Message Reference.
Precaution#
The error codes returned by SQLCODE are negative decimal numbers. However, the error codes in the Error Message Reference are positive decimal and hexadecimal numbers. Therefore, when referring to the Error Message Reference, use the absolute value of the error code returned by SQLCODE, or convert this absolute value into a hexadecimal number.
SQLSTATE#
SQLSTATE is used to store a status code. This status code can be used to determine the error that occurred, or the warning that was raised. Checking the value of SQLSTATE is useful when the result of execution of an embedded SQL statement is SQL_ERROR or SQL_SUCCESS_WITH_INFO.
Definition of Data Structure#
char SQLSTATE[6];
Status Codes#
- 00000 – This code is returned upon successful execution of an embedded SQL statement.
- 01004 – This code is returned when the size of a character type output host variable is the same as or smaller than the corresponding column size. At this time, the returned data are truncated so that they can be stored in the host variable.
- 07006 – This code is returned when the host variable type is not compatible with the corresponding column type.
- 07009 – This code is returned when the number of the columns is greater than the number of corresponding host variables.
- 08001 – This code is returned when the database server is not running.
- 08S01 – This code is returned when the connection with the database server is interrupted.
- 21S01 – This code is returned when the number of columns is not same as the number of corresponding input host variables for an INSERT statement.
- 22002 – This code is returned when NULL data are returned and no indicator variable is being used to detect NULL data.
- HY000 – This code indicates a general error.
- HY001 – This code is returned when a memory allocation error occurs.
- HY009 – This code is returned when the host variable and the indicator variable are both NULL pointers.
- HY010 – This code is returned when an attempt is made to fetch records using a cursor that has not been opened.
- HY090 – This code is returned when the value of the indicator variable is invalid.
WHENEVER Statement#
APRE provides the WHENEVER statement for use in handling runtime errors.
Syntax#
EXEC SQL WHENEVER <condition> <action>;
Arguments#
-
<condition> : This is the result of execution of an embedded SQL statement.
-
<action> : This is the action to take in response to the result of execution of the embedded SQL statement.
Conditions#
The following conditions can be set in a WHENEVER statement:
SQLERROR#
This condition is used to detect the occurrence of an error, meaning a sqlca.sqlcode value of SQL_ERROR, during the execution of an embedded SQL statement.
NOT FOUND#
This condition is used to detect the state in which no records are returned in response to the execution of a SELECT or FETCH statement, at which time the value of sqlca.sqlcode is SQL_NO_DATA.
Actions#
If the result of execution of an embedded SQL statement matches the condition specified in the WHENEVER statement, the action specified here will be taken.
The following actions can be specified in the WHENEVER statement:
CONTINUE#
This specifies that the condition is to be ignored and execution is to continue.
DO BREAK#
This specifies that the current loop construct is to be exited, and that execution is to continue. This has the same effect as using the 'do break'; command in a loop construct. This action can only be specified within a loop construct, and a WHENEVER statement specifying the action will only have an effect within the loop.
DO CONTINUE#
This specifies that control is to be passed to the next iteration of the current loop construct, and that execution is to 'continue'. This has the same effect as using the continue; command in a loop construct. This action can only be specified within a loop construct, and a WHENEVER statement specifying the action will only have an effect within the loop.
DO function_name#
This calls the function specified using function_name.
GOTO label_name#
This specifies that control is to be passed to the statement immediately following label_name, and that execution is to continue.
STOP#
This specifies that the connection with the database server is to be closed, and that the application is to be shut down.
Description#
The scope of applicability of a WHENEVER statement is not determined by the flow of execution of the application. A WHENEVER statement is valid only within the current file.
The WHENEVER statement must precede any embedded SQL statements to which it is intended to apply. That is, all embedded SQL statements that follow a WHENEVER statement in the source file will be tested.
A WHENEVER statement tests the results of execution of all embedded SQL statement in the routine in which it is declared and all routines within that routine). Therefore, whenever the result of execution of any embedded SQL statement in this scope matches the condition in the WHENEVER statement, the corresponding action will be taken.
A WHENEVER statement that checks for the SQLERROR condition and a WHENEVER statement that checks for the NOT FOUND condition can coexist without having any effect on each other.
When execution control moves out of the routine in which a WHENEVER statement was declared, the WHENEVER statement has no further effect. From that point onward, embedded SQL statements will be affected by WHENEVER statements located in the current routine or higher routines.
If two WHENEVER statements that listen for the same condition are present within the same routine, the WHENEVER statement that appeared first will have no effect, and the more recent WHENEVER statement will apply.
If two WHENEVER statements that check for the same condition exist, but have different scopes of applicability, the WHENEVER statement having the broader scope (i.e. the statement declared in an outer routine) will have no effect, and the WHENEVER statement having the narrower scope (i.e. the statement declared in an inner routine) will apply.
WHENEVER statements are connection-independent. In other words, a WHENEVER statement in an application with more than one connection affects all embedded SQL statements within its scope of applicability, regardless of the connection to which the embedded SQL statements pertain.
A WHENEVER statement that has a global scope will affect all embedded SQL statements in the file in which it appears.
Sample Programs#
runtime_error_check.sc#
This example can be found at $ALTIBASE_HOME/sample/APRE/runtime_error_check.sc
Result of Execution#
$ is -f schema/schema.sql
$ make runtime_error_check
$ ./runtime_error_check
<RUNTIME ERROR CHECK>
------------------------------------------------------
[SQL_SUCCESS]
------------------------------------------------------
sqlca.sqlcode = 0
-----------------------------------------------------------
[SQL_SUCCESS_WITH_INFO With SQLSTATE=01004]
-----------------------------------------------------------
sqlca.sqlcode = 1
sqlca.sqlerrm.sqlerrmc = String data right truncated.
SQLSTATE = 01004
SQLCODE = 1
-----------------------------------------------------------
[SQL_ERROR With SQLSTATE=22002]
-----------------------------------------------------------
sqlca.sqlcode = -1
sqlca.sqlerrm.sqlerrmc = Indicator variable required but not supplied.
SQLSTATE = 22002
SQLCODE = -331841
-----------------------------------------------------------
[SQL_NO_DATA With SELECT]
-----------------------------------------------------------
sqlca.sqlcode = 100
sqlca.sqlerrm.sqlerrmc = Not found data
SQLSTATE = 02000
SQLCODE = 100
-----------------------------------------------------------
[SQL_NO_DATA With FETCH]
-----------------------------------------------------------
sqlca.sqlcode = 100
sqlca.sqlerrm.sqlerrmc = Not found data
SQLSTATE = 02000
SQLCODE = 100
2 rows fetched
-----------------------------------------------------------
[SQL_ERROR]
-----------------------------------------------------------
sqlca.sqlcode = -1
sqlca.sqlerrm.sqlerrmc = The row already exists in a unique index.
SQLSTATE = 23000
SQLCODE = -69720
-----------------------------------------------------------
[SQL_ERROR With SQLSTATE=HY010]
-----------------------------------------------------------
sqlca.sqlcode = -1
sqlca.sqlerrm.sqlerrmc = Function sequence error.
SQLSTATE = HY010
SQLCODE = -331796
-----------------------------------------------------------
[sqlca.sqlerrd[2]]
-----------------------------------------------------------
sqlca.sqlcode = 0
sqlca.sqlerrd[2] = 12
-----------------------------------------------------------
sqlca.sqlerrd[3] With Array In-Binding]
-----------------------------------------------------------
sqlca.sqlcode = 0
sqlca.sqlerrd[2] = 12
sqlca.sqlerrd[3] = 3
whenever1.sc#
This example can be found at $ALTIBASE_HOME/sample/APRE/whenever1.sc
whenever2.sc#
This example can be found at $ALTIBASE_HOME/sample/APRE/whenever2.sc
Result of Execution#
$ is -f schema/schema.sql
$ make whenever
$ ./whenever
<WHENEVER>
Success connection
------------------------------------------------------------------
DNO DNAME DEP_LOCATION MGR_NO
------------------------------------------------------------------
1001 PAPER TEAM New York 16
1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
1003 SOLUTION DEVELOPMENT DEPT Japan 14
2001 QUALITY ASSURANCE DEPT Seoul 17
3001 CUSTOMER SUPPORT DEPT London 4
3002 PRESALES DEPT Peking 5
4001 MARKETING DEPT Seoul 8
4002 BUSINESS DEPT LA 7