note 82429 added to function.mysql-query

For all you programmers out there getting the 'Command out of synch' errors when executing a stored procedure call: There are known bugs related to this issue, and the best workaround for avoiding this error seems to be switching to mysqli. Still, I needed mysql to also handle these calls correctly. The error is normally related to wrong function call sequences, though the bug report at http://bugs.php.net/bug.php?id=39727 shows otherwise. For me, after commenting out hundreds of lines and several introspection calls to parse the procedure information (using information_schema and 'SHOW' extensions), I still got the same error. The first result is returned, because I initiated my connection using the MYSQL_MULTI_RESULTS value of 131072 (forget this and you will never get any output, but an error message stating mysql cannot return results in this context) After testing with this code (sproc2 simply calls 'SELECT * FROM sometable'), I found the error must be in the mysql library/extension. Somehow, mysql does not handle multiple resultsets correctly, or is at least missing some functionality related to handling multiple results. <?php //... $rs = mysql_query('CALL sproc2(500)'); while (($row=mysql_fetch_assoc($rs))!==false) { print_r($row); } mysql_free_result($rs); $rs = mysql_query('CALL sproc2(500)'); print mysql_error(); //the notorious 'command out of synch' message :( while (($row=mysql_fetch_assoc($rs))!==false) { print_r($row); } mysql_free_result($rs); ?> After spending hours debugging my code (the full library is already over the MB), the only solution seemed to be to CLOSE the connection after the first call, and reopening it before the second. So if you ever make a uniform database accessing interface and implement stored procedures/prepared statements (or classes for it), this could be a solution if you really wish to enable stored procedures. Still, be aware that this is really a serious flaw in your design (and IMHO, the mysql extension) Also see the documentation for mysqli on mysqli_query, which seems to be working fine. ---- Server IP: 83.137.20.135 Probable Submitter: 62.45.2.60 ---- Manual Page -- http://www.php.net/manual/en/function.mysql-query.php Edit -- https://master.php.net/note/edit/82429 Del: integrated -- https://master.php.net/note/delete/82429/integrated Del: useless -- https://master.php.net/note/delete/82429/useless Del: bad code -- https://master.php.net/note/delete/82429/bad+code Del: spam -- https://master.php.net/note/delete/82429/spam Del: non-english -- https://master.php.net/note/delete/82429/non-english Del: in docs -- https://master.php.net/note/delete/82429/in+docs Del: other reasons-- https://master.php.net/note/delete/82429 Reject -- https://master.php.net/note/reject/82429 Search -- https://master.php.net/manage/user-notes.php