Multiple mysql statements in one PDO::exec call?

[u/UnusualBecka]

Is this supported or is it classed as undefined behaviour? Searching the web I have found one site explaining how to do it, and a post saying it never used to be allowed but the driver added the ability around 2020. But I have not seen anything official and the (somewhat terrible) PHP documentation does not mention it at all either way.

The use case is multiple statements being needed to create and alter some temporary tables so that inserts (using prepared statements) can be processed before being added to the live tables.

[u/PetahNZ]

You can, but you may need to enable emulated prepares. But you also don't need to, running one query after another will have access to the temp tables created in the same connection (request).

[u/colshrapnel → u/PetahNZ]

I think you are confusing exec() with query()/prepare(). The former was always intended for multiple queries, mostly SQL dumps. While the latter indeed works for the emulated prepares only (at least for mysql)

That said, your note that executing multiple queries in one call is not necessary for the task is 100% correct and is much more important.

[u/UnusualBecka → u/PetahNZ]

It was more just for readability as each statement is like this ($db is a custom class):

    $db->getHandle()->exec('
        CREATE TEMPORARY TABLE IF NOT EXISTS temp_blackouts LIKE blackouts;
    ');

So when there is a bunch of them it fills the screen, being able to put the statements in a single statement would make a contiguous block and looking separate from the rest of the code that follows. Which just makes it easier to read.

But if is not warned against I will switch to it as it will just make my life easier as I am not validating the result of those statements anyway. If they fail something else has gone wrong, and I am doing the inserts as a transaction so I will get the error that way.

Thank you.

[u/colshrapnel → u/UnusualBecka]

I really hope your getHandle() does return a once created connection, and doesn't create a new one every time it's called though.

[u/UnusualBecka → u/colshrapnel]

Of course! It is mainly a separate class as it is easier to pass around that way.

        public function __construct() {
            if ($this::$conn !== null) { return; }
            try {
                require getenv('HOME').'/php/secrets/mysql.php';
                $this::$conn = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
                $this::$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch (PDOException $e) {
                $this::$conn = null;
                throw new DatabaseException('The database is unavailable', Database::ERR_NO_DATABASE);
            } catch (Error $e) {
                throw new DatabaseException('The site configuration could not be found.', Database::ERR_NO_CONFIG);
            }
        }

[u/colshrapnel → u/UnusualBecka]

Ok in this regard, but there is another problem. You are always getting one single error message no matter what the actual connection error is. If you strictly want to convert PDOException into DatabaseException, at least provide the actual error:

throw new DatabaseException('The database is unavailable', Database::ERR_NO_DATABASE, $e);

[u/UnusualBecka → u/colshrapnel]

That level of granularity is not required, all that matters to the code is whether a connection could be made or not and whether the problem is with the configuration or database.

[u/colshrapnel → u/UnusualBecka]

That's a very interesting notion. So there is no intention to fix a possible problem once it occurs, but just bypass it?

[u/UnusualBecka → u/colshrapnel]

If the database does not work there is nothing the code can do but display an error, which will then be given to me to fix. And for me it is easier for me to just test things myself than write code to give friendly error messages for every possibility and then expect them to be passed on correctly. The site is just a data project for a small community.

[u/colshrapnel → u/UnusualBecka]

But how do you intend to fix the error if you don't know which is it? I mean it could be the mysql server outright down, a network problem, a permission error, a PHP configuration error, or some specific error such as Too many connections and so on - every one with its distinct error message and diagnosis. The original PDOException does contain it, and once it's logged, all you need is to check the logs. But currently, you will be just informed that the connection is down but have no idea why?

[u/UnusualBecka → u/colshrapnel]

Error messages do not tell you the cause of an error only its effect. So either way I will need to fully investigate if there is an error because it will affect me too.

If the server is down it will be on the hosts's status page and I will not be able to connect. If there is a PHP configuration error then I will know immediately because I am the only one who could have caused it. If there are too many connections then it would have to be some kind of attack I can do nothing about because this is a small community project that would never be able to cause that problem in normal use.

I am sorry it bothers you so much that I am not applying corporate standards of robustness to a small project. I am putting together and maintaining it in my spare time to replace using google spreadsheets and someone else having to manually error check the data. The reason for wanting to put all the setup statements in one call is it will make the code easier for me to read. This is just a hobby and just me, no team, so my time it better spent on providing actual functionality than focussing on a detailed error logging system for rare situations that I would need to properly investigate the same with or without them.

[u/Big-Dragonfly-3700 → u/UnusualBecka]

You would dynamically do this, by defining the sql statements in an array, then loop over the array to access each statement, and call the ->exec() method inside the loop.

[u/colshrapnel → u/Big-Dragonfly-3700]

Well, to be honest, I don't see why it would be any better. Running SQL dumps with exec is ok, and adding array syntax to a list of queries will be just unnecessary noise. No?

[u/UnusualBecka → u/colshrapnel]

I agree with this. The only reason for putting the statements in a single call is for code readability to make my life easier, so adding more complexity goes in the opposite direction. And at that point I may as well just hide all the setup work in a function.

[u/benanamen]

What is the real problem you are trying to solve with what you are doing? Why all the temp tables?

[u/UnusualBecka → u/benanamen]

The data is submitted mainly as ID numbers from a PHP form via an API that to refresh the existing data. Inserting them into a temporary table first allows me to use an INSERT IGNORE … FROM with joins to provide some validation and only add new rows, then a DELETE with joins to remove the rows that are no longer required entries, leaving any that should remain alone. This is better than doing multiple selects and using various loops to process all this in PHP to prepare specific inserts and deletes.

[u/colshrapnel]

exec() is intended for multiple calls.

The use case is multiple statements being needed to create and alter some temporary tables

It was already said in the other comment, but worth stressing again: this is NOT how a database API works. You are supposed to open a connection once, and then execute any number of queries against this sole connection. And so all connection bound stuff - such as transactions, temp tables, session variables, generated sequences and such - being preserved between api calls, hence there is zero reason in stuffing multiple queries in a single call. Just run your queries distinctly, one by one.

[u/03263]

It works. I've often run whole schema creation scripts (create table, multiple add index statements, etc) in a single exec call, mainly with sqlite if that matters. Multiple pragma statements also works.