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

SQL error in assignments module with Oracle Database I

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.1, 2.4
    • Fix Version/s: 2.3.3
    • Component/s: Assignment
    • Labels:

      Description

      We start using moodle 2.3.1 on a new install (not upgrading) with Oracle 10.2 database.

      We found two erros in SQL statements in assignments modules.

      1 - After creating a assignment, when teacher click in the link to see the works sent by students the application display a error about the SQL statement (with params replaced):

      SELECT a.id       AS assignment,
        a.nosubmissions AS offline,
        g.timemodified  AS timemarked,
        g.grader        AS grader,
        g.grade         AS grade,
        s.status        AS status
      FROM mdassign a
      LEFT JOIN mdassign_grades g
      ON g.assignment = a.id
      AND g.userid    = '3'
      LEFT JOIN mdassign_submission s
      ON s.assignment = a.id
      AND s.userid    = '3'
      AND a.id        = '23'

      Looking at the sentence we found that the word "offline" is a reserved word in Oracle environment.

      Solution (for now):
      We replaced the word offline with offlinex in the file <moodlehome>/mod/assign/lib.php in then sentence:

      // get all user submissions, indexed by assignment id
      $mysubmissions = $DB->get_records_sql("SELECT a.id AS assignment, a.nosubmissions AS offline, g.timemodified AS timemarked, g.grader AS grader, g.grade AS grade, s.status AS status
                                  FROM {assign} a LEFT JOIN {assign_grades} g ON g.assignment = a.id AND g.userid = ? LEFT JOIN {assign_submission} s ON s.assignment = a.id AND s.userid = ?
                                  AND a.id $sqlassignmentids", array_merge(array($USER->id, $USER->id), $assignmentidparams));

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  0 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    12/Nov/12