Doctrine (DBAL) Error Handling while Executing Multiple Queries

Question

I have a schema sql file (with syntax error) including multiple queries for settings database

example.sql

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATExxxxxxxxxx TABLE IF NOT EXISTS `example2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

example.php

$sqlContent = file_get_contents("example.sql");
$stmt = $conn->prepare($sqlContent);
$result = $stmt->execute();

execute method doesn't throw any exception even that my sql is incorrect. it documentation says it returns false on failure but it returns true.

How should I do exception handling here? How can I check if my query has an error?

Cheerly

Doctrine DBAL uses the mysqli driver. I checked the latest code and the DBAL should throw an exception when mysqli's prepare returns false for the syntax errors. Issue with the mysqli driver? doctrine-project.org/api/dbal/2.5/… – Lallation

@MattS the problem is not in the Doctrine DBAL, it is in PDO. I added my answer. – Yacht

Answer 1

The problem is not in Doctrine DBAL but in PDO. If you change the driver to mysqli (instead of pdo_mysql), then you will get an error message like this:

[Doctrine\DBAL\Exception\SyntaxErrorException]
An exception occurred while executing 'CREATE TABLE IF NOT EXISTS example (

`id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                                                   
`name` text COLLATE utf8_unicode_ci NOT NULL,                                                                                                                                           
PRIMARY KEY (`id`)                                                                                                                                                                         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

AUTO_INCREMENT=1 ;
CREATExxxxxxxxxx TABLE IF NOT EXISTS example2 (

`id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                                                   
`name` text COLLATE utf8_unicode_ci NOT NULL,                                                                                                                                           
PRIMARY KEY (`id`)                                                                                                                                                                         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

AUTO_INCREMENT=1 ;':
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATExxxxxxxxxx TABLE IF NOT EXISTS example2 (
id int(11) NOT NULL AUTO_I' at line 8

If you use PDO directly (not via Doctrine), then also you do not receive an error.

If you want that it will work properly, then you must to disable emulation of prepared statements (set PDO::ATTR_EMULATE_PREPARES to 0).

Yacht