Hi!
I'm currently developing an application which makes heave use of stored
procedures, stored functions and MySQL's SIGNAL/RESIGNAL feature.
Everything works great if I call only one stored procedure.
But if I call a stored procedure which calls another stored procedure or
function, SIGNALS from stored procedures are not available as exceptions
in PHP.
---------------- doSth stored procedure ----------------
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 ;
---------------------------------------------------------
--------------- signalSth stored function ---------------
DELIMITER $$
USE signaltest$$
DROP FUNCTION IF EXISTS signalSth$$
CREATE FUNCTION signalSth() RETURNS BOOLEAN
BEGIN
SIGNAL SQLSTATE '45001';
RETURN TRUE;
END$$
DELIMITER ;
---------------------------------------------------------
<?php
$host = '';
$db = '';
$user = '';
$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 {
if (! $stmt->execute()) {
echo 'error: ' . PHP_EOL;
print_r($stmt->errorInfo());
print_r($stmt->errorCode());
}
echo 'ok: ' . PHP_EOL;
var_dump($stmt->fetchAll(\PDO::FETCH_ASSOC));
} catch (\PDOException $e) {
echo $e->getMessage() . PHP_EOL;
}
I expect the exception message to be printed to console: "Unhandled
user-defined exception condition" instead
"ok:
array(0) {
}"
is printed.
If I call the stored procedure through MySQL client, the SIGNAL is
handled correctly:
--------------------------------------------------------------
mysql> call doSth();
Empty set (0.00 sec)
ERROR 1644 (45001): Unhandled user-defined exception condition
--------------------------------------------------------------
Some version information:
PHP: PHP 5.4.7-1~dotdeb.0 (cli) (built: Sep 15 2012 09:53:20)
MySQL: 5.5.22-ndb-7.2.6-log
mysqlnd => enabled
Version => mysqlnd 5.0.10 - 20111026 - $Id:
b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $
Compression => supported
SSL => supported
Command buffer size => 4096
Read buffer size => 32768
Read timeout => 31536000
Collecting statistics => Yes
Collecting memory statistics => No
Tracing => n/a
Loaded plugins =>
mysqlnd,example,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password
API Extensions => mysql,mysqli,pdo_mysql
Am I doing anything wrong, is this feature not supported by the PHP
driver or is it a bug?
Regards,
Marco
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
