Re: trigger question

il 12/03/2009 13.21, Scrive tom 43067616:
Hi.

I have a physical file.
I need to make an SQL insert into that table with specified values.
One of field gets for example value X000111. After that insert I need
to call CL pgm with value X000111 as a parameter. I would like to do it
using an SQL trigger.
Using an SQL I can find value I'm looking for (X000111) and then I need to call my CL with parameter. How to do it?
My colegue who knows RPG a little tried to create such a trigger:

CREATE TRIGGER fedxh1p_insert_trigger AFTER INSERT ON fedxh1p
REFERENCING NEW ROW AS N FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE XXX CHAR(9);
SET XXX = N.XHTSNO;
CALL EDIFMS4SPR(' ',' ',' ','PROCESS ', XXX, '*TEST');
END

where EDIFMS4SPR is stored procedure which calls my CL.

EDIFMS4SPR looks like:

C/exec sql
C+ CREATE PROCEDURE EDIFMS4SPR
C+ (IN P1 CHAR(2),
C+ IN P2 CHAR(7),
C+ IN P3 CHAR(10),
C+ IN P4 CHAR(8),
C+ IN P5 CHAR(9),
C+ IN P6 CHAR(5))
C+ EXTERNAL NAME COMPWINLIB/EDIFMS4S
*+ LANGUAGE CL
C/end-exec
C eval *inlr = *on

when we try to do an insert we get error:

Message ID . . . . . . : SQL0723 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : SQL trigger FEDXH1P_INSERT_TRIGGER in *N failed with
SQLCODE -440 SQLSTATE 42884.
Cause . . . . . : An error has occurred in a triggered SQL statement in
trigger FEDXH1P_INSERT_TRIGGER in schema *N. The SQLCODE is -440, the
SQLSTATE is 42884, and the message is Routine EDIFMS4S in COMPWINLIB not
found with specified parameters..
Recovery . . . : Refer to the joblog for more information regarding the
detected error. Correct the error and try the request again.

Does anybody has any idea what possibly we did wrong?

Regards,
Tomasz
The message tells you that the number of parameters declared do not match the number of parameters accepted by the external prgram. I guess that's because the parameter style assumed by the create prrocedure is SQL, while possibly you should specify GENERAL. The parameter style SQL is a standard interface with a fixed parameter list, as specified in source file QSYSINC/H member SQLUDF, if the parameter list you specified in the procedure matches the parameter list of the CL program you must specify PARAMETER STYLE GENERAL in the create procedure statement.

--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñejoAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'
.

Relevant Pages

  • Re: Dont want to wrap long text - just truncate
    ... document then postprocess the results using VBA, ... All you can do in the standard user interface is specify a table of view, ... you have to specify the SQL in an OpenDataSource method call in VBA. ... via MS Query, MS Query has to be installed, and you will need an ODBC DSN ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Dont want to wrap long text - just truncate
    ... >new document then postprocess the results using VBA, ... > and specify a bit of sorting and filtering (go into the Edit Recipients ... To do more, you either have to go through MS Query, ... > or you have to specify the SQL in an OpenDataSource method call in VBA. ...
    (microsoft.public.word.mailmerge.fields)
  • RE: problems with visual studio 2008 on vista
    ... Administrative utility to install and uninstall ASP.NET features on a SQL ... -d <database> Database name for use with application services. ... specify a database and ensure that the web.config file is modified ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: sliced find in Linq
    ... by over all of the returned columns. ... Since the original query was unordered, I'd like the above SQL to ... (Perhaps your table doesn't have a primary key though?) ... didn't specify the ordering, so any ordering could have been used. ...
    (microsoft.public.dotnet.languages.csharp)
  • group by errors
    ... Creating a view in SQL and use Access to open the view ... >specify ALL with the CUBE or ROLLUP operators. ... the query. ... >summary rows are introduced into the result set. ...
    (microsoft.public.access.queries)
Loading