current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
thanks @Gordon, but I am going to run it from command line, so I cannot see it in firebug..any other way? –  Jay Sep 6 '11 at 15:22
    
@Jay yes, use getQueryParams() like it says in the quotation. –  Gordon♦ Sep 6 '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 –  Jay Sep 6 '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.... –  Jay Sep 6 '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 6 '11 at 16:11
show 3 more comments
up vote 2 down vote

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.

share|improve this answer
add comment
up vote 1 down vote

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

share|improve this answer
    
+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 6 '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 6 '11 at 20:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.