When ever I try to call store procedure in mysql that sends back a result set, it keeps saying me that "can't return a result set in the given context".
I've google it and some said it's mysql bug, some said you should change your mysqli driver and ....
Situation :
Using mysqli driver Client API library version 5.0.51a , PHP Version 5.2.4-2ubuntu5.6, Using Zend 1.9 RC 1 Mysqli adapter.
What should I do!?
The answer is to upgrade your php, I've just upgraded mine to 5.3.0, and it's works likes Candy!
Not sure this is the solution to your problem, but what about trying with a more recent version of PHP ?
PHP 5.2.4 is definitly quite old -- so, if it's a bug in PHP's mysqli driver, it might have been corrected since...
Actually, after a quick search, it seems a problem like the one you are witnessing has been introduced between PHP 5.2.3 and PHP 5.2.4 (and was still here in PHP 5.2.5).
See bug #42548 : PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
Are you able to test with something like PHP 5.2.9 or 5.2.10 ?
I know these are not provided by Ubuntu, even in the last Ubuntu stable version :-( You might have to compile from sources :-(
Yet another idea would be to try mith PDO_MySql adapter : maybe it would work with that one ?
It might be possible to change Adapter without causing too much trouble / without taking hours to test ?
As you are working with Zend Framework 1.9, here's another post that might interest you, and might be easier to test : stored procedure error after upgrade to 1.8
An easy solution to try that would be to go back to Zend Framework 1.7 ; would it be possible for you, just to test ?
Anyway... Good luck !
And, if you find the solution, don't forget to indicate what the problem was, and how you solved it ;-)
I had this problem recently on a contract. The client was using a codebase on windoze and php 5.2.6 and my installation was linux and php 5.3.1 Whatever we did, they wouldn't co-operate so in the end they gave me a windoze vista machine and we installed php 5.2.6 and off we went. Moral of the story: version matching counts. Weird cus I never had this ever before in any other job. But hey, you can't know everything. Very definitely not a MySql issue, just PHP.
It works perfectly with PHP 5.2.10 as well.
From an earlier version, I've successfully used mysqli::multi_query to call a problematic procedure and get the right results.
I know this question is ancient, but for those still working with 5.2.4 and getting this error, you may consider creating a new mysql PDO object to work around this problem.
I still use 5.2.4 on my dev server to ensure backward compatibility for the WordPress plugins I develop.
Below is a wrapper around procedural calls that I use to successfully call procedures in both 5.2.4 (run on my dev server) , which would normally give me the error, and my production server (which runs a newer version that doesn't give the error) .
Its WordPress specific, but it wouldn't be difficult to modify it using straight php.
/*
* Need to cache connection so we don't keep creating connections till we hit max.
*/
private $_dbhCache=null;
/**
* mySQL Call Proc
*
* Provides a wrapper around calling a mySQL stored procedure to ensure against a 5.2.4 bug that
* causes procedure calls to fail.
* Error:'can't return a result set in the given context'
*
* references:
* http://stackoverflow.com/questions/1200193/cant-return-a-result-set-in-the-given-context
* http://php.net/pdo_mysql#69592 //i got empty result set but pointed me in the right direction
* http://php.net/pdo_mysql#80306 //this worked, but returned 0-indexed and assoc, i edited it so it only returns assoc mimicking $wpdb->get_results(
* http://www.php.net/manual/en/pdo.connections.php
* http://www.php.net/manual/en/pdostatement.fetch.php explains about FETCH_ASSOC
*
* @param string $proc The mySQL stored procedure string, including paramaters, but without the call statement. e.g.: "my_procedure_name('my_paramater')";
* @return string The results of the procedure call
*/
public function mySQLCallProc( $proc ) {
global $wpdb;
$query = "call $proc";
try {
/*
* Attempt to call the procedure normally.
*
*/
$query_result = $wpdb->get_results( $query, ARRAY_A );
/*
* Check for a database error
* and throw an exception if one is found.
* We can then attempt it again using a workaround.
*/
if ( $wpdb->last_error !== '' ) {
throw new Exception( 'Database Error While Calling Procedure' );
}
} catch ( Exception $e ) {
try {
/*
* Create a PDO Object for the connection
*/
if ( is_null($this->_dbhCache)) {
$dbh = new PDO( 'mysql:host=' . DB_HOST . ';port=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASSWORD, array( PDO::ATTR_PERSISTENT => true ) );
$this->_dbhCache=$dbh ;
}else{
$dbh = $this->_dbhCache;
}
/*
* Prepare and call the procedure.
*/
$stmt = $dbh->prepare( "call $proc" );
$stmt->execute();
/*
* fetch all rows into an associative array.
*/
$query_result = $stmt->fetchAll( PDO::FETCH_ASSOC ); //FETCH_ASSOC gets results as an assoc array. without it, you'll receive both assoc and 0-indexed array
} catch ( PDOException $e ) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
}
return ($query_result);
}
