Exception when viewing engagement analytics for a course (with an Oracle database)

[javeedchida]

Stack trace:

Debug info: ORA-00933: SQL command not properly ended

SELECT rawdata
FROM m_engagement_cache
WHERE indicator = :o_param1
AND courseid = :o_param2
AND timestart = :o_param3
AND timemodified > :o_param4
ORDER BY timemodified DESC
LIMIT 1
[array (
'o_param1' => 'assessment',
'o_param2' => '4',
'o_param3' => '1423465200',
'o_param4' => 1424800396,
)]
Error code: dmlreadexception
Stack trace:
line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
line 1127 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
line 1476 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
line 1061 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->get_record_sql()
line 1549 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_record_sql()
line 156 of /mod/engagement/indicator/indicator.class.php: call to moodle_database->get_field_sql()
line 132 of /mod/engagement/indicator/indicator.class.php: call to indicator->get_cache()
line 111 of /mod/engagement/indicator/indicator.class.php: call to indicator->get_risk_for_users()
line 74 of /report/engagement/lib.php: call to indicator->get_course_risks()
line 85 of /blocks/engagement/block_engagement.php: call to report_engagement_get_course_summary()
line 294 of /blocks/moodleblock.class.php: call to block_engagement->get_content()
line 236 of /blocks/moodleblock.class.php: call to block_base->formatted_contents()
line 993 of /lib/blocklib.php: call to block_base->get_content_for_output()
line 1045 of /lib/blocklib.php: call to block_manager->create_block_contents()
line 361 of /lib/outputrenderers.php: call to block_manager->ensure_content_created()
line 45 of /theme/clean/layout/columns3.php: call to core_renderer->standard_head_html()
line 877 of /lib/outputrenderers.php: call to include()
line 807 of /lib/outputrenderers.php: call to core_renderer->render_page_layout()
line 245 of /course/view.php: call to core_renderer->header()

The problem & a dirty fix

It turns out the LIMIT keyword is not recognized for Oracle. My current (reactionary) fix was to replace the call to get_field_sql() with the below if-else construct.

    $params = array($this->get_name(), $this->courseid, $this->startdate, time() - $this->cachettl);
    if($CFG->dbtype == 'oci') {
        $rawdata = $DB->get_field_sql('
                    SELECT rawdata FROM(
                      SELECT      rawdata
                      FROM        {engagement_cache}
                      WHERE       indicator = ?
                        AND       courseid = ?
                        AND       timestart = ?
                        AND       timemodified > ?
                      ORDER BY    timemodified DESC
                    ) WHERE ROWNUM = 1', $params);
    } else {
        $rawdata = $DB->get_field_sql('
                    SELECT      rawdata
                      FROM        {engagement_cache}
                      WHERE       indicator = ?
                        AND       courseid = ?
                        AND       timestart = ?
                        AND       timemodified > ?
                      ORDER BY    timemodified DESC
                    LIMIT 1', $params);
    }

Pull request with dirty fix is here

Switching get_field_sql() with get_records_select()

I had trouble following Adam's suggestion to move from get_field_sql() to get_records_select(). This is what I tried.

$params = array($this->get_name(), $this->courseid, $this->startdate, time() - $this->cachettl);
$select = 'indicator = ? AND courseid = ? AND timestart = ? AND timemodified > ?';
$rawdata = DB->get_records_select('engagement_cache', $select, $params, 'timemodified DESC', $fields='rawdata', $limitfrom=0, $limitnum=1);

The above caused a blank page to load with nothing reported in the error log. The access log had:

GET /course/view.php?id=4 HTTP/1.1" 200 62737

What am I missing? Thanks.

[aolley]

Hi Javeed,

What you need to do with the get_records_select, is set $rawdata to the first element of the returned array (or false).

$DB->get_field_sql will return the data for that one field directly.
$DB->get_records_sql will return an array of records - in this case you've limited it to one item, but it'll still be in an array.

So you'd want something like

$rawdata = false;
$records = $DB->get_records_select(.....);
if (!empty($records)) {
$rawdata = current($records)->rawdata;
}

[javeedchida]

Hi Adam. Thanks for that. I made that change and from my testing, it seems to work flawlessly, at least for the scenarios that were failing previously.

I committed the changes and created this pull request for your review: #7

Many thanks for your timely response.