www.hkvforums.com

Hello,

I have what appears to be a strange issue in PHPMaker 10.

I am have two stored procedures in MySQL, each of which return a specific set of records, each accepting one integer parameters. Both stored procedures work fine when I run them in a MySQL client outside of PHPMaker. Let's call them storedproc1 and storedproc2. Each stored proc runs a different SELECT statement which uses the corresponding parameter.

Now, in PHPMaker, I have a PHP function defined in the global code server events that needs to get the results of both of these stored procs. I call them up in succession in the following manner:

$rs = ew_Execute("CALL storedproc1 (" . $param . ")");
$rs->MoveFirst();
while (!$rs->EOF) {
//put data into first array structure
}
$rs->Close();

$rs = ew_Execute("CALL storedproc2 (" . $param . ")");
$rs->MoveFirst();
while (!$rs->EOF) {
//put data into second array structure
}
$rs->Close();

The issue is this: the first query runs well and I get data populated into the first array. The second one does not execute and the result set variable $rs gets a boolean value of FALSE, so a fatal error occurs when it tries to execute the MoveFirst() function. Now, when I comment out the call to the first stored procedure, storedproc1, in the PHP function, the call to the second stored procedure then works fine and returning data. Is there something else I need to do between query executions to get this to work? I tried using Conn()->Execute instead of ew_Execute, but the same result occurs. I also tried using both mysqli extension and not, and, again, the same result.

Any help would be appreciated.

Thank you in advance.

Robert Perez

For the second stored procedure, try not to use the same $rs variable. Use $rs2 (for example) instead.

I tried that, and I get the same result. I even put the separate queries to the stored functions in its own independent PHP function, and that did not work either. It seems that something within the ew_Execute function is preventing it from executing a query a second time. One thing I noticed is that it is using a global Connection object, but if the connection is still open (ie. persistent connection), then it should be able to reuse it. If not, it should just reconnect. Has anybody tried to run two SELECT statements separately in succession like I am doing here?

One other thing: I also tried using ew_LoadRecordset, but to no avail.

OK. I converted the part of the code that queries the second stored procedures to one that uses a straight PHP mysqli connection object, and this time it works fine. So, it seems that something within the PHPMaker querying mechanism does not seem to be working for me. Again, this mechanism works fine if I use it once, but if I use it a second time, the result set variable gets a boolean value of false. I would prefer to use the PHPMaker objects if at all possible. Any ideas?

How about this?

global $conn;

$rs =$conn->Execute("CALL storedproc1 (" . $param . ")");
$rs->MoveFirst();
while (!$rs->EOF) {
//put data into first array structure
}
$rs->Close();

$conn->Close();

if (!isset($conn)) $conn = ew_Connect();

$rs2 = $conn->Execute("CALL storedproc2 (" . $param . ")");
$rs2->MoveFirst();
while (!$rs2->EOF) {
//put data into second array structure
}
$rs2->Close();