Stored Procedures

[Wildthrust]

Not sure why this isn't working

 

	
$mysqli = new mysqli("IPADDRESS", "LOGIN", "PASSWORD", "DBNAME");

$result = $mysqli->query('CALL Proc_GetRegionInfo(10000001)');
$row = $result->fetch_assoc();
$regionName =  $row['regionName'];

$result = $mysqli->query('CALL Proc_GetFactionName(500007)');
$row = $result->fetch_assoc(); // Line 13
$factionName =  $row['factionName'];

 

The first query works fine, the second one fails and gives an error message of

Fatal error: Call to a member function fetch_assoc() on a non-object in ...\regiondetail.php on line 13

 

Any ideas?

 

[effigy]

The first doesn't actually work because you're using $result, not $regionresult. Use fetch_row.

[Wildthrust]

oops, actualy even with it being $result on the first one the same thing happens.  Will try the fetch_row though

[Wildthrust]

nope, same error message, but now it says fetch_row vs fetch_assoc

 

If I comment out the first query, the second works and vise versa

[Wildthrust]

Also, if I switch from using stored procedures to the queries themselves, it works.

 

Seems to be someting about calling stored procedures twise

[effigy]

Check the query for failure.

[Wildthrust]

Query works, Stored Proc works in the mysql query browser.

 

Also incase this might help

Windows XP Pro fully updated
Apache 2.2.5 (mysql and mysqli enabled)
MySql 5
PHP 5

[Wildthrust]

Ok, after a bunch more creative digging I found this

http://us2.php.net/manual/en/function.mysqli-store-result.php

 

If you scroll down you get this

Beware when using stored procedures:

If you connect to the database and then call dbproc A followed by a call to db proc B and then close the connection to the db, the second procedure call will not work.

 

It looks like there is a bug in MYSQL or mysqli that returns an extra recordset than you would expect. It then doesn't let you call another stored procedure until you finish processing all the recordsets from the first stored procedure call.

 

The solution is to simply loop through the additional recordsets between calls to db procs. Here is a function that I call between db proc calls:

 

<?php

#--------------------------------

function ClearRecordsets($p_Result){

#--------------------------------

    $p_Result->free();   

    while($this->Mysqli->next_result()){

      if($l_result = $this->Mysqli->store_result()){

              $l_result->free();

      }

    }

}

 

?>

 

When I tried to use the function as is, I got an error about the $this, so I changed the function to

 

	#--------------------------------
function ClearRecordsets($p_Result, $p_mysqli){
#--------------------------------
	$p_Result->free();    
	while($p_mysqli->next_result()){
	  if($l_result = $p_mysqli->store_result()){
			  $l_result->free();
	  }
	}
}

 

Then changed my main code to this

	$mysqli = new mysqli("ip", "login", "pw", "db");

$result = $mysqli->query('CALL Proc_GetRegionInfo(10000001)');
$row = $result->fetch_assoc();
$regionName =  $row['regionName'];
ClearRecordsets($result, $mysqli);

$result = $mysqli->query('CALL Proc_GetFactionName(500007)');
$mysqli->store_result(); 
$row = $result->fetch_assoc();
$factionName =  $row['factionName'];
ClearRecordsets($result, $mysqli);

 

And now it works fine, just have to call that function after each stored proc call

[Wildthrust]

remove the $mysqli->store_result(); part from above