1

I am trying to log the sql queries when a script is running. I am using zend framework and I already checked zend db profiler and this is not useful as this shows "?" for the values in a insert query..I need the actual values itself so that I can log it in a file. I use getAdapter()->update method for the update queries so I don' know if there is a way to get queries and log it. Please let me know if there is a way to log the queries.

regards

JDesigns
  • 2,284
  • 7
  • 25
  • 39

3 Answers3

5

From http://framework.zend.com/manual/en/zend.db.profiler.html

The return value of getLastQueryProfile() and the individual elements of getQueryProfiles() are Zend_Db_Profiler_Query objects, which provide the ability to inspect the individual queries themselves:

  • getQuery() returns the SQL text of the query. The SQL text of a prepared statement with parameters is the text at the time the query was prepared, so it contains parameter placeholders, not the values used when the statement is executed.

  • getQueryParams() returns an array of parameter values used when executing a prepared query. This includes both bound parameters and arguments to the statement's execute() method. The keys of the array are the positional (1-based) or named (string) parameter indices.

When you use Zend_Db_Profiler_Firebug it will also show you the queries on the returned pages in the Firebug console along with any bound parameters.

Community
  • 1
  • 1
Gordon
  • 312,688
  • 75
  • 539
  • 559
  • thanks @Gordon, but I am going to run it from command line, so I cannot see it in firebug..any other way? – JDesigns Sep 06 '11 at 15:22
  • @Jay yes, use `getQueryParams()` like it says in the quotation. – Gordon Sep 06 '11 at 15:23
  • I used this but it just shows Array when i run the script..but when i use getQuery(), i see the sql statements – JDesigns Sep 06 '11 at 16:03
  • sorry I should have been clear, I used print_r but I see the array with values like Array( [1]=>test [2]=>name....)..its not showing the values as in a query so that I can copy the queries..with getQuery() it shows like INSERT INTO.... – JDesigns Sep 06 '11 at 16:07
  • @Jay yes, you have to do that by hand. The bound parameters are not inserted into the query before it gets executed. This only happens inside your database system. – Gordon Sep 06 '11 at 16:11
  • thanks..so no way to get the query statements directly that I can log..thanks again – JDesigns Sep 06 '11 at 16:22
  • someone had the same question http://stackoverflow.com/questions/6712669/in-zend-how-to-print-a-mysql-query-properly, so I am not sure if there is any alternative solution for this.. – JDesigns Sep 06 '11 at 18:27
  • @Jay well, yeah. That's what I meant when I said "the bound parameters are not inserted into the query". – Gordon Sep 06 '11 at 18:53
3

I know you have got your answer though just for reference... I have traversed hundred of pages, googled a lot but i have not found any exact solution. Finally this worked for me. Irrespective where you are in either controller or model. This code worked for me every where. Just use this

//Before executing your query
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$db->getProfiler()->setEnabled(true);
$profiler = $db->getProfiler();

// Execute your any of database query here like select, update, insert
//The code below must be after query execution
$query  = $profiler->getLastQueryProfile();
$params = $query->getQueryParams();
$querystr  = $query->getQuery();

foreach ($params as $par) {
    $querystr = preg_replace('/\\?/', "'" . $par . "'", $querystr, 1);
}
echo $querystr;

Finally this thing worked for me.

Rajan Rawal
  • 6,171
  • 6
  • 40
  • 62
1

There are a few logs MySQL keeps itself.

Most notably:

The binary log (all queries)
Slow query log (queries that take longer than x time to execute)

See: http://dev.mysql.com/doc/refman/5.0/en/server-logs.html

Johan
  • 74,508
  • 24
  • 191
  • 319
  • +1 I prefer this way as well, though I don't think it would show the actual values, if prepared statements were used. – Adrian Schneider Sep 06 '11 at 16:11
  • @Adrian, the binary log holds all values of all update and insert statements. It handles prepared statements correctly and stores them in the binlog as they are eventually executed. – Johan Sep 06 '11 at 20:13