CODEWITHSUNDEEP
phpmysqlpdouser-defined-functions
rsk82
rsk82

Reputation: 29387

How to use a MySQL user function with PDO in PHP?

This is what I've coined up, the code doesn't work and I have no idea what I'm doing wrong.

For example I don't know how I'm supposed to register my function - how to inject it in relation to the query etc. Could someone tell me how to write this piece of code correctly?

error: 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
 error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELI
MITER $$
CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANG' at line 1' in mysql_test.php:18
Stack trace:
#0 mysql_test.php(18): PDO->exec('DELIMITER $$\r\nC...')
#1 {main}
  thrown in mysql_test.php on line 18

the code:

$db = new PDO("mysql:host=localhost;dbname=test_db;charset=utf8", 'root');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$db->exec("CREATE TABLE IF NOT EXISTS test_table (col1 INT, col2 INT);");
for ($i = 0; $i<10; $i++) {
  $db->exec("INSERT INTO test_table VALUES(".rand().",".rand().");");
}
$mysql_function = <<<'NOW'
DELIMITER $$
CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANGUAGE SQL
BEGIN
  RETURN int1+int2;
END;
$$
DELIMITER ;
NOW;
$db->exec($mysql_function);
$db->query("SELECT my_sum(col1, col2) FROM test_table")->fetchAll();

Upvotes: 1

Views: 984

Answers (2)

Marek
Marek

Reputation: 7433

Execute the function creation in individual steps:

$db->exec('DELIMITER $$');
$db->exec('CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANGUAGE SQL
BEGIN
  RETURN int1+int2;
END $$');
$db->exec('DELIMITER ;');

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324640

The mysql driver does not support multi-statement queries. Therefore, DELIMITER makes no sense in a query, and the result is that it is a syntax error to try and use it.

You should be able to just have:

$mysql_function = <<<'NOW'
CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANGUAGE SQL
BEGIN
  RETURN int1+int2;
END;
NOW;

However, CREATE FUNCTION is a Data Definition Statement and you should probably never have such statements in your PHP-run queries. Table structures and functions should be defined when you're setting up the server, using one-time-use queries such as from a .sql file, through the mysql command line, or an administration GUI like phpMyAdmin.

Upvotes: 4

Related Questions