By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

Multi-level Stored Procs and Savepoints

P: n/a
The following situation turns into an error:

SP1 {
Savepoint
Create record in table 1

Call SP2 {
Create sub-records in table 2 referring to table 1
}

Commit / Rollback to savepoint
}
Now, beacuse of a referring index between table 2 and table 1, an error
occurred, because the record in table 1 does not exist when the
sub-records are created in table 2.

Do I something wrong, or is this a standard situation on DB2/OS400?

Twan Kennis
SKB Vragenlijst Services, automatisering
Amsterdam, The Nertherlands
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I solved the problem myself. :-D
What went wrong...

In SP2 I declared the following exception handler

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET pRETURN = SQLCODE;
ROLLBACK TO SAVEPOINT SAV1;
END;

(in which pReturn is an Output parameter)

Because of the SQLWARNING-option, the Stored Procedure failed.
When the exit handler only catches SQL-exceptions, everything goes well:

DECLARE EXIT HANDLER FOR SQLEXCEPTION

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands
================================================== =====================
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...
The following situation turns into an error:

SP1 {
Savepoint
Create record in table 1

Call SP2 {
Create sub-records in table 2 referring to table 1
}

Commit / Rollback to savepoint
}
Now, beacuse of a referring index between table 2 and table 1, an error occurred, because the record in table 1 does not exist when the
sub-records are created in table 2.

Do I something wrong, or is this a standard situation on DB2/OS400?

Twan Kennis
SKB Vragenlijst Services, automatisering
Amsterdam, The Nertherlands

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.