MySQL Forums
Forum List  »  Quality Assurance

Please Help: use mysql_connect, mysqli_multi_query, stmt_prepare to get the prepared stmt output in stored procedure
Posted by: Shen Liang
Date: April 21, 2009 08:43PM

Hi,
I find a very strange behaviors. Not sure how to solve it. The idea is to get the output multi-recordset from the stored procedure. I try the mysql_connect, mysqli_multi_query and stmt_bind_result. But they all behavior differently. My questions is why the mysqli_multi_query + mysqli_next_result can't retrieve the prepared stmt output records inside the stored procedure.

The following is what I have done

1. create stored procedure
CREATE PROCEDURE `GetTips`(TipType VARCHAR(20), RowCount INT)
BEGIN
PREPARE stmt from 'SELECT SQL_CALC_FOUND_ROWS * FROM `t1` LIMIT 10;';
execute stmt;
DEALLOCATE PREPARE stmt;
select found_rows() as totalrow;
END

2. try the mysql_connect with the CLIENT_MULTI_RESULTS
mysql_query("CALL GetTips('','')",$t);

This will return the PREPARE stmt's output recordset. But it can't get the second select statement output. It is reasonable since there is no next_result in Php.

3. try the mysqli multi query
mysqli_multi_query($db,"CALL GetTips('','')")

this will return only the second select statement output. It seems the first prepared stmt output is total invisible. What is the reason for this???

4. try the stmt bind_result
$stmt = $dbw->prepare("CALL GetTips('','')");
$stmt->execute();

the good news is stmt is able to detect 2 recordset. The bad news is there is no record output just as mentioned in the php doc.

Options: ReplyQuote


Subject
Views
Written By
Posted
Please Help: use mysql_connect, mysqli_multi_query, stmt_prepare to get the prepared stmt output in stored procedure
5032
April 21, 2009 08:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.