3 Replies - 2113 Views - Last Post: 18 November 2011 - 05:26 AM Rate Topic: -----

#1 armon   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 24-September 09

How to retrieve status result from CALLing MySQL procedure?

Posted 18 November 2011 - 02:35 AM

Hello,

According to the MySQL manual

Quote

Multiple-result processing also is required if you execute CALL statements for stored procedures. Results from a stored procedure have these characteristics:

Statements within the procedure may produce result sets (for example, if it executes SELECT statements). These result sets are returned in the order that they are produced as the procedure executes.

In general, the caller cannot know how many result sets a procedure will return. Procedure execution may depend on loops or conditional statements that cause the execution path to differ from one call to the next. Therefore, you must be prepared to retrieve multiple results.

The final result from the procedure is a status result that includes no result set. The status indicates whether the procedure succeeded or an error occurred.


How to rertrive that status result??

Because of that status result even if procedure has only one SELECT statement inside I HAVE TO use mysqli_next_result

Example:
$query = "CALL myProcedure(); "; //Inside procedure there is typical SELECT * FROM tab;
            
$result = mysqli_query($dbc_h, $query) or die(mysqli_error($dbc_h));
mysqli_next_result($dbc_h); //without that line we will have error: Commands out of sync; you can't run this command now I guess it's because of that status result or am I wrong?
$result = mysqli_query($dbc_h, $query) or die(mysqli_error($dbc_h));


Is that possible to retrieve status result at all? I cannot find info about it.

Best regards,

Armon

Is This A Good Question/Topic? 0
  • +

Replies To: How to retrieve status result from CALLing MySQL procedure?

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: How to retrieve status result from CALLing MySQL procedure?

Posted 18 November 2011 - 04:11 AM

View Postarmon, on 18 November 2011 - 09:35 AM, said:

How to rertrive that status result??

The status result is just an indication of whether the call failed or not. So you can infer this status result from the return value of the query methods. They differ slightly though, so you will have to know their return values to do this.

  • The mysqli_query function is meant to execute a single query, and return the result set for that single query. So to figure out the status result, just check if the return value is false. If it is, the call failed. Otherwise the function will return the first result set.

  • The other query methods, mysqli_multi_query and mysqli_real_query do not return the result set, but rather a Boolean indicating the success of the query. This Boolean can be read as the status result the MySQL manual is talking about.


Regardless of which you use, all the result sets the call produces will be waiting for you to read them with the mysqli_store_result or mysql_use_result functions. (Which you should do to prevent the "out of sync" error.) - One important thing to note is that the mysqli_query function automatically retrieves the first set, which the other two do not, so yo will have to adjust your code accordingly. - Meaning: if you use mysqli_query, use or store the next result set after using the first; but with the other two you need to use or store the set first.

As an example, this is how you would properly read the result sets from a procedure call into HTML tables:
<?php

$dbLink = new mysqli("localhost", "usr", "pwd", "db");
if (mysqli_connect_errno())
	die("Failed to connect: " . mysqli_connect_error());

$sql = "CALL myProcedure()";
if ($dbLink->real_query($sql))
{
	while($dbLink->more_results())
	{
		$dbLink->next_result();
		if ($result = $dbLink->use_result())
		{
			echo "<table border=1>";
			while ($row = $result->fetch_row())
			{
				echo "<tr>";
				foreach ($row as $field)
				{
					echo "<td>{$field}</td>";
				}
				echo "</tr>";
			}
			echo "</table><br>";
			$result->free();
		}
	}
}
else
	echo "<h3>Failed to call the procedure.</h3>
			<pre>" . $dbLink->error ."</pre>";

$dbLink->close();
?>


Note that I use mysqli_real_query, and read it's result value as the "status result" the MySQL manual mentions. Then I traverse each set.

Even if you are not expecting any result sets from a procedure, you should still loop through them like this, calling mysqli_next_result in a loop while the mysqli_more_results call returns true. (You don't have to actually use the set, just loop through it like that.)
Was This Post Helpful? 1
  • +
  • -

#3 armon   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 24-September 09

Re: How to retrieve status result from CALLing MySQL procedure?

Posted 18 November 2011 - 05:06 AM

Thank you very much for making it clear :)

I have only one more question regarding this:

View PostAtli, on 18 November 2011 - 05:11 AM, said:

Even if you are not expecting any result sets from a procedure, you should still loop through them like this, calling mysqli_next_result in a loop while the mysqli_more_results call returns true. (You don't have to actually use the set, just loop through it like that.)


What is the purpose of that?

The only thing that comes to mind while I'm looking at this is that the programmer doesn't know how many SETs the procedure can return and because of that we have to always assume that there might be more than one SET?

Or maybe there is something going "behind the scene" i don't know about?

Best regards and thank you for the answer.
Was This Post Helpful? 0
  • +
  • -

#4 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: How to retrieve status result from CALLing MySQL procedure?

Posted 18 November 2011 - 05:26 AM

Yea, that's exactly right. Procedures can return a varied amount of sets based on what's going on in the procedure itself and the database server, and we have to account for all that in our code.

Better safe than sorry, right? :)


I had a function at some point that did all this for me; that read the data sets into an array and returned that. May be a good idea if you do this kind of thing a lot.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1