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

quickfindlist block throws sql error after adding to mymoodle page

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: None
    • Component/s: Block: Quick find list
    • Labels:
      None
    • Affected Branches:
      MOODLE_22_STABLE

      Description

      Hi,

      I have added quickfindlist as a student user to mymoodle page. After doing this, mymoodle page stays white without any output.

      Apache log gives this error:
      [Tue Apr 03 10:06:22 2012] [error] Default exception handler: Fehler beim Lesen der Datenbank Debug: ERROR: column "quickfindlist" does not exist at character 120\nSELECT * FROM mdl_block AS b\n JOIN mdl_block_instances AS bi ON b.name = blockname WHERE name = "quickfindlist"\n AND pagetypepattern = "$1"\n AND parentcontextid = $2\n AND bi.id < $3\n[array (\n 0 => 'my-index',\n 1 => '5406',\n 2 => '1398',\n)]\n* line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown\n* line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()\n* line 678 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()\n* line 74 of /blocks/quickfindlist/block_quickfindlist.php: call to pgsql_native_moodle_database->get_records_sql()\n* line 280 of /blocks/moodleblock.class.php: call to block_quickfindlist->get_content()\n* line 232 of /blocks/moodleblock.class.php: call to block_base->formatted_contents()\n* line 926 of /lib/blocklib.php: call to block_base->get_content_for_output()\n* line 978 of /lib/blocklib.php: call to block_manager->create_block_contents()\n* line 349 of /lib/blocklib.php: call to block_manager->ensure_content_created()\n* line 8 of /theme/uni/layout/general.php: call to block_manager->region_has_content()\n* line 685 of /lib/outputrenderers.php: call to include()\n* line 637 of /lib/outputrenderers.php: call to core_renderer->render_page_layout()\n* line 146 of /my/index.php: call to core_renderer->header()\n, referer: https://[...]/my/index.php

      After removing the sql query

      $select = 'SELECT * ';
      $from = 'FROM

      {block} AS b
      JOIN {block_instances} AS bi ON b.name = blockname ';
      $where = 'WHERE name = "quickfindlist"
      AND pagetypepattern = "?"
      AND parentcontextid = ?
      AND bi.id < ?';


      from /blocks/quickfindlist/block_quickfindlist.php, mymoodle page reappears.

      When visiting block settings page after that, there is a similar error. After removing sql query


      $select = 'SELECT * ';
      $from = 'FROM {block}

      AS b
      JOIN

      {block_instances}

      AS bi ON b.name = blockname ';
      $where = 'WHERE name = "quickfindlist"
      AND pagetypepattern = "?"
      AND parentcontextid = ?
      AND bi.id < ?';

      from /blocks/quickfindlist/edit_form.php, the settings page appears (but is slightly broken of course).

      Summing up, there seems to be some sort of start-up problem with the block.

      PS: I'm using PostgreSQL

      Thanks in advance
      Alex

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            mike1989 Mike Worth added a comment -

            There is obviously some problem with the where clause: WHERE name = "quickfindlist". The double quotes are not acting like they do in MySQL. I don't really have any experience of PostgreSQL, so can't comment further on that.

            I wrote this block using explicit MySQL so it can't in general be trusted on other databases; I think this can only really be solved by re-writing the query parts either for each database engine or using an abstraction layer library (I've no idea what they're like now, but for some reason wouldn't do this nicely when I worte the block)

            Show
            mike1989 Mike Worth added a comment - There is obviously some problem with the where clause: WHERE name = "quickfindlist". The double quotes are not acting like they do in MySQL. I don't really have any experience of PostgreSQL, so can't comment further on that. I wrote this block using explicit MySQL so it can't in general be trusted on other databases; I think this can only really be solved by re-writing the query parts either for each database engine or using an abstraction layer library (I've no idea what they're like now, but for some reason wouldn't do this nicely when I worte the block)
            Hide
            abias Alexander Bias added a comment -

            As far as I have understood from some google searching, double quotes in PostgreSQL are related to case-sensitivity of identifiers. Unfortunately, I couldn't change your SQL query so that it works on PostgreSQL, there must be a problem with these quotes which I don't understand.

            Nevertheless, looking at your query

            $select = 'SELECT * ';
            $from = 'FROM

            Unknown macro: {block}

            AS b
            JOIN

            Unknown macro: {block_instances}

            AS bi ON b.name = blockname ';
            $where = 'WHERE name = "quickfindlist\"
            AND pagetypepattern = ?
            AND parentcontextid = ?
            AND bi.id < ?';*/
            $params = array(
            $this->instance->pagetypepattern,
            $this->instance->parentcontextid,
            $this->instance->id
            );

            I think that you don't need the JOIN, do you? If not, this could be implemented using the moodle data manipulation API (http://docs.moodle.org/dev/Data_manipulation_API#moodle_database::get_records.28.29), changing the database call to something like:

            if ($thispageqflblocks = $DB->get_records('block_instances', array('blockname' => 'quickfindlist', 'pagetypepattern' => $this->instance->pagetypepattern, 'parentcontextid' => $this->instance->parentcontextid, 'id' => $this->instance->id))) {

            What do you think of this?

            Show
            abias Alexander Bias added a comment - As far as I have understood from some google searching, double quotes in PostgreSQL are related to case-sensitivity of identifiers. Unfortunately, I couldn't change your SQL query so that it works on PostgreSQL, there must be a problem with these quotes which I don't understand. Nevertheless, looking at your query $select = 'SELECT * '; $from = 'FROM Unknown macro: {block} AS b JOIN Unknown macro: {block_instances} AS bi ON b.name = blockname '; $where = 'WHERE name = "quickfindlist\" AND pagetypepattern = ? AND parentcontextid = ? AND bi.id < ?';*/ $params = array( $this->instance->pagetypepattern, $this->instance->parentcontextid, $this->instance->id ); I think that you don't need the JOIN, do you? If not, this could be implemented using the moodle data manipulation API ( http://docs.moodle.org/dev/Data_manipulation_API#moodle_database::get_records.28.29 ), changing the database call to something like: if ($thispageqflblocks = $DB->get_records('block_instances', array('blockname' => 'quickfindlist', 'pagetypepattern' => $this->instance->pagetypepattern, 'parentcontextid' => $this->instance->parentcontextid, 'id' => $this->instance->id))) { What do you think of this?
            Hide
            raymor Ray Morris added a comment -

            The quoting has been fixed in the plugin. The fix is available at
            https://github.com/MorrisR2/moodle-block_quickfindlist
            or
            https://github.com/marxjohnson/moodle-block_quickfindlist
            and a new ZIP file will be released soon

            Show
            raymor Ray Morris added a comment - The quoting has been fixed in the plugin. The fix is available at https://github.com/MorrisR2/moodle-block_quickfindlist or https://github.com/marxjohnson/moodle-block_quickfindlist and a new ZIP file will be released soon
            Hide
            abias Alexander Bias added a comment -

            I am closing this feature request which has been reported by me as fixed as the developer has stated that it is fixed.

            Show
            abias Alexander Bias added a comment - I am closing this feature request which has been reported by me as fixed as the developer has stated that it is fixed.

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: