RE: SQL0440 with Trigger and Stored Procedure

Try to declare the parameters in the CREATE TRIGGER command to ensure
proper parameter passing:

CREATE TRIGGER MFPUT_T1 AFTER INSERT ON mylib/MFPUT

SP1: BEGIN
 DECLARE QVERSION char(1) default 'D' ;
 DECLARE QSERVER char(10) default 'FTPSERVER ' ;
 DECLARE QFTPUSER char(10) default 'FTPUSER   ' ;
 DECLARE QPARMLIB char(10) default 'mylib     ' ;
 DECLARE QPARMMBR char(10) default 'MEMBER    ' ;
CALL MFPUT_SP1 (QVERSION, QSERVER, QFTPUSER, QPARMLIB, QPARMMBR);
END SP1



Shalom Carmel
----------------------
www.venera.com - Exposing iSeries insecurity


> -----Original Message-----
> Subject: SQL0440 with Trigger and Stored Procedure
>
> I'm trying to get a stored procedure working.  Just to let you know...
> this is the first time I've try to do this.  Ok.  I created a stored
> procedure with the following syntax :
> CREATE PROCEDURE MFPUT_SP1 (CHAR(1), CHAR(10), CHAR(10), CHAR(10), CHAR
> (10)) EXTERNAL NAME mylib/OURFTP LANGUAGE CL
>
> This Stored procedure will be called by a database trigger that was
> defined by the following syntax :
> CREATE TRIGGER MFPUT_T1 AFTER INSERT ON mylib/MFPUT CALL MFPUT_SP1
> ('D', 'FTPSERVER ', 'FTPUSER   ', 'mylib     ', 'MEMBER    ')
>
> So, after a record is added to table MFPUT I want the Trigger to call
> my Stored Procedure which runs my CL program. See, simple.
>
> The CL program has the following parameters :
>  DCL    VAR(&VERSION)   TYPE(*CHAR)   LEN(1)
>  DCL    VAR(&SERVER)    TYPE(*CHAR)   LEN(10)
>  DCL    VAR(&FTPUSER)   TYPE(*CHAR)   LEN(10)
>  DCL    VAR(&PARMLIB)   TYPE(*CHAR)   LEN(10)
>  DCL    VAR(&PARMMBR)   TYPE(*CHAR)   LEN(10)
>
> When I start another session and use DFU to add a record I get the
> following error :
> SQL0440 : Routine OURFTP in mylib not found with specified parameters
> SQL0723 : SQL trigger MFPUT_TI in *N failed with SQLCODE -440 SQLSTATE
> 42884.
>
> The mylib library is in the System part of the libary list and the
> userid has access to the library.
>
> Any thoughts?
> m.
>