RE: Stored Procedure question

You may want to check out this redpaper too.

http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/redp0004.h
tml?Open

-- Scott Johnson

>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
> > Mike.Crump@xxxxxxxxxxxxxxxx
> > Sent: Wednesday, April 23, 2003 10:11 AM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: Stored Procedure question
> >
> >
> > I'm working on our first attempt with a stored procedure.
> > Effectively, I
> > have an external website that is requesting us to run a program and
> > generate a PDF file on the fly and place it back on the
> > calling website
> > (also an AS/400).  So, this is not your typical type of SQL stored
> > procedure and I am not returning a data set to the calling program.
> >
> > Effectively, the calling program sends the header data
> > (customer, item,
> > etc.) and we return the success of the request and the name
> > of the file we
> > have created via parameters.
> >
> > Here is the code I used to generate the SP:
> >
> > CREATE PROCEDURE SP(IN PROD CHAR(5), IN PRODSUFF CHAR(4), IN CUSTNO
> >       CHAR(9), OUT PDFfile CHAR(128), OUT ReturnValue CHAR(1) )
> >        LANGUAGE CL
> >        NOT DETERMINISTIC
> >        NO SQL
> >        EXTERNAL NAME LIBRARY/PROGRAM
> >
> > The calling program is a Notes agent and it is effectively
> doing this:
> >
> > CALL QGPL.SP ( '05425','0601','407580601','
> > ',' ')
> >
> > We've also tried just doing the call with the 3 input
> > parameters and it
> > also errors out.  With the current statement we are getting
> this error
> > (back to the Notes agent):
> >
> > Error code: 720
> > Error message:    IN, OUT, or INOUT not valid for parameter 4
> > in procedure
> > SP in QGPL.
> >
> > I'm not sure if I understand the error message.  Evidently I have a
> > parameter issues but I'm not sure what.  I've considered adding the
> > statement PARAMETER STYLE GENERAL to my create statement.
> Also, I've
> > hunted and not found any corresponding job on the target
> > AS/400 that also
> > might contain some job log information.  I see the job start
> > and end on the
> > system but I haven't been able to determine what job
> > description to modify
> > to help generate more job log information for the individual
> > job requests.
> >
> > So, can anyone shed light on the correctness of the create
> statement,
> > parameter requirements, info on the intent of the error
> > statement, can I
> > find more information about the error on the target AS/400,
> > and any other
> > problem determination techniques?  I've been through the DB
> > manual and the
> > two redbooks that cover the SP subject area and haven't had
> much luck.
> >
> >
> > Michael Crump
> > Saint-Gobain Containers
> > 1509 S. Macedonia Ave.
> > Muncie, IN  47302
> > (765)741-7696
> > (765)741-7012 f
> > (800)428-8642