RE: SQL0440 with Trigger and Stored Procedure

Hm, looks like the CALL'ing stored procedures is a little different than
UDF's.

I've used the char('literial') with UDF's and it works fine.

But from the manual, it looks like CALL won't accept it.  Instead, CALL
only works when casting BLOB, CLOB, DATE, TIME, TIMESTAMP, DBCLOB.

Weird.

You said Shalom's method works, but then said it fails with DFU.  What
does it work with?

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> msmith6@xxxxxxxxxxxx
> Sent: Wednesday, November 30, 2005 3:08 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: RE: SQL0440 with Trigger and Stored Procedure
> 
> I must be doing something wrong. I've tried most everything 
> folks have 
> suggested and still no deal.  I keep getting *stupid* production 
> problems all day and I haven't had as much time to try this.
> 
> I entered the following :
> CREATE PROCEDURE mylib/MFPUT_SP1 (CHAR(1), CHAR(10), CHAR(10), CHAR
> (10), CHAR(10)) EXTERNAL NAME Mylib/MyFTPPGM LANGUAGE CL 
> 
> then :
> CREATE TRIGGER MFPUT_TI AFTER INSERT ON mylib/MFPUT   
> CALL mylib/MFPUT_SP1 (CHAR('D'),                      
>                           CHAR('FTPSERVER', 10),          
>                           CHAR('FTPUSER1', 10),          
>                           CHAR('mylib', 10),          
>                           CHAR('member', 10))              
> 
> I get "Token CHAR was not valid. Valid tokens: : BLOB CLOB 
> DATE DBCLOB 
> DLVA..." back. 
> 
> The only 'casted' parameter list that worked was :
> 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
> 
> I still got the SQL0440 error when adding the record via DFU to MFPUT
> The parameters passed to MyFTPPGM are :
> &Version Char(1)
> &Server  Char(10)
> &FTPUID  Char(10)
> &ParmLib Char(10)
> &ParmMbr Char(10)
> 
> 
> > ------------------------------
> > 
> > message: 2
> > date: Wed, 30 Nov 2005 13:18:01 -0500
> > from: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
> > subject: RE: SQL0440 with Trigger and Stored Procedure
> > 
> > The problem is that SQL treats character literals as VARCHAR.
> > 
> > You need to either: change your stored procedure to accept VARCHAR
> > instead of CHAR.
> > 
> > Or explicitly tell SQL that the literals are CHAR and not VARCHAR 
> like
> > so:
> > CALL MFPUT_SP1 (char('D'), 
> >                char('FTPSERVER '), 
> >                char('FTPUSER   '), 
> >                char('mylib     '), 
> >                char('MEMBER    '))
> > 
> > Or even
> > 
> > CALL MFPUT_SP1 (char('D'), 
> >                char('FTPSERVER' , 10), 
> >                char('FTPUSER' , 10), 
> >                char('mylib' , 10), 
> >                char('MEMBER' , 10))
> > 
> > 
> > Charles Wilt
> > --
> > iSeries Systems Administrator / Developer
> > Mitsubishi Electric Automotive America
> > ph: 513-573-4343
> > fax: 513-398-1121
> >  
> > 
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx 
> > > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> > > msmith6@xxxxxxxxxxxx
> > > Sent: Tuesday, November 29, 2005 4:34 PM
> > > To: midrange-l@xxxxxxxxxxxx
> > > Subject: SQL0440 with Trigger and Stored Procedure
> > > 
> > > > from: Colin Williams <colinwilliams007@xxxxxxxxx>
> > > > subject: Re: SQL0440 with Trigger and Stored Procedure
> > > > 
> > > > If the program is definitely in that library, I would 
> suspect it 
> > > > doesnt like the parameters for some reason!
> > > > 
> > > > Its expected you to pass 10 long  CHAR fields, is that 
> definitely 
> > > > what you calling it with?
> > > 
> > > In the trigger definition I created, I padded the 
> parameters  with 
> > > spaces to make sure they were the length the Stored Procedure was 
> expecting.
> > >       CREATE TRIGGER MFPUT_T1 AFTER INSERT ON mylib/MFPUT CALL 
> MFPUT_SP1 
> > >        ('D', 'FTPSERVER ', 'FTPUSER   ', 'mylib     ', 'MEMBER')
> > > 
> > > What kicks this whole thing off is adding a record to a  
> database. 
> It 
> > > has three fields of no real concern.  None of the data in it is 
> used 
> > > in this procedure.  I'm just using it as the event. 
> > 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>