[SOLVED] Calling mysqli->prepare fails on second call :-/

[BB]

Hi folks,

 

I'm going slightly crazy here.

 

$db_conn = mysqli( host, user, password, db ); // This connects fine

....

function addCompanyContact($db_conn, $ent_type, $ent_id, $cont_info, $cont_type, $cont_notes="")
{
   $ic = "call sp_add_company_contact(?, ?, ?, ?, ?)";

   if ($stmt = $db_conn->prepare($ic) )
   {
      $stmt->bind_param( "issss", $ent_id, $ent_type, $cont_info, $cont_type, $cont_notes );
      $stmt->execute();
      $stmt->store_result();
      $stmt->bind_result($coId);
      $stmt->fetch();
      $stmt->close();
      
      return $coId;
   }
   else
   {
      echo "Unable to prepare statement from '".$ic."'";
      return 0;
   }
}

addCompanyContact($db_conn, 'company', 1, "something", "something", "blah blah"); // Works fine
addCompanyContact($db_conn, 'company', 1, "something", "something", "blah blah"); // The stmt = $db_conn->prepare in the function fails... as do any further calls to addCompanyContact

 

If I add a new db connection statement in the function (e.g. $my_conn = mysqli( host, user, password, db ); ) then it works everytime. So, I guess I'm not closing/resetting something in the db connection correctly.

 

Does anyone know what I'm doing wrong? If you do, please tell me!

 

Many thanks!

[premiso]

Well the problem is not in that section. Post more code for us to help you bud.

[Mchl]

Echo $db_conn->error when it fails.

[BB]

Thanks for the replies guys.

 

I've reduced it down to this code:

 

<?php
require_once($_SERVER['DOCUMENT_ROOT']."/../includes/config.inc.php");
require_once(MYSQL);

//
//  Which includes this
//
// $db_conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ( 'There was a problem connecting to the database: ' . mysqli_connect_error() );
//

function addCompanyContact($db_conn, $ent_type, $ent_id, $cont_info, $cont_type, $cont_notes="")
{
$ic = "call sp_add_company_contact(?, ?, ?, ?, ?)";

//$my_conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ( 'There was a problem connecting to the database: ' . mysqli_connect_error() );

   if ($stmt = $db_conn->prepare($ic) )
   {
      $stmt->bind_param( "issss", $ent_id, $ent_type, $cont_info, $cont_type, $cont_notes );
      $stmt->execute();
      $stmt->store_result();
    	$stmt->bind_result($coId);
    	$stmt->fetch();
      $stmt->close();
      
      return $coId;
   }
   else
   {
      echo "Unable to prepare statement from '".$ic."'\n";
      echo $db_conn->error;
      return 0;
   }
}

addCompanyContact($db_conn, 'company', 1, '123456789', 'telephone', '' );
addCompanyContact($db_conn, 'company', 1, 987654321, 'fax', '' );

?>

 

The output I get from the error is:

 

"Unable to prepare statement from 'call sp_add_company_contact(?, ?, ?, ?, ?)'

Commands out of sync; you can't run this command now"

 

Even after googling that error I'm still in the dark. :-/

The commented out part in the function is what will allow things to work 'normally' (assuming I call the prepare() call on that connection new object).

 

Cheers for any insights/advice.

[BB]

If it makes any difference, this is with MySQL 5.0.51 / PHP 5.2.4 on Ubuntu 8.04.

[premiso]

Show us the mysql file that starts the mysql connection so we can see that.

[BB]

Sure thing:

 

<?php
   /*
      This file contains the db access information and establishes a 
      connection to it.
   */
   define('DB_NAME', ********);
   define('DB_HOST', 'localhost');
   define('DB_USER', ********);
   define('DB_PASSWORD', ********);
   
   $db_conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ( 'There was a problem connecting to the database: ' . mysqli_connect_error() );

   if ( $db_conn )
   {
      /* The DB holds data in utf8 form, so make sure we switch to utf8 for the character set to/from the DB. */
      mysqli_query($db_conn, "SET CHARACTER SET 'utf8';");
   }
?>

[Mchl]

try freeing the result before preparing another statement

 

mysqli_result::free()

[BB]

Cheers Mchl, you were heading in the right direction and that prod got me searching into that side of things a bit more. In the end, adding this after closing the statement in the function seems to fix the problem:

 

      while($db_conn->next_result())
      {
         if ( $l_result = $db_conn->store_result() ) $l_result->free();
      }

 

Having read the documentation I'm still not 100% sure why that's needed but it sounds like the stored proc returns multiple result sets (even though in my SQL I only return a single value).

 

Oh well, thanks to both of you for helping out. Much appreciated!