On 21 Mar 2013 13:44, Anderson, Kurt wrote:
I'm at IBM i 7.1 and I just created a stored procedure to insert a
record into a file (and return the identity created). However, I'm
not sure how to apply error handling (to monitor for such things as
a duplicate record error). I ran a bunch of searches, but I must not
be using the right words. I've looked for Exception, Error handling,
try, catch, and monitor.
I'm using this document as my main source of info:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf
Can anyone show me the path toward error handling in SQL on the IBM
i?
Conditions and condition handlers; perhaps also GET DIAGNOSTICS.
Searching InfoCenter on the three tokens SQL DECLARE HANDLER yielded
the links below, among others:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzsqlprocstmt.htm
_SQL-procedure-statement_
"...
Detecting and processing error and warning conditions: As an SQL
statement is executed, the database manager stores information about the
processing of the statement in a diagnostics area (including the
SQLSTATE and SQLCODE), unless otherwise noted in the description of the
SQL statement. A completion condition indicates the SQL statement
completed successfully, completed with a warning condition, or completed
with a not found condition. An exception condition indicates that the
SQL statement was not successful.
A condition handler can be defined in a compound statement to execute
when an exception condition, a warning condition, or a not found
condition occurs. The declaration of a condition handler includes the
code that is to be executed when the condition handler is activated.
When a condition other than a successful completion occurs in the
processing of SQL-procedure-statement, if a condition handler that could
handle the condition is within scope, one such condition handler will be
activated to process the condition. See compound-statement for
information about defining condition handlers. The code in the condition
handler can check for a warning condition, not found condition, or
exception condition and take the appropriate action. Use one of the
following methods at the beginning of the body of a condition handler to
check the condition in the diagnostics area that caused the handler to
be activated:
* Issue a GET DIAGNOSTICS statement to request the condition
information. See GET DIAGNOSTICS statement.
* Test the SQL variables SQLSTATE and SQLCODE.
If the condition is a warning and there is not a handler for the
condition, the above two methods can also be used outside of the body of
a condition handler immediately following the statement for which the
condition is wanted. If the condition is an error and there is not a
handler for the condition, the routine or trigger terminates with the
error condition.
Parent topic: SQL control statements"
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcompoundstmt.htm
_i compound-statement i_
"A compound statement groups other statements together in an SQL
procedure. A compound statement allows the declaration of SQL variables,
cursors, and condition handlers.
...
SQL-condition-declaration
|--DECLARE--SQL-condition-name---------------------------------->
.-VALUE-.
.-SQLSTATE--+-------+-.
>--CONDITION--FOR--+---------------------+--string-constant-----|
return-codes-declaration
|--DECLARE------------------------------------------------------>
.-DEFAULT--'00000'---------.
>--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--|
| '-CHAR(5)------' '-DEFAULT--string-constant-' |
| .-DEFAULT--0----------------. |
'-SQLCODE--+-INTEGER-+--+---------------------------+------'
'-INT-----' '-DEFAULT--integer-constant-'
handler-declaration
|--DECLARE--+-CONTINUE-+--HANDLER FOR--------------------------->
+-EXIT-----+
'-UNDO-----'
(2)
>--+-specific-condition-value-+------SQL-procedure-statement----|
'-general-condition-value--'
Note (2): specific-condition-value and general-condition-value cannot be
specified in the same handler declaration.
specific-condition-value
.-,-------------------------------.
V .-VALUE-. |
|----+-SQLSTATE--+-------+--string-+-+--------------------------|
'-SQL-condition-name----------'
general-condition-value
|--+-SQLEXCEPTION-+---------------------------------------------|
+-SQLWARNING---+
'-NOT FOUND----'
..."
An example of a handler in a trigger:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyhandlersql.htm
midrange.com
