Execute mysql "create function" statement with PHP

Question

I want to run the following mysql create function statement from PHP:

DELIMITER $$
CREATE FUNCTION `myFunc`(`instring` varchar(4000)) RETURNS int(11)
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE position int;
    ....here comes function logic
    RETURN position;
END$$
DELIMITER ;

But I get this mysql error:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER'

What can I do? Can I execute create statement without DELIMITER keyword?

Don't use the mysql_ functions. They're deprecated for a while now. The two libraries that are still maintained are mysqli and PDO. — Evert
– Evert, Commented Jan 28, 2013 at 19:23
MySQL is not deprecated. It is the mysql_* family of functions that are. Use mysqli_* or PDO. — Ayush
– Ayush, Commented Jan 28, 2013 at 19:24
Bingo (credit to google): stackoverflow.com/questions/5311141/… — Matt Dodge
– Matt Dodge, Commented Jan 28, 2013 at 19:25
I am using mysqli, but that's just an adapter. Sql client is still mysql. — Borut
– Borut, Commented Jan 28, 2013 at 19:29

Community · Accepted Answer · 2020-06-20 09:12:55Z

13

You most likely do not need the DELIMTER command. That belongs to MySQL-centric client programs.

Please try with plain old semicolons:

if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
    !$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;")) {
    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

and let PHP worry about delimiting

CAVEAT

I got the above code from http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 28, 2013 at 19:26

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

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

1 Comment

Jaak Kütt Over a year ago

... "and let PHP worry about delimiting" <-- saved my day :D

Collectives™ on Stack Overflow

Execute mysql "create function" statement with PHP

1 Answer 1

CAVEAT

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

CAVEAT

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related