Re: MAJOR information leaked out in TFM

[Glenn Hopwood <ghopwood.list@xxxxxxxxx>]

SOLVED

I sent an email to tech@xxxxxxxxxxxxxxxx about my issue and they quickly 
found and solved it (Thank you!). It turns out there's a slight error in 
their documentation. The option I5_OPTION_AUTOMATIC_NEXT_RESULT should 
be I5_OPTIONS_ATOMATIC_NEXT_RESULT (Notice the additional 's' and the 
dropped 'u').

Alternatively you can leave the option off the connection and use the 
i5_next_result() function immediately before your first i5_fetch* 
function and get the same results (you have to create an extra resource 
though).

Apparently their functions return your parms in the first result set so 
you have to use i5_next_result() to skip it (unless of course you want 
them).

Glenn

On 4/15/2010 2:26 PM, Glenn Hopwood wrote:
> Sure, here you go. Thanks for your time.
>
> Glenn
>
> Stored procedure:
> CREATE PROCEDURE GETPROCS
> (in LibName char(10))
> RESULT SETS 1
> LANGUAGE SQL
> BEGIN
>
> declare stmt varchar(1000);
>
> DECLARE c1 CURSOR with hold with return to client for S1;
>
> set stmt = 'SELECT SPECSCHEMA, SPECNAME from SYSPROCS where
> SPECSCHEMA = ''' || LibName || '''';
>
> prepare S1 from stmt;
>
> OPEN c1;
>
> DECLARE c1 CURSOR with hold with return to client for S1;
>
> set stmt = 'SELECT SPECSCHEMA, SPECNAME from SYSPROCS where
> SPECSCHEMA = ''' || LibName || '''';
>
> prepare S1 from stmt;
>
> OPEN c1;
>
> SET RESULT SETS CURSOR c1;
>
> END
>
> PHP (I've removed the user stuff and I know the procedure works because
> I get to the final dump):
> $user = "";
> $password = "";
>
> $i5_connection = i5_connect( '127.0.0.1', $user, $password,
> array(I5_OPTION_AUTOMATIC_NEXT_RESULT=>"1") );
> if( !$i5_connection ) var_dump( i5_error( ) );
>
> $storedProcedure = "call GETPROCS(?)";
>
> $result =i5_prepare($storedProcedure);
>
> if(!$result)
>
> {
>
> echo("Prepare failed");
>
> exit();
>
> }
>
> // Describe first parameter
>
> $ret = i5_paramdesc($result, I5_TYPE_CHAR, 0, 10, 0, I5_IN);
>
> $val = "QSYS";
>
> $ret = i5_setparam($result, 0, $val);
>
> if(!$ret)
>
> {
>
> echo("Set Param failed");
>
> exit();
>
> }
>
> $hdl = i5_execute($result);
>
> echo "</br></br>";
>
> $row = i5_fetch_row( $result, I5_READ_NEXT );
>
> var_dump($row);
>
> ?>
>
> On 4/15/2010 1:47 PM, ADelgado@xxxxxxxxxxxxxxxxxx wrote:
> > Would you please (re)post your PHP code and your stored procedure SQL so
> > that I can give you concrete feedback?
> >
> > You can use the tool at http://code.midrange.com/ to better preserve
> > spacing.
> > --
> > Alfredo Delgado
> > 6800 Broken Sound Pkwy, Suite 150
> > Boca Raton, Florida 33487
> >
> > -----web400-bounces@xxxxxxxxxxxx wrote: -----
> >
> > To: Web Enabling the AS400 / iSeries<web400@xxxxxxxxxxxx>
> > From: Glenn Hopwood<ghopwood.list@xxxxxxxxx>
> > Sent by: web400-bounces@xxxxxxxxxxxx
> > Date: 04/15/2010 13:21
> > Subject: Re: [WEB400] MAJOR information leaked out in TFM
> >
> > Alfred,
> >
> > Since you have first hand experience with it then I must be missing
> > something. My code is identical to what you posted below and I get
> > nothing back from the i5_fetch_assoc() function except the parm I sent
> > in. I don't get a result set. I even changed my code according to a
> > message sent by Sylvain about needing to call the i5_next_result()
> > function once and still no luck.
> >
> > Glenn
> >
> > On 4/15/2010 11:48 AM, ADelgado@xxxxxxxxxxxxxxxxxx wrote:
> > > Glenn,
> > >
> > > The solution that I provided Tom is based on first hand knowledge,
> > not
> > > simply an interpretation of the manual. The code sample that I
> > provided
> > > using i5_paramdesc( ) was based on code that Tom said was
> > correctly
> > > executing his stored procedure but wasn't returning a result. The
> > number
> > > of parameters described and the specific descriptions would be
> > unique to
> > > that particular stored procedure.
> > >
> > > Here is the code once again.
> > >
> > > $statuscode='XX';
> > > $qUpdateCustomer = "CALL NEWJCLIB/PROC_JC15_SL( ? )";
> > >
> > > $request = i5_prepare( $qUpdateCustomer );
> > > if( !$request ) var_dump( i5_error( ) );
> > > $paramDesc = i5_paramdesc( $request, I5_TYPE_CHAR, 0, 5, 0,
> > I5_INOUT
> > > ); // I guessed at 5 for the length
> > > if( !$paramDesc ) var_dump( i5_error( ) );
> > > $paramSet = i5_setparam( $request, 0, $statuscode );
> > > if( !$paramSet ) var_dump( i5_error( ) );
> > > $procExec = i5_execute( $request );
> > > if( !$procExec ) var_dump( i5_error( ) );
> > > $stuff = i5_fetch_assoc( $request );
> > > var_dump( $stuff );
> > >
> > > We established earlier in the thread that Tom was making a proper
> > working
> > > connection via i5_connect( ). With the code sample above I'm
> > assuming that
> > > Tom's stored procedure is defined with an input/output parameter
> > of data
> > > type CHAR. I guessed a length of five and commented on my guess in
> > case
> > > the actual length is two, etc.
> > >
> > > Regards,
> > > Alfred
> > >
> > > --
> > > Alfredo Delgado
> > > 6800 Broken Sound Pkwy, Suite 150
> > > Boca Raton, Florida 33487
> > >
> > --
> > This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
> > To post a message email: WEB400@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: [1]http://lists.midrange.com/mailman/listinfo/web400
> > or email: WEB400-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at [2]http://archive.midrange.com/web400.
> >
> > References
> >
> > Visible links
> > 1. http://lists.midrange.com/mailman/listinfo/web400
> > 2. http://archive.midrange.com/web400