Weird return from called stored procedure
Posted: Thu Mar 05, 2009 8:08 am
As anyone run into this problem.
I have a valid stored procedure compiled on my MySQL (version 5.0) database.
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.
I thinking it has something to do with the while statement.
I'm an Oracle guy and new to MySQL so bear with me.
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;
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'm an Oracle guy and new to MySQL so bear with me.