HiRDB sets up return codes (SQLCODE and SQLSTATE) when SQL statements execute. However, HiRDB does not set return codes for declaration statements, such as DECLARE CURSOR. The following variables can be used to reference the return codes:

• SQLCODE
• SQLSTATE

An SQL statement's execution status can be determined by referencing the SQLCODE and SQLSTATE variables.

Table 3-26 shows the SQL statement execution status indicated by the values set in the variables.

Table 3-26 Values set in variables and SQL statement execution status

SQL statement execution status		SQLCODE variable value	SQLWARN0 value	SQLWARN6 value	SQLSTATE variable value
Normal termination	Without warning	0	''		'00000'
	With warning4	0	'W'		'01nnn'1 (nnnR00)
		>0 ( 100, 110)			'R01R00'
	Without data3	110			'R2000'
No data		100			'02000'
Termination with error	Without implicit rollback	<0		''	'mmnnn'2
	With implicit rollback	<0	'W'	'W'	'40nnn'1

mm: Class

nnn: Subclass

: No value is set.

¹ 000 if nnn is not set.

² R0000 if mm or nnn is not set.

³ This status occurs when a search using a list is executed and a row that was present when the list was created is not returned.

⁴ The warning information is set in the SQLWARN1 to SQLWARNF areas or is indicated by the SQLCODE value (positive number other than 100). When warning information is set in the SQLWARN1 to SQLWARNF areas, W is set in SQLWARN0. Therefore, when SQLWARN0 contains W, the SQLWARN1 to SQLWARNF areas should also be checked.

For details about the contents of the SQLWARN0 to SQLWARNF areas, see A. SQL Communications Area.

If warning information is indicated with an SQLCODE value (positive number other than 100), the SQLSTATE subclass (nnn) becomes R00. Table 3-27 shows the relationships among the SQLSTATE, SQLCODE, and SQLWARN0 values if normal termination with warning occurs.

Table 3-27 Relationship among SQLSTATE, SQLCODE, and SQLWARN0 values when normal termination with a warning occurs

SQLSTATE value	SQLCODE value	SQLWARN0 value
01nnn (nnnR00)	0	'W'
01R00	Positive number other than 100	Blank or 'W'

(a) SQLCODE=100 or SQLSTATE='02000'

The UAP determines that there are no more rows to be retrieved.

This setting is useful for determining the following:

• There are no more rows to be fetched with the FETCH statement.
• No row was selected with the 1-row SELECT statement.
• There were no rows to be updated with the INSERT, DELETE, or UPDATE statement.

(b) SQLCODE<0 or SQLSTATE='mmnnn '(mm is not '00', '01', or '02', or mm is not '00', '01', '02', or 'R2' when a search using a list is executed)

The UAP determines that an SQL error occurred.

If an SQL error occurred, implicit rollback may also have occurred. If SQLWARN6='W' or SQLSTATE='40nnn', the UAP determines that implicit rollback occurred.

To identify the SQL statement that caused the error, check the SQL trace information. For details about the SQL trace information, see 10.1.1 SQL tracing.

(c) Values other than (a) or (b)

The UAP determines that the SQL statements terminated normally. Normal termination may come with warning information. If SQLWARN0='W', SQLCODE is a positive value other than 100, or SQLSTATE='01nnn', the UAP determines that normal termination with warning occurred.

When a search using a list is executed, normal termination without any data (a row that was present when the list was created has been deleted) may occur. If SQLCODE is 110 or SQLSTATE is 'R2000', the UAP must determine that normal termination without any data occurred and skip the processing for selection rows.

For details about normal termination with warning, see Table 3-27.

When you detect an error, use the following procedure:

1. Output or display the return codes.
2. If the cause of the error cannot be determined on the basis of the return code alone, specify display or output of additional return code information. It is also possible to display either the SQL statement at which the error occurred or information that can be used to identify the affected SQL statement.
   Table 3-28 shows the additional return code information and the items that are referred to by the information.
   

   Table 3-28 Additional return code information and items referred to by the information

   Additional information	Referenced item
   Message concerning SQLCODE*	SQLERRML field in SQL Communications Areas and contents of SQLERRMC field
   When a distributed system is used, information that identifies the source of the error in terms of the local RD node vs. other nodes	Contents of SQLCAIDE field of SQL Communications Areas

^* If the FETCH statement is re-executed after an error has occurred, HiRDB returns the return code for the previous error; however, variable parts of such an error message may not be applicable.

1. Cancel the transaction (ROLLBACK or abort the UAP). If a UAP transaction is rolled back implicitly by deadlock, the following processing is executed:
   

   Normal UAP:

   If a transaction is rolled back implicitly, the next SQL statement that is executed becomes the start of a new transaction (the ROLLBACK or DISCONNECT statement can also be executed).

   UAP executing in an OLTP environment:

   If a transaction is rolled back implicitly, HiRDB cannot accept any statements except DISCONNECT or ROLLBACK from the UAP executing in the OLTP environment.

   If an X/Open-compliant UAP is operated as a client in the OLTP environment and a deadlock occurs while the UAP is being executed, the affected transaction must be terminated.
   
2. Terminate the UAP or start a transaction (new execution of a different transaction or re-execution of the same transaction).
   Before re-executing the same transaction, you must take error correction measures. If the transaction is re-executed before the cause of the error has been eliminated, the system may enter an endless loop. If the same error occurs after re-execution, you may have to terminate the UAP.