brainchime.com Latest Questions

Admin
Asked: 2024-10-06T19:51:01+05:30 In:

How to call sql stored procedure in cl program?

I have written a SQL stored procedure and I have to call that SQL stored procedure in the CL program. I am facing an issue in calling the SQL stored procedure from the CL program. I can call it using the SQL CALL statement in the RPG program but I can not use the same SQL CALL statement in the CL program by any means, I tried calling it using the procedure call as we call CL or RPG program but that again did not work and caused MCH3601. This is my SQL stored procedure script that takes two input parameters only and inserts data in a table.

CREATE OR REPLACE PROCEDURE STORED33B(
        IN P_NAME CHAR(20),
        IN P_GENDER CHAR(1)
         )
SPECIFIC STORED33B
BEGIN
INSERT INTO PF8_D(NAME,GENDER) VALUES(P_NAME,P_GENDER);
END

Is there any way using which I can call an SQL stored procedure from within the CL program?

as400control-languagedb2stored-procedure

Related Questions

Leave an answer

Leave an answer

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

1 Answer

  1. Admin

    We can call SQL stored procedure from within the CL program using the QZDFMDB2 program which is the datalink file manager DB2 CLP program present in the QSYS library.

    pgm                                                     
dcl        var(&cmd) type(*char) len(200)               
dcl        var(&in1) type(*char) len(20) value('test') 
dcl        var(&in2) type(*char) len(1)  value('F')                                                                                                                               
               
CHGVAR     VAR(&CMD) VALUE('CALL +                      
             testlib.STORED33B(''' *CAT &IN1 *TCAT +  
             ''' , ''' *CAT &IN2 *TCAT ''')')                  
call       pgm(qzdfmdb2) parm((&cmd))   
                  
return                                                   
endpgm

    We need to build the command string which is the SQL stored procedure CALL statement with the required parameter. Later we pass that command string to the program qzdfmdb2 which executes SQL interactively from within the CL program.