ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

program/module returned from sql function

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • program/module returned from sql function

    I've made an SQL trigger to log a file when a field is updated.
    Code:
    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
    Code:
    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.

  • #2
    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
    Code:
    create function ASTEST2/PgmProc()
    returns char
    external name 'ASTEST2/PGMPROC(PGMPROC)'
    language rpgle
    parameter style db2sql
    no sql
    not deterministic
    disallow parallel

    Comment


    • #3
      Since it is a function, you just call it with SET

      Code:
      SET YourSQLVar = ASTEST2.PGMPROC();
      ... but your function will not return the program name that fired the trigger.

      Birgitta

      Comment


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

        Comment

        Working...
        X