program/module returned from sql function

[c0nfitty]

I've made an SQL trigger to log a file when a field is updated.

Create Trigger edi04_xshadd
After update of xshadd on edilib/edi040f
Referencing old as old_dta
new as new_dta
For each row mode db2sql
When (old_dta.xshadd <> new_dta.xshadd)
Insert into astest2/cr_chglog
Values('EDILIB', 'EDI040F', 'XSHADD',
old_dta.xshadd,
new_dta.xshadd,
new_dta.xpocus, //ref#1
new_dta.xpo22, //ref#2
new_dta.XPODTE, //ref#3
new_dta.XSTORE, //ref#4
' ', //ref#5
current_user, current client_applname,
current_timestamp);

as you can see, I'm trying to use current client_applname to fetch the program used to update the file. I've found it works with DBU and interactive sql, however, it returns nothing from an RPGLE program.

I was thinking I could Create an SQL function that calls something like this.... but I'm lost

H nomain
/copy Maputil/qrpglesrc,util_belt

P PgmProc b export
D pi
D pgm 10a
/free
pgm = @mod;
return;
/end-free
P e

Util_Belt has the PSDS defined in it.
@mod is positions 1-10.

[c0nfitty]

this made a function... but not I'm lost on how to call it from SQL. I'm doing all this by trying to reverse engineer RTVDIR

create function ASTEST2/PgmProc()
returns char
external name 'ASTEST2/PGMPROC(PGMPROC)'
language rpgle
parameter style db2sql
no sql
not deterministic
disallow parallel

[B.Hauser]

Since it is a function, you just call it with SET

SET YourSQLVar = ASTEST2.PGMPROC();

... but your function will not return the program name that fired the trigger.

Birgitta

[c0nfitty]

I was planning on adding that to the trigger.... if possible.