Posted by Jerry Stuckle on 11/11/19 12:00

Ratfish wrote:
> On Jan 16, 10:36 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Ratfish wrote:
>>> On Jan 16, 4:06 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> 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.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> 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.
>> jstuck...@attglobal.net
>> ==================
>
> 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.
jstucklex@attglobal.net
==================

[Back to original message]