1

I am trying to call a external stored procedure from Sql stored procedure by passing two parameters. one of the parameters is expected to return back with a string value, which when arrives I need to stop the data to be committed in any file.

To explain the situation - SP1 (SQL stored procedure) will call SP2 (External stored procedure), which will call RPGLE program PGM1, which will call another RPGLE program PGM2.

Now I am tasked to handle commitment control of File1 and File 2 used in PGM2 from SP1. If at any point File1 is updated and File2 gives an error while updating any record, data from File 1 should also be rolled back. but this rollback should happen in SP1.

So far I have tried to split this issue in two parts-

PARTA - How to Call External stored procedure from SQL stored procedure.

PARTB - How to handle commitment in SQL stored procedure in essence, if PGM2 gives back error data should be rolled back.

Below is the piece of code so far I have tried. But have no luck.

CREATE OR REPLACE PROCEDURE MYLIB.SP1 (    
                    IN PRINPUT CHAR(1200) ,       
                    INOUT PRERR CHAR(50) )        
SPECIFIC MYLIB.SP1                         
BEGIN                                             
  DECLARE SQLSTATE CHAR(5) DEFAULT ' ';           
  DECLARE SQLCODE INTEGER DEFAULT 0;              
    CALL MYLIB.SP2(PRINPUT, PRERR);      
      IF SQLCODE = 0 THEN                         
        COMMIT;                                   
      ENDIF;                                      
END

Any suggestion/Guidance is appreciated.

mrsorrted
  • 141
  • 1
  • 8
  • 1
    consider doing inserts, updates and deletes in RPG using SQL. That way you do not need to run STRCMTCTL to update a record in the RPG code. Also, to call RPG from an SQL procedure you do not need to create another stored procedure. Just call the RPG directly. – RockBoro Mar 28 '22 at 17:42

2 Answers2

1

Try this my friend: On SP1

BEGIN                                             
  DECLARE SQLSTATE CHAR(5) DEFAULT ' ';           
  DECLARE SQLCODE INTEGER DEFAULT 0;    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;         
    CALL MYLIB.SP2(PRINPUT, PRERR);      
      IF SQLCODE = 0 THEN                         
        COMMIT;
      ELSE 
        ROLLBACK;                          
      ENDIF;                                      
END

Answers:

PARTA: If the Ext Stored Proc exists, the way you're calling it, it's correct.

PARTB: The set transaction scopes all the rows changed from the moment is issued, to the execution of commit or rollback. Remember this, every SQL Stored proc runs on *caller actgrp, so, you need to check if your RPG program runs on *caller too.

Finally, last time I tested, dinos still walked the earth, the commit on SQL Stored Proc scoped the changes made with a RPG program called within, but the RPG STRCMTCTL doesn't get the changes made on SQL Stored Proc called within the RPG.

Have fun!

statements-set-transaction

control-example-using-transaction-logging-file-start-application

definition-example-jobs-commitment-definitions

Jairo R. Flores
  • 724
  • 4
  • 11
  • Thank you @jairo, I am getting below error which doesn't make sense. SQL7032 on Record 1 -> Position 1 SQL procedure, function, trigger, or variable SP1 in MYLIB not created. – mrsorrted Mar 29 '22 at 07:24
  • Just to update, I gave it a try from RUN SQL scripts interface and MYLIB.SP1 procedure was created successfully, however commitment control isn't happening on FILE1 and FILE2 in PGM2 .. will update this thread if any process is made. – mrsorrted Mar 29 '22 at 08:15
  • @mrsorrted - The fact that you call this a "thread" and seem to want to make ongoing updates as progress is made suggests to me that you're not really using Stack Overflow as intended. I strongly recommend subscribing to some of the [midrange.com mailing lists](https://www.midrange.com/#lists) (at least MIDRANGE-L and RPG400-L) and/or the [IBM i OSS community on Ryver](https://ibmioss.ryver.com/application/signup/members/9tJsXDG7_iSSi1Q) and discuss your situation in one of those places. – John Y Mar 29 '22 at 14:14
  • Thank you for the links @John, I shall follow the links and take my situation in one of those places. – mrsorrted Mar 29 '22 at 17:12
1

Thank you @Jairo, I got it working.

Your solution was correct and it worked, only challenge was the SP2 didn't have any SQL statements which was causing issue, I converted the statements in SP2 to SQL statements and it worked completely fine.

mrsorrted
  • 141
  • 1
  • 8