Sql debugging

Sql debugging

by Augusto C -
Number of replies: 3

Hi guys, I'm in trouble with sql debuggin

Scope:

I'd like to view "effective" sql  statements processed on report module

Attempts:

I've added $DB->set_debug(true); at the beginning of /mod/quiz/report.php and, actually, what I see is , as expected, a list of all the queries processed, BUT they appears like

SELECT * FROM mdl_quiz_feedback WHERE quizid = ? [array ( 0 => '3', )]

Is there a way to see statements AFTER params substitutions?

I've also tried to edit "query_end" function (/lib/dml/moodle_database.php) , but  even $this->last_sql contains SQL statements with placeholders.

...So

Does someone have any suggestion?

Thks in advance

Average of ratings: -
In reply to Augusto C

Re: Sql debugging

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'm not aware of any way to get the version after param substitution (I've never looked into it, but I suspect that with some DBs the substitution is done internally, rather than the Moodle code handling it).

It's pretty straightforward to just copy+paste the vars into the right places though ...

Average of ratings: -
In reply to Davo Smith

Re: Sql debugging

by Augusto C -

Yes, it's obviously straightforward to just copy+paste, but I hoped there was a faster solution as i'm in trouble with statements like the followin':

SELECT qa.slot, AVG(COALESCE(qas.fraction, 0)) AS averagefraction, COUNT(1) AS numaveraged FROM mdl_user u LEFT JOIN mdl_quiz_attempts quiza ON quiza.userid = u.id AND quiza.quiz = ? JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id AND qas.sequencenumber = ( SELECT MAX(sequencenumber) FROM mdl_question_attempt_steps WHERE questionattemptid = qa.id ) WHERE u.id IN (?,?) AND quiza.preview = 0 AND quiza.id IS NOT NULL AND qa.slot IN (?,?,?,?,?,?,?,?,?,?) AND qas.state IN (?,?,?,?,?,?,?,?) GROUP BY qa.slot ORDER BY qa.slot [array ( 0 => '3', 1 => 13402969, 2 => 13406656, 3 => 1, 4 => 2, 5 => 3, 6 => 4, 7 => 5, 8 => 6, 9 => 7, 10 => 8, 11 => 9, 12 => 10, 13 => 'gaveup', 14 => 'gradedwrong', 15 => 'gradedpartial', 16 => 'gradedright', 17 => 'mangaveup', 18 => 'mangrwrong', 19 => 'mangrpartial', 20 => 'mangrright', )]

Anyway, I can't figure why dumping sql statements splitting placeholders and their values? It's useful on writing code, but imho it's absolutlely unseful on reading.... Don't you think so?

Average of ratings: -
In reply to Augusto C

Re: Sql debugging

by Augusto C -

I came up to a solution:

for those who are interested, it could be done simply adding a few lines within the function "print_debug" defined at

/lib/dml/moodle_dataabse.php


here's the code:

/**
     * Prints sql debug info
     * @param string $sql The query which is being debugged.
     * @param array $params The query parameters. (optional)
     * @param mixed $obj The library specific object. (optional)
     * @return void
     */
    protected function print_debug($sql, array $params=null, $obj=null) {
       
        if (!$this->get_debug()) {
            return;
        }     
        if (CLI_SCRIPT) {
            echo "--------------------------------\n";
            echo $sql."\n";
            if (!is_null($params)) {
                echo "[".var_export($params, true)."]\n";
            }
            echo "--------------------------------\n";
        } else {
            echo "<hr />\n";
            echo s($sql)."\n";
            if (!is_null($params)) {
                echo "[".s(var_export($params, true))."]\n";
                /* STARTING HERE  */
                list($aki_sql, $aki_params, $aki_type) = $this->fix_sql_params($sql, $params);
                $SQLextended = $this->emulate_bound_params($aki_sql, $aki_params);
                echo s($SQLextended)."\n";
                /* ENDING HERE */
            }
            echo "<hr />\n";           
        }
    }

Average of ratings: Useful (1)