Re: Fwd: Execute Failed with Stored Procedure Call

[Michael Ryan <michaelrtr@xxxxxxxxx>]

As a followup, I the QCCSID system value is set to 65535. So that's not
going to translate, right?

Do I want something like:

****$options = array('binmode' => DB2_PASSTHRU);
$option_result = db2_set_option($stmt, $options, 1);
**
Or should I set the data type parameters in the db2_bind_param call, like
this:

db2_bind_param($stmt, 1, "vendor", DB2_PARAM_IN, DB2_CHAR);

TIA...

(also posted to WEB400...I think it's a PHP thing now)

On Thu, Dec 2, 2010 at 11:56 AM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

> This may be a stupid question, so be gentle. Is there any ASCII-EBCDIC
> translation when calling an iSeries stored procedure from a PHP script? I'm
> thinking not...sigh...
>
> Where should I do the translation? On the iSeries server I would think,
> right? Anyone have any routines to do this? TIA...
>
>
> On Thu, Dec 2, 2010 at 9:57 AM, hockchai Lim <
> lim.hock-chai@xxxxxxxxxxxxxxx> wrote:
>
> > In our shop, we always specific the "Result sets" keyword if the RPG
> > returns
> > a result set (Again, you say it works in navigator without it.  So...
> > puzzling).  As for the return value, I'm assuming that you're talking
> > about
> > the value 1 in the "Result Sets 1" example that I provided.  If so, that 1
> > is not telling the client that there is only one row in the result set.
> > Rather, it is telling the system that this procedure return a result set
> > (this result set can have as many row as you want).  Of course, this also
> > means that a procedure can actaully returns more than one result set (I
> > personally have never done that before).
> >
> >
> >
> >
> > "Michael Ryan" <michaelrtr@xxxxxxxxx> wrote in message
> > news:mailman.17472.1291301286.2702.rpg400-l@xxxxxxxxxxxxxxx
> > > Hi Lim -
> > >
> > > I was wondering about that. I'm not returning a result set per se...I
> > > don't
> > > think...I'm returning an array (MODS). Should I still have the 'Result
> > > Sets
> > > 1' clause in the CREATE PROCEDURE? Also, do I need to specify the
> > returned
> > > value? I have a MODS with up to 500 occurrences with each entry being 71
> > > bytes, so I could return up to 35500 bytes. Do I specify that in my
> > > procedure? btw, here's the output when I call the stored procedure from
> > > Navigator:
> > >
> > > ================
> > > > call inventory.spr101r('MYVENDOR', 'MYMODEL')
> > >
> > > Return Code = 0
> > >
> > >
> > > SQL State: 0100C
> > > Vendor Code: 466
> > > Message: [SQL0466] 1 result sets are available from procedure SPR101R in
> > > INVENTORY. Cause . . . . . :   Procedure SPR101R in INVENTORY was called
> > > and
> > > has returned one or more result sets. Recovery  . . . :   None.
> > >
> > > Statement ran successfully, with warnings   (250 ms)
> > > =================
> > > Weird thought...it's not calling my program with the same name is it?
> > >
> > >
> > > On Thu, Dec 2, 2010 at 9:23 AM, hockchai Lim
> > > <lim.hock-chai@xxxxxxxxxxxxxxx>wrote:
> > >
> > > > Michael,
> > > > I did notice that your CREATE PROCEDURE statement does not have the
> > > > "Result
> > > > Sets" keyword to sepecify that the procedure returns a result set.  But
> > > > you
> > > > also said that it works from navigator.  So, I'm not sure.  Here is one
> > > > of
> > > > the CREATE PROCEDURE example that I've:
> > > >
> > > > CREATE PROCEDURE AILDEVC1SP
> > > > (
> > > >     In    CunoIn       DEC(7,0),
> > > >     In    TrackID      Char(29),
> > > >     In    SORTCODE     DEC(1,0),
> > > >     In    CptlaIn      char(3),
> > > >     In    CptlpIn      char(3),
> > > >     In    CptlsIn      char(4),
> > > >     In    CptlxIn      char(7),
> > > >     In    CyhldrIn     Char(30),
> > > >     In    Address      Char(255),
> > > >     In    PageNumber   DEC(8,0),
> > > >     In    GrpPerPage   DEC(8,0),
> > > >     Out   TotalRec     DEC(8,0),
> > > >     Out   TotalDev     DEC(8,0),
> > > >     InOut RETURNKEY    CHAR(300)
> > > > )
> > > > Result Sets 1
> > > > Language RPGLE
> > > > External name AILDEVC1RP
> > > >
> > > >
> > > > "Michael Ryan" <michaelrtr@xxxxxxxxx> wrote in message
> > > > news:mailman.17465.1291299070.2702.rpg400-l@xxxxxxxxxxxxxxx
> > > > > Hmmm...that makes the db2_execute error go away. I'm not getting any
> > > > > output,
> > > > > but that could very well be a different issue. Thanks Glenn!
> > > > >
> > > > > On Thu, Dec 2, 2010 at 8:41 AM, Glenn Hopwood
> > > > > <ghopwood.list@xxxxxxxxx>wrote:
> > > > >
> > > > > > I'm not sure if it's your problem, but I don't think you should have
> > > > > > the
> > > > > > '$' in front of your variable names in the db2_bind_param()
> > functions.
> > > > > > Suspect lines...
> > > > > > db2_bind_param($stmt, 1, "$vendor", DB2_PARAM_IN);
> > > > > > db2_bind_param($stmt, 2, "$model", DB2_PARAM_IN);
> > > > > >
> > > > > > Should be...
> > > > > > db2_bind_param($stmt, 1, "vendor", DB2_PARAM_IN);
> > > > > > db2_bind_param($stmt, 2, "model", DB2_PARAM_IN);
> > > > > >
> > > > > > http://www.phpbuilder.com/manual/function.db2-bind-param.php
> > > > > >
> > > > > > Glenn
> > > > > >
> > > > > > On 12/1/2010 1:47 PM, Michael Ryan wrote:
> > > > > > > I've cross posted this since it's an RPG/stored procedure
> > question.
> > > > > > > Thanks!
> > > > > > >
> > > > > > > ---------- Forwarded message ----------
> > > > > > > From: Michael Ryan<michaelrtr@xxxxxxxxx>
> > > > > > > Date: Wed, Dec 1, 2010 at 1:46 PM
> > > > > > > Subject: Execute Failed with Stored Procedure Call
> > > > > > > To: Web Enabling the AS400 / iSeries<web400@xxxxxxxxxxxx>
> > > > > > >
> > > > > > >
> > > > > > > Hi folks -
> > > > > > >
> > > > > > > Could someone help me out with this? I'm trying to call a stored
> > > > > > procedure
> > > > > > > from my PHP script. Lots of help from lots of folks, and it's very
> > > > much
> > > > > > > appreciated. I've decided to use the MODS approach. I'm getting an
> > > > > > > error
> > > > > > on
> > > > > > > the db2_execute...here's a link to the PHP code:
> > > > > > > http://code.midrange.com/09a32d9a0e.html
> > > > > > >
> > > > > > > I know the connection is being made, and I know the db2_prepare is
> > > > > > > successful. I'm passing two parameters to the procedure and
> > > > > > > expecting
> > > > > > > an
> > > > > > > array (MODS) back. Do I need to specify the array in the
> > db2_prepare
> > > > > > > statement?
> > > > > > >
> > > > > > > Here's my CREATE PROCEDURE statement that I executed from STRSQL:
> > > > > > >
> > > > > > > CREATE PROCEDURE INVENTORY/SPR101R(IN pInVnda CHAR ( 5), IN pInMdl
> > > > > > > CHAR ( 20)) LANGUAGE RPGLE NOT DETERMINISTIC READS SQL DATA CALLED
> > > > > > > ON NULL INPUT EXTERNAL NAME 'NEWSYS/SPR101R' PARAMETER STYLE
> > GENERAL
> > > > > > > Here's the salient part of my RPG code:
> > > > > > >
> > > > > > >              Exec SQL
> > > > > > >                  Set Result Sets For Return To Client
> > > > > > >                    Array :ReturnDS For :WkRowcount Rows;
> > > > > > >
> > > > > > > This works when I call the procedure from iSeries Navigator.
> > > > > > >
> > > > > > > Thanks!
> > > > > >
> > > > > > --
> > > > > > This is the RPG programming on the IBM i / System i (RPG400-L)
> > mailing
> > > > > > list
> > > > > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > > > > To subscribe, unsubscribe, or change list options,
> > > > > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > > > > Before posting, please take a moment to review the archives
> > > > > > at http://archive.midrange.com/rpg400-l.
> > > >
> > > >
> > > > --
> > > > This is the RPG programming on the IBM i / System i (RPG400-L) mailing
> > > > list
> > > > To post a message email: RPG400-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > > > or email: RPG400-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/rpg400-l.
> >
> >
> > --
> > This is the RPG programming on the IBM i / System i (RPG400-L) mailing
> > list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.