CALL RPG-Pgm. from with SQL-Trigger Command

[Oliver Baumann]

Hello Newsgroup,

I try to work with a Trigger which calls a ILE-RPG-Pgm.
I start the Trigger with "ADDPFTTRG". For "Insert" and "Update" Functions it
works fine. By "Delete" Operations is the Situation, that the triggered
Record is not the Record I deleted in my Application. I don't know what
happens.
My Idea is now, to create a Trigger with a SQL Cmd : "Create TRIGGER...". As
I red in the IBM Documentation, it is not possible to call a ILE Pgm from an
interactive SQL Statement.
Is that true ?

Any information is welcome.

Thanks in Advance
Oliver

[Jonathan Ball]

Oliver Baumann wrote:
> Hello Newsgroup,
>
> I try to work with a Trigger which calls a ILE-RPG-Pgm.
> I start the Trigger with "ADDPFTTRG". For "Insert" and "Update"
Functions it
> works fine. By "Delete" Operations is the Situation, that the
triggered
> Record is not the Record I deleted in my Application. I don't know
what
> happens.
> My Idea is now, to create a Trigger with a SQL Cmd : "Create
TRIGGER...". As
> I red in the IBM Documentation, it is not possible to call a ILE Pgm
from an
> interactive SQL Statement.
> Is that true ?

No, it isn't true. You can call a high level language (HLL) program,
e.g. RPG, COBOL, C, CL, etc., from within a SQL trigger. However, you
first must register the program as an external stored procedure using
the CREATE PROCEDURE statement. Here's an example of an update trigger
that calls an RPGLE program:

1. Create a test table for the trigger:

create table ball.test_table (the_col char(5));

2. RPGLE program TEST8 to be called by the trigger:

h bnddir('QC2LE') dftactgrp(*no)

d Test8 pr
d newval 5
d Test8 pi
d newval 5

d CmdToRun pr 10i 0 extproc('system')
d Cmd * value options(*string)

/free
CmdToRun('sndmsg ''the value: ' +
%trim(newval) + ''' tomsgq(ball)');
// executes: sndmsg 'the value: xxxxx' tomsgq(ball)
return;
/end-free

3. Create an external stored procedure for the program:

create procedure ball.test8 (char(5))
language rpgle parameter style general
no sql
external name ball.test8

4. Create the trigger:

create trigger ball.check_update
after update of the_col on ball.test_table
referencing old as old_line
new as new_line
for each row
mode db2row
when (old_line.the_col = ' ' and new_line.the_col <> ' ')
begin
declare wcol char(5);
set wcol = new_line.the_col;
call ball.test8 (wcol) ;
end;

5. Test the trigger:

insert into ball.test_table values(' ');
update ball.test_table set the_col = 'abcde';

Message queue:

>From . . . : BALL 03/03/05 10:44:47
the value: abcde

[Oliver Baumann]

Hallo Jonathan,

from where do you have all this skills ? I need you by mý side for all the
sometimes curious requirements I have to perform.
Now, I try to follow your suggestion.

Thank you very much for your response.
Nice weekend.
Kind Regards
Oliver

"Jonathan Ball" <notg...@yahoo.com> schrieb im Newsbeitrag
news:1109875776....@f14g2000cwb.googlegroups.com...

[Jonathan Ball]

Oliver Baumann wrote:
> Hallo Jonathan,
>
> from where do you have all this skills ? I need you by mý side for all the
> sometimes curious requirements I have to perform.

Ich probiere immer etwas neu!

In fact, some of these skills (you're very kind) were
acquired in Germany: I had an 18 month project in
Wasserburg am Inn in 1985-86. I lived in Bad Endorf,
between Rosenheim and Prien am Chiemsee. It was one of
the best times of my adult life.

Wenn Sie ehrlich meine Fähigkeiten brauchen, rufen Sie
mich doch an! (001) 626.394.7900 Ich möchte gern
wieder ein Vertrag in Deutschland haben (und mein
Deutsch etwas vebessern!)

> Now, I try to follow your suggestion.

Gute Glück.