Call stored procedure in custom SQL - Commands out of sync;

[SilverCorvus]

I have a problem with custom SQL queries in actions. Its cause is the call of a stored procedure and the (supposedly) old mysql interface.
Calling and using the data of a stored procedure works fine, but when I want to make another query afterwards (regardless if stored procedure or normal select) it doesn't work and shows the error message "Commands out of sync; you can't run this command now"

Code: Select all

class actions_test {
    function handle(&$params){
      $app =& Dataface_Application::getInstance();  // reference to Dataface_Application object

      $res = mysql_query("call storedProcedure(Null, Null, Null, Null, Null, Null)", $app->db());
      if (!$res ) trigger_error(mysql_error($app->db()));

      $data = array();
      while($data[] = mysql_fetch_assoc($res)) {  }

      ... //Output the data

      $res = mysql_query("call storedProcedure(Null, Null, Null, Null, Null, Null)", $app->db());
      if (!$res ) trigger_error(mysql_error($app->db()));

      $data = array();
      while($data[] = mysql_fetch_assoc($res)) { }
   }
}


That's because the stored procedure return in fact two result sets, but according to http://us3.php.net/manual/en/function.mysql-query.php
"mysql_query() sends a unique query (multiple queries are not supported) ..."
And you have to receive/free all the data, before requesting new via a query. So that mess it up.
mysql_free_result($res) doesn't work, it does only fetch/clear all the rows of the given result, but not multiple results.

One suggested solution is to switch to mysqli (i for improved ) which can handle also multiple results. But the connections builds up Xataface and I neither want to change it in its core nor connect it manually, so I would have to implement the checks ect. again.

According to http://dev.mysql.com/doc/refman/5.0/en/ ... ption.html you can enable multiple-statement support with mysql_set_server_option() and then use mysql_next_result() to get the next result. That would be the solution, but I get the fatal error "Call to undefined function mysql_set_server_option()"

What went wrong? Why isn't there this function? Can I install it somehow? How can I solve this problem otherwise?

I work with
* Apache/2.2.17 (Win32) mod_ssl/2.2.17 OpenSSL/0.9.8o PHP/5.3.4 mod_perl/2.0.4 Perl/v5.10.1
* MySQL-Client-Version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
* PHP Extension: mysql

[shannah]

The manual page you reference here is for the mysql C api which may not be the same as the PHP api. I can't find any mention of mysql_set_server_option() on the php.net documentation site.

As far as solving the problem, I haven't done much with stored procedures, but one suggestion might be to free your result before making a new query.

@mysql_free_result($res);

This is good practice anyways after you're finished with any result set to avoid memory leaks in your code.

e.g.

Code: Select all

$res = mysql_query("select etc...", df_db());
if ( !$res ) throw new Exception(mysql_error(df_db()));
while ($row = mysql_fetch_assoc($res)){
    // do something
}

// Now we're done
@mysql_free_result($res);

// Now do another query...




[SilverCorvus]

The manual page you reference here is for the mysql C api which may not be the same as the PHP api. I can't find any mention of mysql_set_server_option() on the php.net documentation site.

Oh, that's why it don't work - I mixed it up.

As far as solving the problem, I haven't done much with stored procedures, but one suggestion might be to free your result before making a new query.

@mysql_free_result($res);

This is good practice anyways after you're finished with any result set to avoid memory leaks in your code.

Well, it doesn't work, like I wrote above ...
But I will free my results from now on.

That seems to be the answer:

From http://forge.mysql.com/wiki/Which_PHP_D ... ould_I_use
Please stop using the first MySQL driver for PHP ever published: ext/mysql. Since the introduction of the MySQL Improved Extension - mysqli - in 2004 with PHP 5 there is no reason to still use the oldest driver around. ext/mysql does not support Charsets, Prepared Statements and Stored Procedures. It is limited to the feature set of MySQL 4.0. Note that the Extended Support for MySQL 4.0 ends at 2008-12-31. Don't limit yourself to the feature set of such old software! Upgrade to mysqli, see also Converting_to_MySQLi. mysql is in maintenance only mode from our point of view.

The MySQL driver for PHP supports only MySQL 4 features, but stored procedures are a MySQL 5 feature. It simply doesn't work with this driver ...
How about switching to MySQLi? Sounds like a good idea, but I don't know how much work it would be for Xataface ...

Is there a way to close the DB connection and reconnect via Xataface?

PS: Is there a way to change the layout (the font type) of the quotes? It isn't very readable.

[shannah]

Mysqli is not high on the priority list right now. The lecture from the mysqlforge page that you posted tends to paint hyperbolically bleak picture of the situation with the standard mysql extension. While it may be true that you cannot access some stored procedures with it, the statement that it doesn't support charsets is a little misleading, as you can indeed set the connection charset using direct mysql commands through the legacy driver.

In response to this post, I decided to, once again, look into the landscape to decide how important it is to modify Xataface to use mysqli, and how difficult it would be. I found the following:

1. Many of my servers that I am currently using (some I manage some I do not) don't have mysqli installed. So it would require recompiling PHP with mysqli (i think) to get it working - or requesting it from my host. This leads me to believe that there are probably still many servers out there that still need the old driver in order to work (i.e. I couldn't just change it to use mysqli for a future version - I'd have to introduce it as an optional setting).

2. While it wouldn't be that hard to modify all of the internal Xataface code to use mysqli instead of mysql, this would break all existing applications built on Xataface since they could be using the df_db() function to get the mysql connection. --> Just like #1 I would have to introduce it as an optional setting

3. The easiest solution at this time to be able to use features that require mysqli would be to make a second connection using the mysqli connector as needed and just use that connection for those queries that need it. You can access the connection info from Dataface_Application->getInstance()->_conf['_database'].


-Steve

[SilverCorvus]

1. Many of my servers that I am currently using (some I manage some I do not) don't have mysqli installed. So it would require recompiling PHP with mysqli (i think) to get it working - or requesting it from my host. This leads me to believe that there are probably still many servers out there that still need the old driver in order to work (i.e. I couldn't just change it to use mysqli for a future version - I'd have to introduce it as an optional setting).

MySQLi came with PHP5 in 2004, PHP4 is since 2008 finally out-dated (no security fixes anymore). So it looks illogical that some provider offers only PHP4 and the old MySQL API ... But it is how it is, right?

If you cannot discard the MySQL API, but you would have to implement it in two ways, I can understand the low priority. Also it works fine the way it is - except for stored procedures. ^^
I will use a second connection like suggested. Thanks.