Retun lists from RPG Stored Procedure to PHP(i5)

General discussion on Zend Core for IBM System i
Post Reply
gracekim
Posts: 4
Joined: Wed Apr 28, 2010 12:15 am

Retun lists from RPG Stored Procedure to PHP(i5)

Post by gracekim » Thu Apr 29, 2010 4:29 pm

How return result set from RPG Stored procedure to PHP?
Case )
Passing Parameters - State, Start_Date, End_Date
Return data set - Order_No, Status, BOL.

This stored procedure return over 100 records depends on condition(State, Start_Date, End_Date).

I don't know how I have to program for this case.

I have source code for simple case.Input one parameter(Your Name) and one output.
include_once('db.php');
$desc = new i5_Description();
$desc->I5_TYPE_CHAR("pname",20,I5_IN);
$desc->I5_TYPE_CHAR("greet",30,I5_OUT);
$prog = new i5_program("TSHTEST","GREETING", $desc,$as400);
$name='Grace Kim';
$prog->set('pname',$name);
$prog->call();
if(is_null($prog->LastErr)){
echo "err ==> ".$prog->LastErr."<br>";
echo '<br>greet: '.$prog->__get("greet").'<br>';
echo var_dump($prog);
}
$prog->__destruct();
$as400->disconnect();
echo "<br>Closed()";

This works fine. But as I mention, I need return list with 3 fields. I can't use query because I call stored procedure.
This stored procedure developed with RPG(I don't know anything about RPG). Same RPG code works fine with ASP.

ASP Code:

Call stored procedure and pass input parameters-----------------
rsorders_cmd.CommandText = "{call QGPL.GETORLISTC(?,?,?)}"
rsorders_cmd.Prepared = true
rsorders_cmd.Parameters.Append rsorders_cmd.CreateParameter("param1", 200, 1, 255, rsorders__ORIGINUM) ' adVarChar
rsorders_cmd.Parameters.Append rsorders_cmd.CreateParameter("param2", 200, 1, 255, rsorders__BDATE8) ' adVarChar
rsorders_cmd.Parameters.Append rsorders_cmd.CreateParameter("param3", 200, 1, 255, rsorders__EDATE8) ' adVarChar

Set rsorders = rsorders_cmd.Execute

Return Result set
While ((Repeat1__numRows <> 0) AND (NOT rsorders.EOF))
%>
<tr>
<td ><%=(rsorders.Fields.Item("ORDERN").Value)%></td>
<td ><%=(rsorders.Fields.Item("STATUS").Value)%></td>
<td ><%=(rsorders.Fields.Item("BOL").Value)%></td>
rsorders.MoveNext()
Wend

Thank you.

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by chris_hird » Thu May 13, 2010 12:17 am

The samples provided should help you. You need to ensure you prepare the call to the program with the correct input and output parameters.

http://files.zend.com/help/Zend-Core-i5 ... amples.htm

The first example shows how to pass in multiple arrays which are for the input and output variables.

Chris...
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

gracekim
Posts: 4
Joined: Wed Apr 28, 2010 12:15 am

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by gracekim » Thu May 13, 2010 4:28 pm

Chris, Thank you for replay.
But none of them works for me. First sample is for sending parameter(input) and receive data thru output parameter. It works but it doesn't work for SQL Result set.

I send Query it return SQL Result set but not for stored procedure. When I use 'CALL GTI/Stored_Procedure_Name', It returns 'False' for i5_prepare.

Also I can't use NativeAccess. It is for files.

I researched for Stored_Procedure and return SQL Result set, People say bring business logic into PHP or save result set into temperay file and send query or open the temperary file with NativeAccess.

I don't want to use both of way, Is there another solution?

I need run stored procedure with input parameter and receive SQL result set like query.

Thank you.

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by chris_hird » Tue May 25, 2010 5:07 pm

Sorry this took so long, I was hoping someone would chime in about stored procedures as I am not an expert on them! I had a problem where I needed the results from an SQL statement to be converted before it was passed back to the PHP script, basically the time was stored in a format which PHP could not decipher. I ended up using User Defined Functions to take each field I was interested in and converting it to the right format as part of the SQL script. This worked for me after some messing around due to the differences between DB2 SQL and MySQL which is what I was used to. Anyhow the reason I say this is because the SQL call had to be specifically structured for it to work! The same script in DB2 SQL which was run under STRSQL from the command line would not work in the PHP script. I fear this may be what you are experiencing with stored procedures??? If not I still hope some other person can help you with your stored procedure?

Chris...
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

gracekim
Posts: 4
Joined: Wed Apr 28, 2010 12:15 am

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by gracekim » Tue May 25, 2010 6:02 pm

When I test with MySql, it worked. Also I tested with db2_connect. It worked. Only i5_connect doesn't work.
I think using db2_connect for return SQL result set for RPG stored procedure.(At least db2_connect works).

Thank you again.

Grace.

chris_hird
Posts: 171
Joined: Fri Apr 10, 2009 12:41 am
Location: Toronto
Contact:

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by chris_hird » Wed May 26, 2010 11:57 am

That makes sense, IBM provides 2 SQL translators, I am pretty sure the i5_Toolkit functions use one and the db2_ functions use another. As you already have it working it may not be an issue, but you could register with EasyCom support and ask them the question, they are pretty responsive and you will certainly get someone who knows the structure of the call and they developed the i5_toolkit.

Chris...
Shield Advanced Solutions Ltd
Home of JobQGenie and the Receiver Apply Program
http://www.shield.on.ca/Blog

gracekim
Posts: 4
Joined: Wed Apr 28, 2010 12:15 am

Re: Retun lists from RPG Stored Procedure to PHP(i5)

Post by gracekim » Wed May 26, 2010 4:23 pm

Thank you Chris.

Post Reply