DB2 SQL Trigger - can I call an RPGLE program from it?

[JimWaymire]

DB2 SQL Trigger - can I call an RPGLE program from it?

[MichaelCatalani]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

The trigger program can be RPGLE.

[B.Hauser]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

You can call any RPG program out of an SQL Routine body with the SQL Command CALL.
But to avoid problems with passing parameters incorrectly you should register the RPG program as stored procedure.

Birgitta

[jamief]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

Birgitta,

Would you post a small example of this???

Please
Jamie

[tomholden]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

a simple call statement would be

CL: CALL MYPGM

IIRC...(but my mind is slipping...)

[abercrombieande]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

birgitta,

would you post a small example of this???

Please
jamie

rpg

create procedure god.proctst ( 
	in process char(40) ) 
	language rpgle 
	specific god.proctst
	not deterministic 
	modifies sql data 
	called on null input 
	external name 'obj511/rpgpgm' 
	parameter style general ;

cl

create procedure god.proccallcl ( 
	in startdt char(10) , 
	in enddt char(10) ) 
	language cl 
	specific god.proccallcl
	not deterministic 
	modifies sql data 
	called on null input 
	external name 'obj511/clpgm' 
	parameter style general ;

[B.Hauser]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

Just to add: Creating an external stored procedure will work for all programs or ILE procedures without return value written in almost all programming languages (even RPGIII! or CLP).

Jamie I know you do not like CL, but here an example in CL:
1. CL Source Code: (Program Name GetJobQual)

            PGM        PARM(&ParJob &ParUser &ParJobNo)               
                                                                      
            DCL        VAR(&ParJob)   TYPE(*CHAR) LEN(10)     
            DCL        VAR(&ParUser)  TYPE(*CHAR) LEN(10)  
            DCL        VAR(&ParJobNo) TYPE(*CHAR) LEN(6)    
                                                                      
            RTVJOBA    JOB(&ParJob) USER(&ParUser) NBR(&ParJobNo)     
                                                                      
EOP:        RETURN                                                    
            ENDPGM

2. Registration as stored procedure using the SQL command CREATE PROCEDURE:

Create Procedure MYSCHEMA/GETJOBQUAL  
         (OUT POutJob    CHAR(10) , 
          OUT POutUser   CHAR(10) , 
          OUT POutJobNo  CHAR(6) ) 
       Language CL 
       Deterministic 
       No SQL 
       Called on NULL Input 
       External Name 'MYSCHEMA/GETJOBQUAL' 
       Parameter Style General;

Using the iNavigator Wizard makes registration even easier.
iSeries Navigator --> Database --> Open Database --> Right click on the Schema --> New --> Procedure --> External

After registration you can call this CL from any SQL interface using the CL Command CALL:
You may test this procedure using iSeries Navigator's Run an SQL Script. (NOT STRSQL!!!).
Because there are only output paramters you need to pass parameter markers (?). (See attachment)

A program without parameters can be called directly without any problems.
Just open STRSQL and type

CALL QUSCMDLN

or

CALL QCMD

and see what happens.

Calling API QCMDEXC is different because IBM already registered QCMDEXC as external stored procedure.
Just type

CALL QCMDEXC('WRKSPLF', 7)

in STRSQL.

Birgitta

[jamief]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

Thank you Birgitta... Nice work, as always
jamie

[JimWaymire]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

I reviewed all your answers, but still am at a loss. When I create the sql procedure, I
get an error saying it cannot find the program.

Here is a partial copy of the sql txt:

CREATE TRIGGER JDWLIB/TRKHDRTRG
AFTER INSERT on JDWLIB/ORDHDRPF
REFERENCING
NEW ROW as NewRow
FOR EACH ROW MODE DB2ROW

begin

DECLARE OldRow Char(291);
DECLARE NewRow Char(291);

Call JDWLIB/TRKHDR01(:NewRow,:OldRow);
End;


Here are the objects for the RPGLE program:

Opt Object Type Library Attribute Text
TRKHDR01 *PGM JDWLIB RPGLE Tracking System - Order Hea
TRKHDR01 *MODULE JDWLIB RPGLE Tracking System - Order Hea


What in the world am I doing wrong???????

Thanks,
Jim

[abercrombieande]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

I reviewed all your answers, but still am at a loss. When I create the sql procedure, I
get an error saying it cannot find the program.

Here is a partial copy of the sql txt:

CREATE TRIGGER JDWLIB/TRKHDRTRG
AFTER INSERT on JDWLIB/ORDHDRPF
REFERENCING
NEW ROW as NewRow
FOR EACH ROW MODE DB2ROW

begin

DECLARE OldRow Char(291);
DECLARE NewRow Char(291);

Call JDWLIB/TRKHDR01(:NewRow,:OldRow);
End;


Here are the objects for the RPGLE program:

Opt Object Type Library Attribute Text
TRKHDR01 *PGM JDWLIB RPGLE Tracking System - Order Hea
TRKHDR01 *MODULE JDWLIB RPGLE Tracking System - Order Hea


What in the world am I doing wrong???????

Thanks,
Jim

Can you post your procedure code?

[JimWaymire]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

Can you post your procedure code?

[abercrombieande]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

Call JDWLIB/TRKHDR01

I am assuming this is a procedure ? We don't really need the trigger that calls the procedure unless there is a problem with the trigger itself.

[JimWaymire]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

The trigger is a sql trigger, which calls a sql procedure TRKHDRTRG, which calls an rpg program TRKHDR01.




(

Call JDWLIB/TRKHDR01

I am assuming this is a procedure ? We don't really need the trigger that calls the procedure unless there is a problem with the trigger itself.

[abercrombieande]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

The code you posted for the trigger does not call the procedure. All you did was name the trigger TRKHDRTRG. You need to change that call statement to be the procedure not the RPG program

[JimWaymire]

Re: DB2 SQL Trigger - can I call an RPGLE program from it?

No, that is not what I want. I did an earlier SQL Trigger that calls a Sql Procedure and writes data to a file.
That works fine.
Now I want this SQL Trigger to call an RPGLE program, called TRDHDR01.
The Sql Procedure will not compile, because it says it cannot find TRDHDR01.
TRDHDR01 is an RPGLE Program, which does exist.............