Moodle SQL queries problem

Moodle SQL queries problem

by Italo Marques -
Number of replies: 3

Hello everyone.

I've been working with Moodle for a while now but this is my first here so please forgive my lack of knowledge. =)

I maintain two Moodle sites, one on Moodle 1.9.7 and the other on Moodle 2.6.10 and both have Ad-hoc Contributed Reports installed.

I've been trying out these queries but just found out something very weird. While most of those work just fine on the older site, any query containing something like %%wwwroot%% (e.g. Courses without Teachers) returns the following error message on the newer site:

"Error executing the query : Error: Incorrect number of query parameters. Expected 1 , got 0 ."

Am I doing something wrong?

Average of ratings: -
In reply to Italo Marques

Re: Moodle SQL queries problem

by Italo Marques -

Hmmm...it turns out the problem wasn't the token but the '?' inside the CONCAT() string and replacing it with CHAR(63) did the magic,

This:
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,' - ',c.fullname,'</a>')

Becomes:
concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,' - ',c.fullname,'</a>')

Still, isn't there anyway of making it work without having to replace the question mark? It's very weird that it works on Moodle 1.9 but not on 2.6

Average of ratings: -
In reply to Italo Marques

Re: Moodle SQL queries problem

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

It is not surprising if you realise that the Moodle database API changed in Moodle 2.0 to use placeholders to insert values into queries, and ? is the placeholder symbol.

Average of ratings: Useful (2)
In reply to Tim Hunt

Re: Moodle SQL queries problem

by Italo Marques -

I see. I'm pretty much used to Moodle 1.9 and still getting used to 2.6 but all this database thing is very new and confusing to me, I know only a little bit of SQL but Ad-Hoc Database Queries has helped me quite a LOT, you're the one behind it am I right? Thanks very much...the older site has hundreds of courses and thousands of activities, having to update the quizzes' end dates is so much easier with a simple UPDATE query.

Average of ratings: -