Re: MAJOR information leaked out in TFM

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