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

SQL Error When used with Microsoft SQL Server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.5.8
    • Fix Version/s: None
    • Component/s: Block: Ranking
    • Labels:
      None
    • Plugin Version:
      2017050300
    • Affected Branches:
      MOODLE_35_STABLE

      Description

      There is an existing issue for this from 2015 that doesn't appear to be addressed:  https://github.com/willianmano/moodle-block_ranking/issues/2

      When Moodle is used with Microsoft SQL Server, adding a Ranking Block to a course results in a "LIMIT" error, and once that error is fixed, it is followed by a "GROUP BY" error:
      Debug info: SQLState: 42000<br>
      Error Code: 102<br>
      Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'.<br>

      SELECT
      DISTINCT u.id,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,u.username,
      sum(rl.points) as points
      FROM
      mdl_user u
      INNER JOIN mdl_role_assignments a ON a.userid = u.id
      INNER JOIN mdl_ranking_points r ON r.userid = u.id AND r.courseid = '8'
      INNER JOIN mdl_ranking_logs rl ON rl.rankingid = r.id
      INNER JOIN mdl_context c ON c.id = a.contextid
      WHERE a.contextid = '474'
      AND a.userid = u.id
      AND a.roleid = '5'
      AND c.instanceid = '8'
      AND r.courseid = '8'
      AND rl.timecreated BETWEEN '1572148800' AND '1572424334'
      GROUP BY u.id
      ORDER BY points DESC, u.firstname ASC
      LIMIT 10
      [array (
      0 => '8',
      1 => 474,
      2 => 5,
      3 => '8',
      4 => '8',
      5 => 1572148800,
      6 => 1572424334,
      )]
      Error code: dmlreadexception
      Stack trace: * line 486 of \lib\dml\moodle_database.php: dml_read_exception thrown

      • line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      • line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      • line 896 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      • line 972 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      • line 134 of \blocks\ranking\lib.php: call to sqlsrv_native_moodle_database->get_records_sql()
      • line 85 of \blocks\ranking\block_ranking.php: call to block_ranking_get_students_by_date()
      • line 288 of \blocks\moodleblock.class.php: call to block_ranking->get_content()
      • line 230 of \blocks\moodleblock.class.php: call to block_base->formatted_contents()
      • line 1216 of \lib\blocklib.php: call to block_base->get_content_for_output()
      • line 1268 of \lib\blocklib.php: call to block_manager->create_block_contents()
      • line 374 of \lib\blocklib.php: call to block_manager->ensure_content_created()
      • line 3954 of \lib\outputrenderers.php: call to block_manager->region_has_content()
      • line 41 of \theme\eguru\layout\columns2.php: call to core_renderer->blocks()
      • line 1291 of \lib\outputrenderers.php: call to include()
      • line 1221 of \lib\outputrenderers.php: call to core_renderer->render_page_layout()
      • line 244 of \course\view.php: call to core_renderer->header()

       

        Attachments

          Activity

            People

            Assignee:
            willianmano Willian Mano Araujo
            Reporter:
            mhenry79 Mike Henry
            Participants:
            Component watchers:
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: