How to know if there is a mysql syntax error from pdo

[guillermobytes]

Hi,
i'm making a query with PDO and there is a SQL syntax error in it.

$sql = 'BAD CODE';
$pdoStmt = $pdo->prepare($sql);
if (false === $pdoStmt) {
    echo 'ERROR';
}

it does not echo 'ERROR'

any suggestions on how i should test a syntax error?

[Dormilich]

if I remember right, PDO throws a PDOException in this case …

Return Values

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

[guillermobytes]

ok, wierd, because it didn't throw any Exception neither :S

[Dormilich]

I suspect, "BAD CODE" isn’t enough to cause a MySQL error …

[Atli]

See PDO::errorInfo.

@Dormilich
It does.

#1064 - 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 'BAD CODE' at line 1

[Atli]

That's odd... The example in the manual, using the PDO::prepare method doesn't work, but if you substitute it for PDO::query, then it does...

$stmt = $dbh->query('bogus sql');
if (!$stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}

PDO::errorInfo():
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 'bogus sql' at line 1
)

[Atli]

Ahh Ok, you need to execute it and use the PDOStatement::errorInfo field.

$stmt = $dbh->prepare('bogus sql');
$res = $stmt->execute();
if (!$res) {
    echo "\nPDO::errorInfo():\n";
    print_r($stmt->errorInfo());
}

... Not often that you see errors like that in the manual :|

[Markus]

What error?

[Atli]

@Markus
According to example #1 for PDO::errorInfo, this:

$stmt = $dbh->prepare('bogus sql');
if (!$stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}

Should produce something like:

PDO::errorInfo():
Array
(
    [0] => HY000
    [1] => 1
    [2] => near "bogus": syntax error
)

However, according to my tests, it does not.
Granted, I tested this against a MySQL database rather than DB2, but PDO should act consistently despite which database you are using.

[Markus]

Oooh. I'll update the docs.

[guillermobytes]

ok thanks for the details

[Atli]

@Markus
You can do that? :O
Nice :D