6

I'm making the change from mysql_* calls to using PDO calls and am unable to figure out how to debug my actual SQL when developing new code.

Under mysql_* calls I could write some SQL in a PHP function and could echo the actual SQL to see what the processor was working with.

I haven't been able to find such a beastie in the PDO library. debugDumpParams looks like it should, but it doesn't spit back the bound statement.

Examples of problems I have encountered:

  1. In my very first attempt I was binding a string and was including the quotes in the SQL statement, despite binding with a data_type of string - it was only by fluke I tried removing the quotes from the statement; debugging would have let me see there was a repeated quote in there.

  2. I copied some code from one project to another and accidentally forgot to correct the database name. Naturally the SQL failed because the tables didn't exist in the other DB. But the program just returned the correct false result. Nowhere in the PHP logs or the MySQL logs or anywhere did I get a hint that the table didn't exist where I was looking for it.

So, how are other people doing debugging for PDO SQL calls? What am I missing? :)

Brad
  • 159,648
  • 54
  • 349
  • 530
Lea de Groot
  • 309
  • 4
  • 12
  • Umm, for some reason the editor removed my second example: 2. I copied some code from one project to another and accidentally forgot to correct the database name. Naturally the sql failed because the tables didn't exist in the other db. But the program just returned the correct false result. Nowhere in the php logs or the mysql logs or anywhere did I get a hint that the table didn't exist where I was looking for it. – Lea de Groot Aug 05 '12 at 02:37
  • @LeadeGroot, Formatting fixed. It put your first item on the same line as "Examples of problems...", making the second item your first in a numerically ordered list. Strange. – Brad Aug 05 '12 at 02:40
  • you can use exceptions in php to debug your errors – bugwheels94 Aug 05 '12 at 02:44

1 Answers1

4

For the first problem, checking the query is difficult indeed. There isn't much you can do about this except for logging the queries on your database server. The reason is that prepared queries are not (always) simply concatenating data into the query. The whole point is that the data is sent separately from the query. You can access the query string though via $yourStatement->queryString. It will only show the query with the parameters.

For your second problem, by default PDO doesn't throw an exception when an error occurs. It's up to you to check for them. You can change this though. http://php.net/manual/en/pdo.error-handling.php

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Then, you can catch problems by a try/catch block around your query execution.

Brad
  • 159,648
  • 54
  • 349
  • 530