Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-5144

Bug in QuickMail plugin when used with Oracle DB

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.6.2, 2.6.3
    • Fix Version/s: None
    • Component/s: Block: Quickmail
    • Labels:
      None
    • Affected Branches:
      MOODLE_26_STABLE

      Description

      There is a bug in the QuickMail plugin when you're using it with an Oracle database. Turning the debugging level up to Developer, this is the error message that is displayed:

      Debug info: ORA-00933: SQL command not properly ended
      SELECT u.id, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname , u.email, u.mailformat, u.suspended, u.maildisplay, ue.status
      FROM m_user as u
      JOIN m_user_enrolments as ue
      ON u.id = ue.userid
      JOIN m_enrol as en
      ON en.id = ue.enrolid
      WHERE en.courseid = :o_param1
      AND ue.status = :o_param2
      ORDER BY u.lastname, u.firstname
      [array (
      'o_param1' => 2144,
      'o_param2' => 0,
      )]
      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 1087 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
          line 409 of /blocks/quickmail/lib.php: call to oci_native_moodle_database->get_recordset_sql()
          line 106 of /blocks/quickmail/email.php: call to quickmail::get_non_suspended_users()
      

      The problem is that the code in the lib.php file has the table names with "... as X", which doesn't work on Oracle. Instead, it should be just "... X"

      An example of the broken code:

              $sql = "SELECT u.id, " . $get_name_string . " , u.email, u.mailformat, u.suspended, u.maildisplay, ue.status  
                  FROM {user} as u  
                      JOIN {user_enrolments} as ue                 
                          ON u.id = ue.userid 
                      JOIN {enrol} as en
                          ON en.id = ue.enrolid                     
                      WHERE en.courseid = ?
                          AND ue.status = ?
                      ORDER BY u.lastname, u.firstname";
      

      And the code fixed:

              $sql = "SELECT u.id, " . $get_name_string . " , u.email, u.mailformat, u.suspended, u.maildisplay, ue.status  
                  FROM {user} u  
                      JOIN {user_enrolments} ue                 
                          ON u.id = ue.userid 
                      JOIN {enrol} en
                          ON en.id = ue.enrolid                     
                      WHERE en.courseid = ?
                          AND ue.status = ?
                      ORDER BY u.lastname, u.firstname";
      

        Attachments

          Activity

            People

            Assignee:
            bushido Mark Nielsen
            Reporter:
            smily03 Chris Myers
            Participants:
            Component watchers:
            Mark Nielsen
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: