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

Question

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?

score 5 · Accepted Answer · answered Jun 08 '11 at 19:54

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';

Oh, I see... I think I'd heard about that long before, but I'd forgotten it. Is it any different with the Pear MDB2 `autoExecute()` (not `autoPrepare()`) method? The documentation ( http://pear.php.net/manual/en/package.database.mdb2.intro-auto.php ) seems to suggest that the query will contain the columns values rather than placeholders. — Mr. Lance E Sloan, Jun 10 '11 at 13:15
@L S: From what I can see in the code, `autoExecute` calls `autoPrepare`, so there wouldn't be any difference. — netcoder, Jun 10 '11 at 13:22

score 0 · Answer 2 · edited Jul 24 '13 at 09:20

0

print_r ($db->last_query, true);

edited Jul 24 '13 at 09:20

Rohit Vipin Mathews

11,629
15
57
112

answered Jul 24 '13 at 09:02

piotr

1

Thanks for the suggestion, but doesn't this work only ***after*** the call to `$stmt->execute()` has completed? I need to access the completed SQL statement ***before*** that. – Mr. Lance E Sloan Jul 24 '13 at 13:07

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

2 Answers2