[FIXED] how to get SQL generated by Pear MDB2 without executing it?

Issue

I'm working with Pear MDB2 with PHP 5.3. I'm coding a project that updates a DB and before I let it start changing data, I'd like to see what the SQL queries generated by autoPrepare() and execute() look like before actually executing them.

I plan to create and execute an update query like this:

    $stmt = $db->extended->autoPrepare($tableName, $tableColumns,
    MDB2_AUTOQUERY_UPDATE, 'id = ' . $db->quote(12345, 'integer'),
    $tableColumnTypes));

    $res =& $stmt->execute($tableColumnValues);

I already know that I can see the SQL generated by autoPrepare() with placeholders for the values by accessing $stmt->query. I'd like to see the completed SQL generated by execute(), with values substituted for placeholders, without actually sending the query to the DB.

How can I do that?

Solution

Prepared statements are compiled on the server-side, so you can't see them before they execute. Per example, in MySQL, if you want to execute a prepared statement, what MDB2 actually does is:

PREPARE stmt FROM 'SELECT * FROM foo WHERE bar = ?';
SET @baz = 'baz';
EXECUTE stmt USING @baz;

The server never "returns" the actual query it executed. If you want to see what query was executed, you'll have to set-up a query log.

Per example, in MySQL (my.cnf):

[mysqld]
general_log_file = /var/log/mysql_queries.log
general_log = 1

The query log would show, for the query example above:

Query     PREPARE stmt FROM 'SELECT * FROM foo WHERE bar = ?';
Query     SET @baz = 'baz';
Query     EXECUTE stmt USING @baz;
Execute   SELECT * FROM foo WHERE bar = 'baz';

Answered By - netcoder

Answer Checked By - Candace Johnson (PHPFixing Volunteer)