Stored procedure, stored function and signals

[Marco Baumgartl]

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.