Ad-hoc database queries

LIMIT is supported - just not in the SQL. On the form where you edit the query, there is a separate input box where you can enter the limit you want.
(It is like this, becuase the plugin is based on Moodle's general Database library, and that is how that works.)

I'm wondering why I can't seem to make any report with the LIMIT clause work and I see that there are several contributed reports that use this. We are using:
Moodle Version: 4.1.1 (2022112801)
Release: 01.16.2023
PHP: 7.4.33
MySQL: 5.7.33-0ubuntu0.16.04.1

I don't think this problem is specific to this plugin. I think it is a general scheduled tasks problem, which just happens to be affecting this task, so try asking in an appropriate forum. You probably just need to manually clear the lock, or something.

I tried to run the scheduled task \report_customsql\task\run_reports
but get the message "Cannot obtain task lock" Does anyone else get this when trying to run this on demand? The task seems to not have run since September 2022. It happens both on our production (Moodle 3.9) and our development server (Moodle 4.1) and with plugin versions 2021070700 and 2022031800.

At the moment, this is basically a tool by admins for admins.
Ability to share selected queries with teachers would be a great feature, but we don't have any immediate plans to develop it ourselves. If someone else can develop this in a good, we will would be delighted to get a pull request on github.

Hi. thanks for this plugin, is very usefully. I would like to know if is possible to schedule the queries and send email to teacher, not only the administrator. Thanks

Hi Everybody,
I created a report with a provided query. When i use this on demand it works fine. When I want to mail the results nothing happens.
I can see that the CRON task has run
Ad-hoc database queries: run scheduled reports task \report_customsql\task\run_reports Scheduled 21/07/22, 09:28 0.09 secs cron04 1626851 3 reads 0 writes Success
but the report did not
aa This query was last run on woensdag, 20 juli 2022, 16:18 . It took 0.005s to run. Available to Only administrators (moodle/site:config).
What do I do wrong
I'm working on a MoodleWorkplace 3.11.6 (build: 20220314)
But also LMS 3.11.8 (build: 20220711)

Bingo! Thank you again!
(I thought I need some webservice permission, but it was a report access problem)

When you create a report, you choose one of three capabilities that are required to access the report. You need whichever permission you selected there.

Thank you Tim! This is very useful.
My next problem, when I try to call the service:
{"error":"Sorry, but you do not currently have permissions to do that (Change site configuration).","errorcode":"nopermissions","stacktrace":null,"debuginfo":null,"reproductionlink":null}
Which permissions ("Webservice function") do I need?
I have a webservice test user with several webservice functions and some other REST calls was successful.

Have you seen https://github.com/moodleou/moodle-report_customsql/blob/main/internaldoc/functionality.txt#L71 ?

Did anyone try to fetch query results via web service/pluginfile.php?
Can I find any documentation or examples about this feature?

Hi Sue, if your course shortnames don't follow a patter that allows you to use %keyword% you could try (depending on the length of the list for manageability) WHERE course.shortname IN('courseName1', 'CourseName2') etc etc listing each course shortname in ' ' with a comma seperator. Alternatively, if your courses are organised in categories, you could create a report for a category or allowing the user running the report to enter a category name as a parameter.

Thank you, I’ve made a few attempts at the SQL and was able to get one course but not multiple. If I use WHERE course.shortname LIKE '%2022%' can I add multiple courses with a comma between? Or am I limited to one course?

I am sure you can do that if you work out the right SQL. In the report, try adding something like
WHERE course.shortname LIKE '%2022%'
(to work out the exact details, you will meed to know SQL, and which courses you want the report to include.)