Page 1 of 1

Weird return from called stored procedure

Posted: Thu Mar 05, 2009 8:08 am
by CanMikeF1
As anyone run into this problem.

I have a valid stored procedure compiled on my MySQL (version 5.0) database.

Code: Select all

 
PROCEDURE `get_ann`(
 IN LimitStart_ INT,
 IN LimitCnt_ INT)
BEGIN
 
SET @lim = CONCAT(' LIMIT ', LimitStart_, ',', LimitCnt_);
SET @q = "SELECT family_id as fid,
                  date_format(wedding_date, '%M %e, %Y')    as fdate,
                  post
             FROM family_rank a, family_details b, rights
            WHERE family_id = details_id
              AND family_id = rights_id
              AND post = 'y'";
 
SET @q = CONCAT(@q, @lim );
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
 
Works fine. To test, I called if from the command line in MySQL passing the appropriate @start and @end values.

However, when I call it from my test PHP code it returns garbage.

Code: Select all

 
<?php
 
require_once('./includes/mysqli_connect.php'); // Connect to db.
 
$start = 1;
$stop  = 10;
 
$sql = 'call get_ann(?,?)';
 
$stmt = $dbc->prepare($sql);
 
if ($dbc->errno) {die($dbc->errno.':1:'.$dbc->error);}
 
$stmt->bind_param("ii", $start, $stop);
 
$stmt->execute();
if ($dbc->errno) {die($dbc->errno.':2:'.$dbc->error);}
 
$stmt->bind_result($fid, $fdate, $post);
 
while ($stmt->fetch())
{
  echo 'Member id: '.$fid.'<br />';
  echo 'fdate    : '.$fdate.'<br />';
  echo 'post     : '.$post.'<br />';
}
 
?>
 
 
I thinking it has something to do with the while statement.

I'm an Oracle guy and new to MySQL so bear with me.

Re: Weird return from called stored procedure

Posted: Thu Mar 05, 2009 10:21 pm
by Benjamin
Can you please define or post an example of the garbage?

Re: Weird return from called stored procedure

Posted: Fri Mar 06, 2009 8:07 am
by CanMikeF1
This is the return from the call!
Member id: 1851869711
fdate : ary 1, 1908y�rightspostpost����þ���������が~bÊ™familydb��mily_id
post :
the "Member id:" field is only 6 character long on the base.
the "fdate:" should be a date
the "post:" should return either 'y' or 'n'

It seems to be trying to return values, just not he correct ones?