MySQL Forums :: PHP :: Stored procedure, stored function and signals


Advanced Search

Stored procedure, stored function and signals
Posted by: Marco Baumgartl ()
Date: August 03, 2012 01:07AM

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.

Options: ReplyQuote


Subject Written By Posted
Stored procedure, stored function and signals Marco Baumgartl 08/03/2012 01:07AM
Re: Stored procedure, stored function and signals Peter Brawley 08/03/2012 10:31PM
Re: Stored procedure, stored function and signals Marco Baumgartl 08/04/2012 03:56PM
Re: Stored procedure, stored function and signals Peter Brawley 08/05/2012 01:02PM
Re: Stored procedure, stored function and signals Marco Baumgartl 08/06/2012 02:53AM
Re: Stored procedure, stored function and signals Peter Brawley 08/06/2012 12:51PM
Re: Stored procedure, stored function and signals Mark Meyer 08/30/2012 02:52PM
Re: Stored procedure, stored function and signals Peter Brawley 08/30/2012 09:54PM


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.