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

MS SQL cannot return Boolean data type in query results, any code using this returns no results when using Sql 2005

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 1.7.1
    • 1.7.3, 1.8.3, 1.9
    • Assignment (2.2)
    • None
    • Windows 2003, Php 5.12, Sql 2005
    • Microsoft SQL
    • MOODLE_17_STABLE
    • MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE

    Description

      line 1095 in lib.php in assignments is a good example:
      $select = 'SELECT u.id, u.id, u.firstname, u.lastname, u.picture, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked, ((s.timemarked > 0) AND (s.timemarked >= s.timemodified)) AS status ';

      returns no records for sql 2005
      if changed to the following it works:

      $select = 'SELECT u.id, u.id, u.firstname, u.lastname, u.picture, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked, ';
      if ($CFG->dbtype == 'mssql' || $CFG->dbtype == 'odbc_mssql' || $CFG->dbtype == 'mssql_n')

      { $select .= ' Case When ((s.timemarked > 0) AND (s.timemarked >= s.timemodified)) THEN \'True\' Else \'False\' End AS status '; }

      else

      { $select .=' ((s.timemarked > 0) AND (s.timemarked >= s.timemodified)) AS status '; }

      Attachments

        Issue Links

          Activity

            People

              stronk7 Eloy Lafuente (stronk7)
              tommcmur Tom McMurtry
              Nobody Nobody
              Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/Oct/07