18.1.5 Evaluation and handling of SQL statement errors

You must determine whether the SQL statements executed by the application program have executed successfully. This subsection explains how to determine whether the SQL statements have been executed successfully and the error handling procedure.

Organization of this subsection

(1) How to evaluate SQL statement errors
(2) Error handling procedure

(1) How to evaluate SQL statement errors

When an SQL statement is executed, SQLCODE is returned from the CLI function. You use the SQLCODE value to determine whether the SQL statement executed successfully. The following table lists and describes the SQLCODE values.

Table 18‒1: SQLCODE values
No.	SQLCODE value	Meaning
1	`100`	There are no more rows to be retrieved. This is useful especially when the following is to be determined: Whether all rows have been fetched by a `FETCH` statement Whether there is any row that is to be updated by an `INSERT`, `DELETE`, or `UPDATE` statement
2	`1`	The SQL statement's processing terminated, but a warning occurred in an extension of the processing. Possible causes of warnings are as follows: The disk at the HADB server that stores the server message log files has become full. The disk that stores client message log files has become full. A memory shortage was detected when outputting SQL trace information on the HADB server. `ROLLBACK` processing was performed and then the connection was closed because `COMMIT` processing failed in an extension of the connection close processing.
3	Negative value	An SQL error occurred.
4	Other	The SQL statement executed successfully.

For details about the return values of the CLI function, see 19.8 Return values of the CLI functions.

To Page Top

(2) Error handling procedure

You use the procedure described below to handle errors that are detected. This subsection presents an example using a CLI function.

(a) Output of return value

The CLI function's return value is output or displayed.

(b) Error handling

If data manipulation by an SQL statement results in an error, take the action described below.

To handle the error:

Check the value of the isInConnect member in the SQL results information. The action to be taken depends on the value of isInConnect:
- If the value of isInConnect is a_rdb_SQL_IS_IN_CONNECT
  
  Go to step 2.
- If the value of isInConnect is a_rdb_SQL_IS_NOT_IN_CONNECT
  
  The application program has disconnected from the HADB server due to a fatal error. Check the message output to the client message log file. If the HADB server terminated abnormally, contact the HADB administrator.

Check the value of the EndTran member in the SQL results information. The action to be taken depends on the EndTran value, as shown in the following table:

No.	Value of EndTran	Action
1	`a_rdb_SQL_ROLLBACKED`	Because internal rollback was executed, disconnect the application program from the HADB server and terminate the application program. Then take appropriate action based on the return value.
2	`a_rdb_SQL_TRAN_NOT_ENDED`	Execute `ROLLBACK` to cancel the transaction, disconnect the application program from the HADB server, and terminate the application program. Then take appropriate action based on the return value.
3	Other	Disconnect the application program from the HADB server and terminate the application program. Then take appropriate action based on the return value.

To Page Top