469,304 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

Multi-level Stored Procs and Savepoints

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
1 1279
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.

Similar topics

37 posts views Thread by ajikoe | last post: by
4 posts views Thread by Frank Jona | last post: by
5 posts views Thread by bobwansink | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.