PEAR MDB2 executeStoredProc and returns a result set. Possible? Worth it?
I've been trying to make this work for the last couple of hours. I have a simple stored procedure called get_log_entries:
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_log_entries`(p_min_id INT)
BEGIN
SET p_min_id = IFNULL(p_min_id, -1);
SELECT * FROM db_log WHERE item_id > p_min_id;
END
It's dead simple and returns results in two columns: item_id (int) and log_description (varchar).
I am trying to accomplish this using an MDB2 object but no luck so far. Here's some code that tries to do it:
$conn = MDB2::factory('mysql://myUser:myPassword@localhost/my_db');
if (PEAR::isError($conn)) {
die ("Cannot connet to DB(10): " . $conn->getMessage());
}
// loading the Function module
$conn->loadModule('Function');
$params = array('null');
$result = $conn->executeStoredProc('get_log_entries', $params);
if (PEAR::isError($result)) {
$msg = $result->getMessage() . "<br /><br />" . $result->getUserInfo();
die ($msg);
}
At this point this nice error appears with the message:
"_ doQuery: [Error message: execute statement failed] [Last executed query: CALL get_log_entries ()] [Native code: 1312] [Native message: PROCEDURE wh_search.get_log_entries cannot return a result set in this context]"
Now I have a couple of questions:
1. Is it even possible to execute SP using MDB2 and return results?
2. Or is it better to write a wrapper class for "native" PHP-MySQL functions?
Thank!
source to share