CODEWITHSUNDEEP
phpmysqlpdo
Alex Schenkel
Alex Schenkel

Reputation: 832

How to get SQL errors when using multiple statements

We are using PHP >= 7.2 with PDO with the MySQL driver to execute multiple SQL statements in one exec() call. We use this concept to create DB Migration scripts, which are executed by PHP. These plain SQL scripts contain multiple SQL statements, which are executed by our PHP framework, like this:

$conn = PDO::conn('mysql:......','user','pw');
$ret = $conn->exec('
    DROP VIEW IF EXISTS v_my_view;
    CREATE VIEW v_my_view AS SELECT id,name FROM mytable;
');
// $ret now contains 1
if ($ret === FALSE) { // some error handling }

This works fine, even for many statements within one exec() call: The database executes all statements.

Now if the SQL itself contains an error, (e.g. syntax error, or unknown column etc.), the call to exec() returns exactly the same as before (1), and also DBH::errorInfo() returns no error:

$conn = PDO::conn('mysql:......','user','pw');
$ret = $conn->exec('
    DROP VIEW IF EXISTS v_my_view;
    -- The following statement creates an SQL error (unknown column):
    CREATE VIEW v_my_view AS SELECT id,name_not_known FROM mytable;
');
// $ret still contains 1
var_dump($conn->errorInfo()); // Returns Error Code 0, all fine.

So it seems that PDO::exec() does support multiple statements, but does not implement any error handling / abort mechanism. This also seems to be a MySQL-only problem: The same mechanism works fine on a PostgreSQL database.

Is there any way we can force PDO to "stop on errors" on multiple statement queries?

Upvotes: 4

Views: 1336

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157870

Yes, there is. It is explained in my PDO tutorial, under the running multiple queries section.

Basically you need to temporarily switch the emulation mode on with 

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

and then run your SQL dump using a regular query() method. and then you will have to loop over results of every query to catch the error. As your queries don't seem to return any data, the loop could be as simple as 

do {} while ($stmt->nextRowset());

Note that you must enable exception mode for PDO in order to get an error exception for the erroneous query. In case you will need some data from the queries, such as insert id, you may add necessary commands inside the curly braces.

Upvotes: 2

Related Questions