Commands out of sync error

[Ratfish]

I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.

Aaron

[Marlin Forbes]

Ratfish wrote:

root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.

Hi Ratfish,

I think you must free the resultsets from the stored procedures before
opening another one. Look at mysql_free_result if you're using the mysql
extension, or mysqli_stmt_free_result if using the mysqli extension.

Regards,
Marlin Forbes
Data Shaman
datashaman.com

[Ratfish]

On Jan 16, 12:09 am, Marlin Forbes <"marlinf <ATdatashaman <POINT>
wrote:

Ratfish wrote:

root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.

Hi Ratfish,

I think you must free the resultsets from the stored procedures before
opening another one. Look at mysql_free_result if you're using the mysql
extension, or mysqli_stmt_free_result if using the mysqli extension.

Regards,
Marlin Forbes
Data Shaman
datashaman.com

Thanks. I tried adding a call to free_result() before calling
close(), but that did not fix my problem. Any other ideas?

$stmt->free_result();
$stmt->close();

Aaron

[Jerry Stuckle]

Ratfish wrote:

I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.

Aaron

Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Ratfish]

On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:

Ratfish wrote:

I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.

Aaron

Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Here's the code that fails:

// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}

$stmt->bind_param("i", $EntId);

// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}

//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>

[Jerry Stuckle]

Ratfish wrote:

On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:

>Ratfish wrote:

>>I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.
Aaron

Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Here's the code that fails:

// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}

$stmt->bind_param("i", $EntId);

// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}

//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>

OK, what is sps_entertainment returning?

If it's returning more than one row, you need to continue fetching until
they're all returned.

Also, I see if it returns no rows, you're not freeing the result. You
still have to free the result, even if no rows are returned.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Ratfish]

On Jan 16, 10:36 am, Jerry Stuckle <[e-mail]:

Ratfish wrote:

On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:

Ratfish wrote:
I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.
Aaron
Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

Here's the code that fails:

// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}

$stmt->bind_param("i", $EntId);

// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}

//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>

OK, what is sps_entertainment returning?

If it's returning more than one row, you need to continue fetching until
they're all returned.

Also, I see if it returns no rows, you're not freeing the result. You
still have to free the result, even if no rows are returned.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

sps_entertainment returns a single record. I've removed the freeing of
the result outside of the fetch if statement, but am still having the
issue...

[Jerry Stuckle]

Ratfish wrote:

On Jan 16, 10:36 am, Jerry Stuckle <[e-mail]:

>Ratfish wrote:

>>On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:
Ratfish wrote:
I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.
Aaron
Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================
Here's the code that fails:
// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
$stmt->bind_param("i", $EntId);
// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>

OK, what is sps_entertainment returning?

If it's returning more than one row, you need to continue fetching until
they're all returned.

Also, I see if it returns no rows, you're not freeing the result. You
still have to free the result, even if no rows are returned.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

sps_entertainment returns a single record. I've removed the freeing of
the result outside of the fetch if statement, but am still having the
issue...

First of all, ensure that it only returns a single result. I've had
SP's return multiple results before when I only expect one. Try calling
it from the MySQL command line, for instance.

Also, you say this is where you "think" the error occurs. Are you sure?
You might be chasing the wrong error. Try adding identifiers to your
error messages to tell you exactly which one is failing.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Ratfish]

On Jan 17, 11:40 am, Jerry Stuckle <[e-mail]:

Ratfish wrote:

On Jan 16, 10:36 am, Jerry Stuckle <[e-mail]:

Ratfish wrote:
On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:
Ratfish wrote:
I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be getting this
error? The error doesn't occur on my development box where I use the
'root' db user, but does occur in production where I'm using a non-
root user record to establish a connection. I'm essentially opening a
connection at the top of the php page and then calling multiple stored
procedures to fetch data. When I call the 2nd stored procedure I'm
getting the error. Any info would be greatly appreciated.
Aaron
Sorry, my crystal ball is in the shop. Since you didn't post any code,
I find it impossible to tell what's wrong.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================
Here's the code that fails:
// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
$stmt->bind_param("i", $EntId);
// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>
OK, what is sps_entertainment returning?

If it's returning more than one row, you need to continue fetching until
they're all returned.

Also, I see if it returns no rows, you're not freeing the result. You
still have to free the result, even if no rows are returned.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

sps_entertainment returns a single record. I've removed the freeing of
the result outside of the fetch if statement, but am still having the
issue...

First of all, ensure that it only returns a single result. I've had
SP's return multiple results before when I only expect one. Try calling
it from the MySQL command line, for instance.

Also, you say this is where you "think" the error occurs. Are you sure?
You might be chasing the wrong error. Try adding identifiers to your
error messages to tell you exactly which one is failing.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

I've verified that the stored procedure only returns a single result
set.

I'm not sure what you mean by "adding identifiers to your error
messages". Can you elaborate on that?

Aaron

[Jerry Stuckle]

Ratfish wrote:

On Jan 17, 11:40 am, Jerry Stuckle <[e-mail]:

>Ratfish wrote:

>>On Jan 16, 10:36 am, Jerry Stuckle <[e-mail]:
Ratfish wrote:
On Jan 16, 4:06 am, Jerry Stuckle <[e-mail]:
>Ratfish wrote:
>>I'm getting a "2014:: Commands out of sync; you can't run this command
>>now" error on a php page when I try to call a second stored procedure
>>against a MySQL db. Does anyone know why I might be getting this
>>error? The error doesn't occur on my development box where I use the
>>'root' db user, but does occur in production where I'm using a non-
>>root user record to establish a connection. I'm essentially opening a
>>connection at the top of the php page and then calling multiple stored
>>procedures to fetch data. When I call the 2nd stored procedure I'm
>>getting the error. Any info would be greatly appreciated.
>>Aaron
>Sorry, my crystal ball is in the shop. Since you didn't post any code,
>I find it impossible to tell what's wrong.
>--
>==================
>Remove the "x" from my email address
>Jerry Stuckle
>JDS Computer Training Corp.
>[e-mail]
>==================
Here's the code that fails:
// load the entertainment record
$sql = "call sps_entertainment(?)";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
$stmt->bind_param("i", $EntId);
// execute the statement
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
//if ($result = $link->store_result()) {
if ($stmt->bind_result($FEntId,
$FCreateDate,
$FCreateUser,
$FModifyDate,
$FModifyUser,
$FEntTypeCode,
$FEntName,
$FURL,
$FPictureQualityCode,
$FPictureResolutionCode,
$FRevenueSourceCode,
$FEntDesc,
$FEntFullDesc,
$FSiteStatusCode,
$FZombiiRatingCode,
$FEntIconId))
{
if ($stmt->fetch())
{
$stmt->free_result();
$stmt->close();
?>
do some html here
<select id="ddlEntIcon" name="ddlEntIcon">
<option value="*" >(Not Specified)</option>
<option value="NEW" >(Upload new image...)</option>
<?php
$sql = "call sps_entertainment_icons()";
$stmt = $link->prepare($sql);
if ($link->errno) {die($link->errno.":: ".$link->error);}
// execute the statement HERE'S WHERE I THINK THE ERROR IS OCCURING!
$stmt->execute();
if ($link->errno) {die($db->errno.":: ".$link->error);}
$stmt->bind_result($EntIconId, $EntIconName);
while ($stmt->fetch())
{
if ($FEntIconId != "" && $FEntIconId == $EntIconId) {
echo "<option value=\"" . $EntIconId . "\" selected>" .
$EntIconName . "</option>\n";
}
else {
echo "<option value=\"" . $EntIconId . "\">" . $EntIconName . "</
option>\n";
}
}
$stmt->free_result();
$stmt->close();
?>
</select>
OK, what is sps_entertainment returning?
If it's returning more than one row, you need to continue fetching until
they're all returned.
Also, I see if it returns no rows, you're not freeing the result. You
still have to free the result, even if no rows are returned.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================
sps_entertainment returns a single record. I've removed the freeing of
the result outside of the fetch if statement, but am still having the
issue...

First of all, ensure that it only returns a single result. I've had
SP's return multiple results before when I only expect one. Try calling
it from the MySQL command line, for instance.

Also, you say this is where you "think" the error occurs. Are you sure?
You might be chasing the wrong error. Try adding identifiers to your
error messages to tell you exactly which one is failing.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[e-mail]
==================

I've verified that the stored procedure only returns a single result
set.

I'm not sure what you mean by "adding identifiers to your error
messages". Can you elaborate on that?

Aaron

You indicated you "think" the error occurs in one place. But all you're
doing is putting out the error number and message, so you don't know for
sure.

Add unique text to each message so you know for sure which one had the
problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================