Midrange News for the IBM i Community


Posted by: clbirk
dumb question on trigger (not roy rogers horse)
has no ratings.
Published: 05 Oct 2012
Revised: 23 Jan 2013 - 1640 days ago
Last viewed on: 20 Jul 2017 (2818 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

dumb question on trigger (not roy rogers horse) Published by: clbirk on 05 Oct 2012 view comments(6)

Can a physical file trigger call a "CL command"..

 

Here is the scnerio, on a web page in php, if they click a certain button, I am going to write a row to a table, and I want it to activate a CL command that actually truth be told will call a s/36 ocl command that will do something.

I was using the easycom/aura "plugin" however now with new zend server that no longer works (free), and I know there is the xmlservice toolkit and I can do that, but I wondered if when setting up a trigger and it says program, can that be a cl command (even if that cl command has then an strs36prc command within it?

 

I guess an alternative is to call a stored procedure that would have a call like:

 

CALL QSYS.QCMDEXC('chgcurlib curlib(saleslib)',0000000023.00000);

 

and of course put in what I am after...  I can do the stuff with xmlservice but I thought I would give triggers a try...

 

 

 

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 16 days 4 hours 34 minutes ago

A trigger program wouldn't be a CL command, it would be a CL program. ADDPFTRG doesn't have any specific help on the type of program, so it doesn't look like CL is in any way excluded.

Trying to get at the data in the trigger buffers is going to be simpler in RPG than in CL. If it's easier to get the work done in CL than in RPG, I'd probably use an RPG trigger program to parse the parameters then call a CL program to do the real work.

As far as STRS36PRC in the CL (trigger or called from trigger) program, that shouldn't be a problem, in and of itself. Not sure what performance implications it would have though.

If the triggered action doesn't need to be done inline, you might also consider having the trigger program write to a *DTAQ. Then your CL (or whatever) would have a loop on receive data queue around whatever the real work is.

Posted by: clbirk
Premium member *
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 16 days 4 hours 27 minutes ago

I don't need to get at the buffers. You see whenever I add a record into this table (file), I want to call a process that will read this table and do a few things, some of which are on s/36 files on the box. 

 

The alternative is that I simply loop all day every minute and check for any new records in the table. I would prefer not to do that.

 

The dataque is an idea but again I have to mess with the new "toolkit" to do that. One other option that I could do is that I could create an rpg program that "listens" to a specified port, and from php "call" that port, and then that job could trigger what I need to have happen. One of the courses I took at common this time was one of scott klements on doing that sort of thing.

I probably should take the 5 minutes and put on the new toolkit and be done with it, but I was looking at some other ways to do it.

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 16 days 3 hours 59 minutes ago

So the short answer is yes, you can evoke a CL program from a Trigger. You could then, obviously submit the program you want to run to batch or QUSRNOMAX and have it run "now". Certainly don't call the program while the trigger has control over the task. Evoke the call to your program and return.

Posted by: Ringer
Premium member *
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 13 days 10 hours 40 minutes ago
Edited: Mon, 08 Oct, 2012 at 09:22:06 (1747 days ago)

Can't you use db2_connect and db2_execute to call a stored proc? (could do via AJAX too).  Not sure why you are wanting to use a trigger. You'll have to connect to the back end anyway. You can add your own error checking below.

CREATE PROCEDURE MYLIB/MYPGM
(IN inPartNum CHAR(20), OUT outError CHAR(80))
LANGUAGE RPGLE                     
NOT DETERMINISTIC                  
NO SQL                             
EXTERNAL NAME MYPGM         // finds *PGM in *LIBL

PARAMETER STYLE GENERAL ;          

--------------------------------------------------------

PHP code:

// *SYS and *NONE

$connArray = array('i5_naming'=>DB2_I5_NAMING_ON,
                             'i5_commit'=>DB2_I5_TXN_NO_COMMIT) ; 

$conn = db2_connect('*LOCAL','','', $connArray ) ;

$mySQL = "CALL MYPGM(?,?)";  // will find SYSPROCS "MYLIB "row via *LIBL and *PGM too
$stmt = db2_prepare($conn, $mySQL);

// Explicitly bind parameters 

$inPartNum = 'COZZI-TOOLS' ;
$outError = ' ' ;  

db2_bind_param($stmt, 1, "inPartNum ", DB2_PARAM_IN);
db2_bind_param($stmt, 2, "outError ", DB2_PARAM_OUT);

$status = db2_execute($stmt);

Chris Ringer

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 9 days 10 hours 53 minutes ago

Triggers are totally awesome, except when:

  1. You're copying a few hundred thousand records into a file, and each record copied in triggers a call.
  2. You're adding records from a web browser, and the trigger calls another trigger calls another...., and the website presentation software has a 15sec timeout for server response.

I kind of prefer a trigger to write an entry to a data queue (or something similar) and have batch processing perform the necessary updates. You're not saving any processing cycles on the server, but the interactive/web response doesn't get bogged down waiting for the cascading triggers to all finish.

Posted by: Ringer
Premium member *
Comment on: dumb question on trigger (not roy rogers horse)
Posted: 4 years 9 months 9 days 10 hours 34 minutes ago

Yeah or when a developer does a CRTDUPOBJ on a PF into his/her personal library and starts messing with their 'test' data but does not realize they are firing triggers against production data. Ooops.

CHGCMDDFT CMD(CRTDUPOBJ) NEWDFT('TRG(*NO)')

Chris Ringer