2

I have problem with a stored procedure where i use a cursor, so i made a test code to spot the problem but its still a mystery for me, here is the test codes:

Stored proc. body named 'Test1':

SELECT 'ok1';

Stored proc. body named 'Test2':

DECLARE testCursor CURSOR FOR SELECT 'stuff';
OPEN testCursor;
CLOSE testCursor;
SELECT 'ok2';

Php test code:

$mysql_obj = new mysqli($sqlserver, $sqluser, $sqlpass, $sqldatabase);

$statement = $mysql_obj->prepare('CALL Test1()');
$statement->execute();

$result = $statement->get_result();
if($result === false) die('bad result');
if($result->num_rows === 0) die('row expected');

$row = $result->fetch_row();
if($row === NULL) die('fetch_row error');

echo 'Got: ' . $row[0];

Run results the message "Got: ok1" as expected, but if i change the call to Test2 the message will be "row expected".

The really confusing in this problem that in phpmyadmin CALL Test1(); and the CALL Test2(); command both results one row with the "ok1"/"ok2" value (tested in dbForge Studio Express, it's good too).

What am I missing?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
abe
  • 33
  • 3
  • According to the PHP docs for `mysqli->prepare`, "you should not add a terminating semicolon or \g to the statement". – Evan Mulawski Mar 03 '12 at 20:39
  • True, but it doesn't affect the problem, I just tried and didn't help. – abe Mar 03 '12 at 21:13
  • did you try to just not close the cursor? In oracle you would just do "open testCursor" and leave the stored procdure – Sebas Jan 31 '13 at 18:56

0 Answers0