7. Understanding Database Procedures, Sessions, and Events : How Database Procedures Are Created, Invoked, and Executed : Effects of Errors in Database Procedures : iierrornumber and iirowcount Variables
 
Share this page                  
iierrornumber and iirowcount Variables
The iierrornumber and iirowcount variables, in conjunction with the RAISE ERROR statement, handle errors in database procedures.
The iirowcount variable contains the number of rows affected by the last executed SQL statement. The iierrornumber variable contains the error number (if any) associated with the execution of a database procedure statement.
Because both iierrornumber and iirowcount reflect the results of the preceding query, beware of inadvertently resetting the value of one when checking the other.
The following example from a database procedure illustrates this error:
...

update emp set ...

/* The following statement resets iierrornumber, which will reflect the results of the second statement and not the first, as desired. */

/* wrong way to check iirowcount */

rcount = iirowcount;

/* The error number reflects the results of the preceding assignment, not the update statement */

enumber = iierrornumber;
The following example illustrates the correct way to check iierrornumber and iirowcount: select both values into variables, and then check the contents of the variables (because iierrornumber and iirowcount is reset to reflect the results of the SELECT statement).
...

update emp set ...

/* right way to check iirowcount (using select) */

select iirowcount, iierrornumber into rcount, enumber;
 
The following table lists the values of iirowcount and iierrornumber after the successful or unsuccessful execution of an SQL statement:
Statement
Success
Success
Error
Error
iirowcount
iierrornumber
iirowcount
iierrornumber
Insert
number of rows
0
0
Ingres error number
Update
number of rows
0
0
Ingres error number
Delete
number of rows
0
0
Ingres error number
Select
0 or 1
0
0
Ingres error number
Assignment
1
0
0
Ingres error number
Commit
-1
0
-1
Ingres error number
Rollback
-1
0
-1
Ingres error number
Message
-1
0
-1
Ingres error number
Return
-1
0
-1
Ingres error number
If
no change
no change
no change
Ingres error number
Elseif
no change
no change
no change
Ingres error number
While
no change
no change
no change
Ingres error number
Else
no change
no change
no change
no change
Endif
no change
no change
no change
no change
Endloop
no change
no change
no change
no change
Endwhile
no change
no change
no change
no change
The execution of each database procedure statement sets the value of iierrornumber either to zero (no errors) or an error number. To check the execution status of any particular statement, iierrornumber must be examined immediately after the execution of the statement.
Errors occurring in IF, WHILE, MESSAGE, and RETURN statements do not set iierrornumber. However, any errors that occur during the evaluation of the condition of an IF or WHILE statement terminate the procedure and return control to the calling application.