First, I'm not a fan of putting transaction control inside of stored procedures if those procedures are going to be called from a language such as C, 4GL, Java, etc. In my view, the top-level program should be the one controlling the scope of a transaction. But I know that there are some applications that are written completely in SPL, so there are cases where this is appropriate.
What I've done in the past is something like this:
create procedure "owner".my_procedure ( i_effective_date date,
i_user_id integer)
define p_logging_supported smallint;
define p_in_transaction smallint;
define p_sql_error integer;
define p_isam_error integer;
define p_err_text varchar(72);
let p_logging_supported = 0;
let p_in_transaction = 0;
--
-- main exception handler
--
on exception
set p_sql_error, p_isam_error, p_err_text
.
.
.
end exception
--
-- check for transaction support
--
begin
on exception
set p_sql_error, p_isam_error, p_err_text
if p_sql_error = -256 -- Transaction not available
then
let p_logging_supported = 0;
let p_in_transaction = 0;
elif p_sql_error = -535 -- Already in transaction
then
let p_logging_supported = 1;
let p_in_transaction = 1;
elif p_sql_error = 0 -- 'begin work' was successful
then
let p_logging_supported = 1;
let p_in_transaction = 1;
else -- some other happened
raise exception p_sql_error, p_isam_error, p_err_text;
end if;
end exception
begin work;
end
--
-- main procedure logic
--
.
.
.
end procedure;
Just put that BEGIN / END block as the first bit of logic inside each procedure.
There is another benefit of this approach, which answers another question you raised in your other thread. If you use this consistently, you can tell when one procedure is called from another procedure. If procedure A executes a BEGIN WORK, then calls procedure B, when procedure B attempts to execute the BEGIN WORK, it will get the -535 SQLCODE. So you could add another procedure variable:
define p_called_from_another_proc smallint;
and then inside the 'if' statement for -535, set that variable to 1.
Of course, your procedure would then need to perform whatever logic to check for conditions to determine whether you want to COMMIT or ROLLBACK the transaction. You also need to include logic to check your p_in_transaction and p_called_from_another_proc variables. You probably don't want to do a ROLLBACK if the procedure was called from another procedure, as the top-level procedure should control the boundaries of the transaction.
if p_logging_supported = 1
then
if p_in_transaction = 1
then
if p_called_from_another_proc = 0 -- this is the top-level procedure
then
if p_no_errors_so_far = 1
then
commit work;
else -- errors occurred somewhere in this procedure, so rollback
rollback work;
end if; -- if p_no_errors_so_far = 1
end if; -- if p_called_from_another_proc = 0
end if; -- if p_in_transaction = 1
end if; -- if p_logging_supported = 1
This is from memory, so you may have to tweak it a bit.
------------------------------
Mark Collins
------------------------------
Original Message:
Sent: Wed May 11, 2022 06:09 AM
From: Antonio Ferrara
Subject: SPL transactions
Could you please give me an example of how to properly manage transactions in an SPL?
Thanks.
------------------------------
Antonio Ferrara
------------------------------
#Informix