Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-46903

Database error on SCORM Objectives report

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.6.4, 2.7.1, 3.0.6
    • Fix Version/s: 3.0.7, 3.1.3
    • Component/s: SCORM
    • Labels:
    • Testing Instructions:
      Hide

      Requires multi-database testing - specifically Oracle.

      Use a SCORM package that reports objective data. To make testing easier I have added better support for the public SCORM 2004 package available from: http://scorm.com/wp-content/assets/golf_examples/PIFS/RunTimeAdvancedCalls_SCORM20043rdEdition.zip
      NOTE: This is a SCORM 2004 package and other errors may occur as we do not completely support SCORM 2004

      Enter the SCORM package and complete the SCORM package to generate the objective related data. (the exit button in the actual content may not work but the exit activity link in top right should work fine.)

      View the SCORM reports page - select Objective report and check to make sure no errors appear.

      Show
      Requires multi-database testing - specifically Oracle. Use a SCORM package that reports objective data. To make testing easier I have added better support for the public SCORM 2004 package available from: http://scorm.com/wp-content/assets/golf_examples/PIFS/RunTimeAdvancedCalls_SCORM20043rdEdition.zip NOTE: This is a SCORM 2004 package and other errors may occur as we do not completely support SCORM 2004 Enter the SCORM package and complete the SCORM package to generate the objective related data. (the exit button in the actual content may not work but the exit activity link in top right should work fine.) View the SCORM reports page - select Objective report and check to make sure no errors appear.
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_30_STABLE, MOODLE_31_STABLE
    • Pull Master Branch:
      master_MDL-46903

      Description

      Replication steps:

      1. Log in as teacher/admin
      2. Navigate to a course
      3. Create a new SCORM activity (any package, default settings) or use an existing one
      4. View the SCORM activity
      5. Go to Reports
      6. Go to Objectives

      Using Oracle, I came across the following database error.

      Error reading from database
       
      More information about this error
      Debug info: ORA-00932: inconsistent datatypes: expected - got CLOB
      SELECT DISTINCT value, scoid FROM a_scorm_scoes_track WHERE scormid = :o_param1 AND LOWER(element) LIKE LOWER(:o_param2) ESCAPE '\' ORDER BY value
      [array (
      'o_param1' => '1',
      'o_param2' => 'cmi.objectives_%.id',
      )]
      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 1092 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
          line 1199 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_recordset_sql()
          line 652 of \mod\scorm\report\objectives\classes\report.php: call to moodle_database->get_recordset_select()
          line 193 of \mod\scorm\report\objectives\classes\report.php: call to scormreport_objectives\get_scorm_objectives()
          line 97 of \mod\scorm\report.php: call to scormreport_objectives\report->display()
      

      The problem comes about because the "value" field is a long text field (a CLOB in Oracle terms) and this cannot be used for ordering or for testing if a result is distinct. The offending query is...

      mod/scorm/report/objectives/classes/report.php, line 652

      $rs = $DB->get_recordset_select("scorm_scoes_track", $select, $params, 'value', 'DISTINCT value, scoid');
      

      This line has been as it is since the inception of the report in 2.6. The function goes on to form the record set into an array, which removes the need for distinct results, and it also resorts the values on the scoid value, so ordering by value is not necessary.

      The statement can therefore be changed to the following to gain the same results.

      $rs = $DB->get_recordset_select("scorm_scoes_track", $select, $params, 'scoid', 'value, scoid');
      

      However, if there is a large amount of duplication, shifting the filtering for distinct results from the DB to code may be unwise.

        Attachments

          Activity

            People

            • Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                14/Nov/16