View Only
  • 1.  SPL transactions

    Posted Wed May 11, 2022 06:10 AM
    Could you please give me an example of how to properly manage transactions in an SPL?

    Antonio Ferrara

  • 2.  RE: SPL transactions
    Best Answer

    Posted Thu May 12, 2022 10:25 AM
    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
    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
          if p_in_transaction = 1
              if p_called_from_another_proc = 0   -- this is the top-level procedure
                  if p_no_errors_so_far = 1
                      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

  • 3.  RE: SPL transactions

    Posted Thu May 12, 2022 11:04 AM
    You were very clear.
    You have solved all my doubts

    Antonio Ferrara