DB2 SQL Trigger - can I call an RPGLE program from it?
Announcement
Collapse
No announcement yet.
DB2 SQL Trigger - can I call an RPGLE program from it?
Collapse
X
-
Re: DB2 SQL Trigger - can I call an RPGLE program from it?
The trigger program can be RPGLE.Michael Catalani
IS Director, eCommerce & Web Development
Acceptance Insurance Corporation
www.AcceptanceInsurance.com
www.ProvatoSys.com
-
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
Comment
-
Re: DB2 SQL Trigger - can I call an RPGLE program from it?
Birgitta,
Would you post a small example of this???
Please
JamieAll my answers were extracted from the "Big Dummy's Guide to the As400"
and I take no responsibility for any of them.
www.code400.com
Comment
-
Re: DB2 SQL Trigger - can I call an RPGLE program from it?
Originally posted by jamief View Postbirgitta,
would you post a small example of this???
Please
jamie
Code: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 ;
Code: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 ;
Comment
-
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)
Code: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
Code: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;
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 typeCode:CALL QUSCMDLN
Code:CALL QCMD
Calling API QCMDEXC is different because IBM already registered QCMDEXC as external stored procedure.
Just typeCode:CALL QCMDEXC('WRKSPLF', 7)
BirgittaLast edited by B.Hauser; July 30, 2011, 02:23 AM.
Comment
-
Re: DB2 SQL Trigger - can I call an RPGLE program from it?
Thank you Birgitta... Nice work, as always
jamieAll my answers were extracted from the "Big Dummy's Guide to the As400"
and I take no responsibility for any of them.
www.code400.com
Comment
-
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,
JimJim Waymire
Comment
-
Re: DB2 SQL Trigger - can I call an RPGLE program from it?
Originally posted by JimWaymire View PostI 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
Comment
-
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.
(Originally posted by abercrombieande View PostCode:Call JDWLIB/TRKHDR01
Jim Waymire
Comment
-
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.............Jim Waymire
Comment
Comment