Re: Calling a stored procedure from PHP, GLENN, GRIZZLY, anyone ......

I whipped up an example that works... It isn't 'ready for primetime' and has some hard coded stuff in it but it should work for you. (I'm @ v5r4)

Here's the procedure. (Thanks to Charles Wilt from the midrange list)
CREATE PROCEDURE GETPROCS
(in LibName varchar(128))
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;

SET RESULT SETS CURSOR c1;

END

Here's my PHP code. Note that the db2_prepare() function has 2 parms specified unlike your version. Also, I think the *param* functions start at 1 not 0.

As I said, I wouldn't put this in production but it should work. Insert your user and password in the 'connect' portion. If the job desc of the specified user isn't set up correctly you may need to hardcode the library on the 'call' sql statement. (Because of the DB2_I5_NAMING_ON option you should use the '/' notation if you need to do that.)

<table>
<tr>
<td>Schema&nbsp;</td>
<td>Proc&nbsp;</td>
</tr>

<?php

// Connect to the DB..
$conn = "*LOCAL";
$name = "";
$pwd = "";
$options = array('i5_naming'=>DB2_I5_NAMING_ON);

$i5link = db2_connect($conn, $name, $pwd, $options);
if (!$i5link) echo 'Connection failed: '.db2_stmt_error().' : '.db2_stmt_errormsg();

$sql_stmt = 'call GETPROCS(?)';
$LibName = 'QSYS';

$stmt = db2_prepare($i5link, $sql_stmt)
or die("Failed prepare: ".db2_stmt_error()." : ".db2_stmt_errormsg());

db2_bind_param($stmt, 1, "LibName", DB2_PARAM_IN);

$result = db2_execute($stmt)
or die("Failed query: ".db2_stmt_error()." : ".db2_stmt_errormsg());

while($row=db2_fetch_assoc($stmt)){
$SCHEMA = $row['SPECSCHEMA'];
$PROC = $row['SPECNAME'];

echo("<tr><td nowrap>$SCHEMA&nbsp;</td>
<td nowrap>$PROC&nbsp;</td></tr>");
}

?>
<table/>

Let me know if it works.

Glenn

On 4/13/2010 8:34 AM, Tom Deskevich wrote:

OK, can YOU give me an example of one that works?

The example I am following from a previous post does not include this in
the db2_prepare:

Does something like this not work for you?

e.g.

$qUpdateCustomer = "CALL SP_INSERT_F550101Z( ?, ?, ? )";
$request = i5_prepare( $qUpdateCustomer );<=======
i5_setparam( $request, 0, $transactionId );
i5_setparam( $request, 1, 'C' );
i5_setparam( $request, 2, $_POST['ABALPH'] );
i5_execute( $request );

Or. My friend Grizzly's example with no prepare in it..

$company = 01;
$name = 'Grizzly';
$sql = "call library.procedure($company,'$name')";
$result = db2_exec($sql);

If I had a boss breathing down my back, I would be in a straight jacket
by now. Can it be that hard to call a stored procedure with PHP?
I told my wife last night that either I am really stupid, or I am just
average and all you guys out there are geniuses.

But an example that takes me through the whole process would be nice,
even to show the result set in a table. I know, I am asking a lot. But
think of all the other average or below average skill programmers you
may help!
You can make the world a better place!


Tom Deskevich