i'll try to keep this as simple as possible, but it might be long :

basically, i don't understand why i needed to call statement::free_result followed by mysqli::next_result if i call a sp, so that my next sql won't be a problem.

Example :

Test case goes like this :

  1. calling a testcall1(par1, par2) stored procedure which would return sum of a field.

  2. First call will be "stage 1", followed by another similar call "stage 2".

  3. 1 and 2 are called by prepared statements, whereas this stage, i'll call $mysqli->query directly.

	$server = "127.0.0.1";
		$user = "usernamehere";
		$pass = "passwordhere";
		$db = "databasename";

	$mysqli= new mysqli($server, $user, $pass, $db);
	$stmt = null;

		$x = 10;
		$y = 1;

		$query= "call testcall1(?,?)";

		$stmt = $mysqli->prepare($query);

		$stmt->bind_param("ii", $x, $y);
		$stmt->execute();

		mysqli_stmt_bind_result($stmt,$field1);

		if($stmt->fetch()){
			echo "<br>(Stage 1) it is actually.. " . $field1;
		}


		// uncomment this for stage 2 to work
//						$stmt->free_result();
//						$mysqli->next_result();


		$stmt->execute();
		mysqli_stmt_bind_result($stmt,$field1);

		if($stmt->fetch()){
			echo "<br>(Stage 2) it is actually.. " . $field1;
		}

		//comment these to get out of sync error
//			$stmt->free_result();
//			$mysqli->next_result();


		$query= "SELECT field1 FROM emp WHERE id = 1";

		$set= $mysqli->query( $query ) or die("failed : ".$mysqli->error." $query");
		while ($row= $set->fetch_row())
		{
			echo $row[0];
		}

output :
1. (Stage 1) it is actually.. {number here}
2. stage 2 not called, unless you uncomment as stated.
3. at here, i get Commands out of sync; you can't run this command now

    I cannot reproduce this with normal SQL statements, so my guess is that this is a quirk with stored procedues.

      laserlight wrote:

      I cannot reproduce this with normal SQL statements, so my guess is that this is a quirk with stored procedures.

      hmm...you're right. But on 3rd stage, (last one) it'll still throw Commands out of sync error if you don't use $stmt->free_result(); Which should be normal for non-stored procedure but multiple sql calls i guess.

      Is it just me..or is it really MYSQLI for stored procedure is rarely used by php community? It doesn't have good documentation so far.

      Such as my case, $mysqli->next_result is meant to move to the Next SQL's result...which I clearly didn't have in my strings! But yet for some reason, it will solve the problem magically.

        Write a Reply...