Re: Fwd: Execute Failed with Stored Procedure Call

[Michael Ryan <michaelrtr@xxxxxxxxx>]

I have an email into the system administrator regarding changing the QCCSID
system value.

I guess I need to convert to/from ASCII and EBCDIC in the meantime. Anyone
have a good iconv (or something) prototype/conversion routine they could
share? Thanks...

On Thu, Dec 2, 2010 at 12:38 PM, Mark Murphy/STAR BASE Consulting Inc. <
mmurphy@xxxxxxxxxxxxxxx> wrote:

> You are correct, no translation will happen with that CCSID.  Best bet if
> you are going to be doing web stuff from this machine is to set the CCSID
> properly.  I don't think there is much impact if you are using a single
> language.
>
> Mark Murphy
> STAR BASE Consulting, Inc.
> mmurphy@xxxxxxxxxxxxxxx
>
>
>
> From:   Michael Ryan <michaelrtr@xxxxxxxxx>
> To:     "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>,
> "Web Enabling the AS400 / iSeries" <web400@xxxxxxxxxxxx>
> Date:   12/02/2010 12:26 PM
> Subject:        Re: Fwd: Execute Failed with Stored Procedure Call
> Sent by:        rpg400-l-bounces@xxxxxxxxxxxx
>
>
>
> 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.
> --
>
> 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.