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 `END`s on your functions' definitions need to be terminated with `$$`, and you should only need one `DELIMITER $$` statement. — Uueerdo, Nov 18 '19 at 17:45
@Uueerdo your solution works great in PHPMyAdmin but with `mysqli->multi_query()` the functions are not created. — Linesofcode, Nov 18 '19 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, Nov 18 '19 at 18:55
@Uueerdo All my query statements are separated by semi-colon; is this what you were refering to? — Linesofcode, Nov 18 '19 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, Nov 18 '19 at 19:40
@Uueerdo oh, ok. I tried to put `DELIMITER ;;` and `END$$` but does not work either. The tables are created without any problem but no function is created. — Linesofcode, Nov 18 '19 at 19:44
Creating functions through API calls (like mysqli) is often a little different than through scripts; you will probably have to resign yourself to creating them through separate `query` calls; and from information I could find from questions on msqli's predecessor, it is likely to do not need (and cannot use) DELIMITER at all. — Uueerdo, Nov 18 '19 at 19:47
Actually, it looks like [this](https://stackoverflow.com/questions/15418218/how-to-get-mysqli-working-with-delimiters-in-sql-statements) question addresses it better. — Uueerdo, Nov 18 '19 at 19:49
@Uueerdo I think I figure it out with your help, just give me some minutes. — Linesofcode, Nov 18 '19 at 19:50

score 1 · Accepted Answer · answered Nov 18 '19 at 21:04

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;

Insert common SQL query along with multiple functions

1 Answers1