You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Rob Johnson -
Number of replies: 16

I have recently upgraded my school districts production site from 1.9.7 to the latest 2.1.  I have upgraded the questionnaire module as well.  The tables set up correctly, and old questionnaires are working fine except those set to separate groups.  These show the error seen in the image below.

debug image

I have searched these forums and found similar error messages, but not with the "near 'INT))=GM.userid" portion.  I would really appreciate some help in sorting this out.  Thanks.

Rob

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Thanks for reporting this bug, Rob. I confirm I have the bug on my 2.1 moodle test site. I suppose the bug comes from the fix introduced in CONTRIB-2797.

I hope Mike Churchward will come up with a fix any time soon.

Joseph

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Rob Johnson -

Thank you for clearing up the cause of this error message.  I was thinking it was an issue from the upgrade.  I will test any fix Mike comes up with.  Questionnaire is a great addition to Moodle. smile

Rob

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

In the meantime, and if you do need the "groups" feature to work, you might consider reverting to this previous version of the questionnaire/locallib.php file (v 1.144) from contrib here. 

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Rob Johnson -

Thank you for the suggestion.  I replaced the locallib.php file, but still get an almost identical error.  Please see below.  Here is California, we still have a few weeks before school begins.  I can be paitient and wait for Mike to find a fix. smile

debug image

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

sorry, Rob, I now realize that the "Postgress" bug fixes done by Mike 2 months ago apply to several other Questionnaire files, not just to locallib.php.

I think it would be wiser to wait for Mike to tackle the issue in the tracker...

Joseph 

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Rob Johnson -

Hi Joseph,

Would it be possible for me to try reverting the "other Questionnaire files" you mention in your above post?  One of my bosses wants to see some data from last school year.  I tried browsing source code on the mods and plugins entry, but I get a 404 error.  I would appreciate if you would provide links.

Thanks,

Rob

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Jane Boyden -

Just for a little more background information about when this error is occurring:

  1. The course (and activity) is set up with separate groups.

  2. Individual users are each added to a group and have no problems viewing and responding to the questions in the questionnaire.

  3. The course teacher is NOT added to any of the groups.

  4. The course teacher, when first clicking the questionnaire activity, sees a tab indicating there are 6 results to view.




    Questionnaire Choice

  5. Clicking that tab generates the error, along with the message saying, "Sorry, you must be member of at least one group that is used in this activity..

  6. As a site administrator - I also receive the same message and am unable to see any responses to the questionnaire.

Here's the debugging message:

Debug info: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT))=GM.userid
ORDER BY R.id' at line 7
SELECT R.id, R.survey_id
FROM mdl_questionnaire_response R,
mdl_groups_members GM
WHERE R.survey_id='1' AND
R.complete='y' AND
GM.groupid=0 AND
(CAST (R.username as INT))=GM.userid
ORDER BY R.id
[array (
)]
Stack trace:
  • line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
  • line 794 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 2272 of \mod\questionnaire\locallib.php: call to mysqli_native_moodle_database->get_records_sql()
  • line 626 of \mod\questionnaire\report.php: call to questionnaire->survey_results()
Average of ratings: -
In reply to Jane Boyden

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Thanks, Jane, for confirming the bug. As I have already reported in CONTRIB-2797 the bug is due to the fixing of another bug. We have to wait for Mike Churchward to revise the changes he made about 2 months ago and come up with a solution that works for people using mySQL database.

Joseph

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Pat Mulrooney -

I submitted a fix for mysql, oracle, and mssql (have no way of testing oracle and mssql though) here: http://tracker.moodle.org/browse/CONTRIB-3136

 

 

 

Average of ratings: -
In reply to Pat Mulrooney

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Rob Johnson -

Thank you for posting this, but when I changed the code you specified, I get a blank page accross my site.  Any ideas?  I was unable to turn on debugging to troubleshoot.

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Pat Mulrooney -

Did you see the follow post to my approach? Using the already available "$DB->sql_cast_char2int("R.username")" is a better approach then creating the function in lib.php that does the same thing.

Also make sure you did not miss any semi-colons.

Average of ratings: -
In reply to Rob Johnson

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Rob,

A new fix for the bug has been committed to CVS (HEAD). I have tested it for MySQL and it works fine.

The updated files are available from the CONTRIB repository and the new ZIP archive should be available as usual some time tomorrow.

Joseph

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Jenny Gray -

I have now committed the fix to the Moodle_20_stable branch in CVS so you can either download from there, or wait for the 2.0 zip archive tomorrow.

Average of ratings: -
In reply to Jenny Gray

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Thanks Jenny, and... what about the MOODLE_21_STABLE branch ?

Joseph

Average of ratings: -
In reply to Joseph Rézeau

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Jenny Gray -

Ah - I didn't know there was one!  I'll do that as well now.

Average of ratings: -
In reply to Jenny Gray

Re: You have an error in your SQL syntax...viewing questionnaire submissions set to separate groups

by Joseph Rézeau -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Thanks, Jenny.wink

Joseph

Average of ratings: -