User defined MySQL function not accessible with PHP PDO connection

Question

I've got a trivial MySQL function:

DELIMITER $$
DROP FUNCTION IF EXISTS `mydb`.`CALC` $$
CREATE FUNCTION `mydb`.`CALC_IT`(Flag VARCHAR(1), One FLOAT, Other FLOAT) 
RETURNS FLOAT
BEGIN
    IF One IS NULL THEN RETURN 0; END IF;
    IF Other IS NULL THEN RETURN 0; END IF;
    IF Flag = 'Y' THEN
        RETURN Other - One;
    ELSE
        RETURN Other
    END IF;
END $$
DELIMITER ;

And it's called in a query from PHP using a PDO connection:

$query = 'SELECT CALC_IT(`Flag`, `One`, `Two`) FROM `mydb`.`table` WHERE `Condition` = 1';
$db = new PDO('mysql:host=localhost', 'user', 'pass');
$stmt = $db->prepare($query);
if (!$stmt->execute()) {
    var_dump($stmt->errorInfo());
}

But, it reports the following:

array
  0 => string '42000' (length=5)
  1 => int 1305
  2 => string 'FUNCTION CALC_IT does not exist' (length=37)

And, if you try it with the legacy Mysql code, it works:

$db = mysql_connect('localhost', 'user', 'pass');
$result = mysql_query($query);
if (mysql_error()) {
    var_dump(mysql_error());
}

The query also works if you try it with any other mysql client.

So why doesn't some user defined MySQL functions work in PHP's PDO library?

bobince · Accepted Answer · 2009-04-15 16:41:08Z

1

new PDO('mysql:host=localhost', 'user', 'pass');

Missing dbname=mydb?

it will be mydb.CALC_IT

Yep, that's a possibility. There are advantages and disadvantages to selecting a database at connect-time rather than explicitly specifying it in the query. If you're only using a single database on the server it tends to be easier to say so at connect-time and handle the possible access-rights errors then, but if you're doing cross-db work the explicit way “mydb.CALC_IT” will be necessary.

edited Apr 15, 2009 at 16:41

answered Apr 15, 2009 at 14:43

bobince

538k111 gold badges675 silver badges846 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jrgns Over a year ago

Mmmm, ok. Looks like you need to directly reference the function if you call it through PDO, so it will be mydb.CALC_IT. Please update your answer, and I'll accept it!

Collectives™ on Stack Overflow

User defined MySQL function not accessible with PHP PDO connection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related