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

CAST(R.username as int) causes mysql/mssql/oracle issues when viewing activity report

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.3
    • Fix Version/s: 2.0.4
    • Component/s: Module: Questionnaire
    • Labels:
      None
    • Database:
      Any
    • Workaround:
      Hide

      Need to incorporate a database type check when doing CAST conversions.

      Using this as a model (http://tracker.moodle.org/secure/attachment/12783/MDL-12893.patch.txt):

      !! I am not certain the mssql and oracle ones will work !!

      Add to the bottom of questionnaire/lib.php


      function questionnaire_get_sql_cast($fieldname) {

      global $DB;

      switch ($DB->get_dbfamily())

      { case 'mysql': return 'CAST('.$fieldname.' AS signed)'; case 'postgres': return 'CAST('.$fieldname.' AS INT)'; case 'mssql': return 'CAST('.$fieldname.' AS bigint)'; case 'oracle': return 'TO_NUMBER('.$fieldname.')'; }

      }


      Then find all instances of '(CAST(R.username as int))' and replace it with .questionnaire_get_sql_cast("R.username").

      For example in questionnaire/choose_group_form.php lines 56-61 goes from:


      $sql = "SELECT R.id, GM.id as groupid
      FROM ".$CFG->prefix."questionnaire_response R, ".$CFG->prefix."groups_members GM
      WHERE R.survey_id=".$sid." AND
      R.complete='y' AND
      GM.groupid=".$group->id." AND
      (cast (R.username as int)) =GM.userid";


      to


      $sql = "SELECT R.id, GM.id as groupid
      FROM ".$CFG->prefix."questionnaire_response R, ".$CFG->prefix."groups_members GM
      WHERE R.survey_id=".$sid." AND
      R.complete='y' AND
      GM.groupid=".$group->id." AND
      ".questionnaire_get_sql_cast("R.username")."=GM.userid";


      You also need to change it on lines 2252, 2260, 2269, 2606, 2614, 2623 of questionnaire/locallib.php and lines 161, 174, 196, 426, 669 of questionnaire/report.php

      Show
      Need to incorporate a database type check when doing CAST conversions. Using this as a model ( http://tracker.moodle.org/secure/attachment/12783/MDL-12893.patch.txt): !! I am not certain the mssql and oracle ones will work !! Add to the bottom of questionnaire/lib.php function questionnaire_get_sql_cast($fieldname) { global $DB; switch ($DB->get_dbfamily()) { case 'mysql': return 'CAST('.$fieldname.' AS signed)'; case 'postgres': return 'CAST('.$fieldname.' AS INT)'; case 'mssql': return 'CAST('.$fieldname.' AS bigint)'; case 'oracle': return 'TO_NUMBER('.$fieldname.')'; } } Then find all instances of '(CAST(R.username as int))' and replace it with .questionnaire_get_sql_cast("R.username"). For example in questionnaire/choose_group_form.php lines 56-61 goes from: $sql = "SELECT R.id, GM.id as groupid FROM ".$CFG->prefix."questionnaire_response R, ".$CFG->prefix."groups_members GM WHERE R.survey_id=".$sid." AND R.complete='y' AND GM.groupid=".$group->id." AND (cast (R.username as int)) =GM.userid"; to $sql = "SELECT R.id, GM.id as groupid FROM ".$CFG->prefix."questionnaire_response R, ".$CFG->prefix."groups_members GM WHERE R.survey_id=".$sid." AND R.complete='y' AND GM.groupid=".$group->id." AND ".questionnaire_get_sql_cast("R.username")."=GM.userid"; You also need to change it on lines 2252, 2260, 2269, 2606, 2614, 2623 of questionnaire/locallib.php and lines 161, 174, 196, 426, 669 of questionnaire/report.php
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Attachments

        Issue Links

          Activity

            People

            Assignee:
            jenny-gray Jenny Gray
            Reporter:
            mulroony Patrick Mulrooney
            Participants:
            Component watchers:
            Mike Churchward
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              1/Aug/11