Stored procedure, stored function and signals
Hi,
I check the data in my stored procedures and report errors back to PHP using SIGNAL/RESIGNAL. This works great. To avoid duplicate code, I moved some logic to a function which reports errors by SIGNAL/RESIGNAL too. But this doesn't work.
-- stored procedure doSth
DELIMITER $$
USE signaltest$$
DROP PROCEDURE IF EXISTS doSth$$
CREATE PROCEDURE `doSth`()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
resignal;
END;
SELECT signalSth();
SELECT 'something';
END$$
DELIMITER ;
-- stored function signalSth
DELIMITER $$
USE signaltest$$
DROP FUNCTION IF EXISTS signalSth$$
CREATE FUNCTION `signalSth`() RETURNS BOOLEAN
BEGIN
SIGNAL SQLSTATE '45001';
RETURN TRUE;
END$$
DELIMITER ;
Calling doSth() in MySQL console, gives me the expected result:
mysql> call doSth();
Empty set (0.00 sec)
ERROR 1644 (45001): Unhandled user-defined exception condition
If I call the procedure from my PHP application I don't see any error:
$host = 'somehost';
$db = 'database';
$user = 'dbuser';
$password = 'password';
$dsn = sprintf('mysql:host=%s;dbname=%s', $host, $db);
$conn = new \PDO($dsn, $user, $password);
$conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare('CALL doSth()');
try {
$stmt->execute();
var_dump($stmt->fetchAll(\PDO::FETCH_ASSOC));
} catch (\PDOException $e) {
echo $e->getMessage() . PHP_EOL;
}
I'm using MySQL 5.5.22-ndb-7.2.6-log and PHP 5.3.13-1~dotdeb.0 with Suhosin-Patch.
Any hints to the problem?
Regards,
Marco
Edited 1 time(s). Last edit at 08/03/2012 01:24AM by Marco Baumgartl.
Subject
Written By
Posted
Stored procedure, stored function and signals
August 03, 2012 01:07AM
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.