Alix Jermyn wrote :
>Thanks for you help to date guys - I would appreciate your clarification on
>a couple of points within dbprocs.
>1: The simple dbproc below does two inserts, into the parent and child
>table. My understanding from the documentation was that the substrans
>rollback forced the rollback of all work within the dbproc (starting from
>the subtrans begin statement), but processing continued on from there. I
>would like the rollback to occur, and then an exit with the error code that
>caused it. However, the stop statements are never executed - after the
>rollback correctly actions, the dbproc is exited immediately. If I comment
>out the rollback, and go directly to the stop, I get the action I would
>like, but can you confirm that the stop statement has an implicit rollback
>within it (implicit rollbacks do seem to occur when the stop is executed,
>but can you confirm that this is the expected behaviour).
>2: Has the try catch process been fixed within dbprocs? I sometimes get
>some weird behaviour, and see from the mailing list archive that this was a
>known problem which ws going to be fixed. Has it been for 7.3.latestest?
>Thanks
>// drop dbproc Rule_MinMax_create
>//
>create dbproc Rule_MinMax_create (
>in Name varchar(30),
>in UseMin Boolean,
>in Min integer,
>in UseMax Boolean,
>in Max integer
>)
>as
>var Id integer;
>begin
>subtrans begin;
>set Id = "JET"."RULE_SEQUENCE".nextval;
>insert into "JET"."RULE_HEADER"("Id", "Name", "Type") values (:Id, :Name,
<1);
>if $rc <> 0 then begin
>/* subtrans rollback; */
>stop($rc, $errmsg);
>end;
>insert into "JET"."RULE_MINMAX"("Id", "UseMin", "Min", "UseMax", "Max")
>values(200, :UseMin, :Min, :UseMax, :Max);
>if $rc <> 0 then begin
>/* subtrans rollback; */
>stop($rc, $errmsg);
>end;
>subtrans end;
>end;
>//
>// call Rule_MinMax_create ('alix_206', false, 230, false, 237)
>// select H.*, R.* from "JET"."RULE_HEADER" H left outer join
>"JET"."RULE_MINMAX" R on H."Id" = R."Id" union select H.*, R.* from
>"JET"."RULE_HEADER" H right outer join "JET"."RULE_MINMAX" R on H."Id" =
>R."Id"
>###########################################
1. The variable $rc contains the sql code of the last sql statement
executed in your db-procedure. The rollback subtrans statement is
a sql statement, i.e. after having executed that statement $rc is hopefully 0.
This means that you exit the procedure immediately without any error. Please
save $rc and $errmsg into a local variables to solve the problem :
var
rc integer;
errmsg char(80);
...
if $rc <> 0 then begin
rc = $rc;
errmsg = $errmsg;
subtrans rollback;
stop(rc, errmsg);
end;
2. There are no known bugs concerning try/catch in the latest 7.3 release.
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
