User defined MySQL function not accessible with PHP PDO connection

Tags: php mysql pdo
By : Jrgns
Source: Stackoverflow.com
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?

By : Jrgns


Answers

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.

By : bobince


Cross platform development is a nice idea, but to be completely honest I have never seen an application that looks really good outside of its native environment. That is why I think, that if you really want to offer good user experience you should use native toolkits on all platforms that you want to support.

Of course, if you just want a proof of concept, then WinForms on a Mac (or Linux) are alright but if you want an application that competes against other native solution then you really should consider writing a separate native frontend for each supported platform.

A good example how that was done is MindManager from Mindjet. Their Mac version is a full, from scratch rewrite of the interface. The result is an application that gives Mac users the experience that they learned to expect on their platform.

So, back to Mono. There are Cocoa bindings in Mono. Personally, I have never tried using them, but if you want to target Macs with your application, they are certainly worth a look. It would be really great if they could be regarded as a valid choice for UI development on a Mac.



GtkSharp requires X11 on Mac OS X. Mac users will find that off-putting. I haven't tried Winforms on OS X, but mono's Linux winforms support was pretty immature (buggy) and slow. I don't think there is a mature .net GUI toolkit for OS X at this point, at least nothing at the level of Swing or SWT.

By : karunski


This video can help you solving your question :)
By: admin