CODEWITHSUNDEEP
phpmysqlstored-procedurespdodelimiter
automatix
automatix

Reputation: 14532

How to create a stored procedure containing a DELIMITER in PHP with PDO?

In my model I defined some procedures. The code (generated by MySQL Workbench) contains DELIMITER definitions, so the procedures look like:

-- schema
CREATE DATABASE ...
CREATE TABLE foo ...
-- procedures
DELIMITER $$
...
  BEGIN
    DECLARE ... ;
    OPEN ... ;
    SET ... ;
    ... ;
  END$$
DELIMITER ;

Now I need to "import" the SQL to the database via PDO. I tried to pass it as input for the PDO#exec(...), but noticed, that the execution stops on the line of the first DELIMITER definition.

I don't want remove the DELIMITER statements. So the the SQL code should remain the same.

How to use PDO to execute SQL code containing DELIMITER statements?

Upvotes: 2

Views: 2663

Answers (3)

Michael - sqlbot
Michael - sqlbot

Reputation: 179154

From comments:

I don't want remove the DELIMITER statements. And actually I want to get it working without to execute every statement manually

That's not how it works.

To understand why, you need to understand how the mysql CLI -- and any other program that can read and execute a dump file like this -- actually handles it.

DELIMITER is not something the server understands.

DELIMITER is used to tell the client-side parser what the current statement delimiter should be, so that the client-side parser can correctly split the statements and deliver one at a time to the server for execution.

From the docs. Note carefully that mysql, every time it is used here, refers to the mysql client utility -- not the server.

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. [...] The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

So, to handle such a file, you need a client-side parser that does the same thing mysql does... and here, the code you are writing is (needs to be) the client-side statement parser. So you are the one that needs to write the logic to handle the delimiter.

To do what you want, you have to interpret the DELIMITER statements, use them to keep track of the current statement delimiter, but do not send them to the server.

Then, you have to read through the input one line at a time, buffering what you've read, until you find the specified delimiter at the end of the line, and send the resulting statement to the server -- excluding the actual statement delimiter from what you send... so, for example, you would not send the ending $$ after the procedure body (unless the current statement delimiter is ;, which you can either send or not send -- the server doesn't care.) Then empty the buffer and start reading again until you see another instance of a delimiter (and send the statement to the server) or match a DELIMITER statement and set your code's current delimiter variable to match it so that you correctly identify the end of the next statement.

Upvotes: 9

ThangTD
ThangTD

Reputation: 1684

I met same problem with you when I tried with PostgreSQL. The problem seems PDO just allow you execute 1 query 1 time. As mentioned: PDO::exec() executes an SQL statement in a single function call, returning the number of rows affected by the statement. In php manual

Could you try this:

$stmt = $db->prepare($sql);
$stmt->execute();

Or with mysqli: multi_query. php manual

Here my whole class: http://sandbox.onlinephpfunctions.com/code/f0528fda6d7bd097c3199f1f3c019805a163ae3a

Upvotes: -2

Your Common Sense
Your Common Sense

Reputation: 157885

Delimiters is a thing that you don't need with PDO. You can just run your queries as is

$pdo->query("CREATE DATABASE ...");
$pdo->query("CREATE TABLE foo ...");
$pdo->query("BEGIN
    DECLARE ... ;
    OPEN ... ;
    SET ... ;
    ... ;
  END");

as simple as that

Upvotes: 2

Related Questions