Patch needed to execute MySQL stored procedures [#48195]

Since a stored procedure doesn't know ahead of time how many results will be returned, the client needs to support reading any multiple of results. Therefore, if the server sees that you don't have CLIENT_MULTI_RESULTS set, it will give an error saying that it can't return a result set in the given context. To fix this the following change must be made.

Line 52 needs to be changed from:
$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
to:
$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 131074);

This changes the flag at the end from CLIENT_FOUND_ROWS to (CLIENT_FOUND_ROWS | CLIENT_MULTI_RESULTS).

Comments

Comment #1

jbratton CreditAttribution: jbratton commented 8 February 2006 at 20:34

Status:

Needs review

» Postponed

Alright, so it looks like there is much more to adding support for Stored Procedures then just the fix above.

To use stored procedures you really should switch over to database.mysqli.inc since it is made for the newer versions of mysql that support stored procedures, but just switching to database.mysqli.inc does not fix the problem.

To use stored procedures with mysqli you must use the mysqli_multi_query function for the query and then use the mysqli_use_results and mysqli_next_results functions or else any other query following the stored procedure call will fail with a mysql error number 2013.

As of right now there are no provisions in the database.mysqli.inc file for using mysqli_multi_query, mysqli_use_results, and mysqli_next_results so to properly use stored procedures in drupal you must use the $active_db global and the built-in php mysqli functions directly. As MySQL 5.0 becomes more widely used this is something that will definitely need to be fixed.

Comment #2

suityou01 CreditAttribution: suityou01 commented 8 February 2008 at 20:32

Version:

4.7.0-beta4

» 5.7

I changed the connection function in the mysql include file as per the original post. Now it calls my stored procedure, but gives me a white screen of death!

Here's a blast

"Warning: Commands out of sync; you can't run this command now query: SELECT * FROM system WHERE type = 'theme' in /home/charlie/drupal/includes/database.mysql.inc on line 173

Warning: Commands out of sync; you can't run this command now query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'Commands out of sync; you can't run this command now\nquery: SELECT sid FROM sessions WHERE sid = '6d3ff1eb6eacae3defe1b9a42253a527' in /home/charlie/drupal/includes/database.mysql.inc on line 173.', 2, '', 'http://localhost/drupal/?q=datarecoverycentre/apply', 'http://localhost/drupal/?q=datarecoverycentre/apply', '127.0.0.1', 1202502558) in /home/charlie/drupal/includes/database.mysql.inc on line 173"

Anyone got any ideas? What is the defacto standard for calling stored procedures from drupal?

Thanks

Comment #3

lilou CreditAttribution: lilou commented 30 August 2008 at 18:59

Status:

Postponed

» Active

Status change.

Comment #4

ElNino CreditAttribution: ElNino commented 4 January 2010 at 19:14

I'm new to Drupal, but I somehow managed to get my Stored Procedure to work by calling SP directly (see below)

$query = "CALL vfweb_valid_user($myusername_re, $myuserpassword_re, NULL)";
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect to data engine failed: %s\n", mysqli_connect_error());
exit();
}
/* execute multi query */
$returned_output = 0;
if (mysqli_multi_query($link, $query))
{
do
{
/* store first result set */
if ($result = mysqli_store_result($link))
{
while ($row = mysqli_fetch_row($result))
{
//printf("%s\n", $row[0]);
$returned_output = intval($row[0]);//'is_valid'
$_SESSION['user_id_re'] = $myusername;
$_SESSION['pass_word_re'] = $myuserpassword;