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

Question

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();

Just a tip: You don't need to concatenate in PHP's rand() output. You can call RAND() natively in MySQL, and you don't need a loop: INSERT INTO test_table VALUES (RAND(),RAND()),(RAND(),RAND()),(RAND(),RAND()),(RAND(),RAND()); Separate multiple inserts by (),(),() in the VALUES. — Michael Berkowski
– Michael Berkowski, Commented May 26, 2014 at 14:01
@Fred-ii- <<<'NOW' is nowdoc syntax, as you will see if you read the page you've kindly linked to. — lonesomeday
– lonesomeday, Commented May 26, 2014 at 14:01
You shouldn't be running this kind of query as part of your normal code anyway... stuff like this should be done directly in mysql from the command line, or through PHPMyAdmin or a similar GUI. — Niet the Dark Absol
– Niet the Dark Absol, Commented May 26, 2014 at 14:04
The DELIMITER is a client feature of the MySQL command line client, it is not a normal MySQL syntax feature and will generate a syntax error if you try to use it in a PDO query. — Michael Berkowski
– Michael Berkowski, Commented May 26, 2014 at 14:04

rsk82 · Accepted Answer · 2014-05-26 14:13:41Z

4

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.

edited May 26, 2014 at 14:13

rsk82

29.6k54 gold badges159 silver badges251 bronze badges

answered May 26, 2014 at 14:08

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

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

3 Comments

Jared Farrish Over a year ago

Minor quibbling: mysqli does support multiple statements.

Niet the Dark Absol Over a year ago

@JaredFarrish That is does, however this particular case uses PDO and the mysql driver.

Niet the Dark Absol Over a year ago

new PDO(" mysql :host=localhost...

rsk82 · Accepted Answer · 2014-05-26 14:13:53Z

0

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 ;');

edited May 26, 2014 at 14:13

rsk82

29.6k54 gold badges159 silver badges251 bronze badges

answered May 26, 2014 at 14:04

Marek

7,4431 gold badge24 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related