Insert common SQL query along with multiple functions

Question

I'm inserting an SQL Template in a new database through PHP code using the mysqli->multi_query() function. This SQL Template contains something like:

CREATE TABLE `applicants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DELIMITER $$ 
CREATE FUNCTION `myFunctionExample`(`someField` INT(11), `someOtherField` VARCHAR(255) CHARSET utf8) RETURNS double(10,3)
BEGIN
   .... MY SQL ....

   RETURN ...;
END;
DELIMITER $$ 
CREATE FUNCTION `mySecondFunctionExample`(`someField` INT(11), `someOtherField` VARCHAR(255) CHARSET utf8) RETURNS double(10,3)
BEGIN
   .... MY SQL ....

   RETURN ...;
END;

I have two different problems:

When using PHP:

All the tables are created, no functions are created

When using PHPMyAdmin:

All the tables are created, only the first function is created

So it seems I have a problem separating the functions (?) and somehow mysqli->multi_query() is not creating the functions?

Generally speaking, the ENDs on your functions' definitions need to be terminated with $$, and you should only need one DELIMITER $$ statement. — Uueerdo
– Uueerdo, Commented Nov 18, 2019 at 17:45
@Uueerdo your solution works great in PHPMyAdmin but with mysqli->multi_query() the functions are not created. — Linesofcode
– Linesofcode, Commented Nov 18, 2019 at 18:51
I could be wrong, but I think I recall reading something around here about multi_query not working with DDL statements. It looks like the official doc page specifically mentions have the queries separated by semi-colon; so that could rule out anything requiring DELIMITER statements, — Uueerdo
– Uueerdo, Commented Nov 18, 2019 at 18:55
@Uueerdo All my query statements are separated by semi-colon; is this what you were refering to? — Linesofcode
– Linesofcode, Commented Nov 18, 2019 at 19:37
No, I am saying END; does not terminate your CREATE FUNCTION statements when DELIMITER $$ has been used; and DELIMITER statements are not terminated with ; — Uueerdo
– Uueerdo, Commented Nov 18, 2019 at 19:40

Linesofcode · Accepted Answer · 2019-11-18 21:04:39Z

Solved.

With the help of @Uueerdo I end up realizing I might not need any limiters for my functions when creating them through PHP. I also found this thread Execute mysql "create function" statement with PHP that talks about not having to worry about delimiters in PHP.

So I just removed the DELIMITER $$ from my code and made sure I used ; to separate everything:

CREATE TABLE `applicants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE FUNCTION `myFunctionExample`(`someField` INT(11), `someOtherField` VARCHAR(255) CHARSET utf8) RETURNS double(10,3)
BEGIN
   .... MY SQL ....

   RETURN ...;
END;
CREATE FUNCTION `mySecondFunctionExample`(`someField` INT(11), `someOtherField` VARCHAR(255) CHARSET utf8) RETURNS double(10,3)
BEGIN
   .... MY SQL ....

   RETURN ...;
END;

Collectives™ on Stack Overflow

Insert common SQL query along with multiple functions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related