Posted by Ratfish on 01/18/08 17:36

On Jan 17, 11:40 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> 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.
> jstuck...@attglobal.net
> ==================

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

[Back to original message]