Does not work with MS-SQL database!

[amit]

We are trying to use this plugin with Microsoft SQL server as the moodle database which is officially supported using PDO. However this plugin does not work with SQL Server backend as it seems to use database specific SQL. (LIMIT is not supported by MS SQL!)

Here is an example of the raw SQL used:

Is there any chance of making the SQL generic, so other database engines like MS SQL can use this plugin?

[vtos]

HI @amit ,

Thank you for reporting this. To be honest, the plugin wasn't tested under the MS SQL db engine, at least since my adoption of it. I think the query (and possibly more of them) can be adjusted to respect the MS SQL db engine. I'll set up the necessary environment and explore the issue. Though I'm quite optimistic about fixing it with the next plugin version :slightly_smiling_face:

[vtos]

When looking into the issue another incompatibility was revealed - when starting an attempt as the student role:

Debug info: SQLState: 42000<br>
Error Code: 8120<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'm_tag.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.<br>

SELECT t.id, SUBSTRING(t.name, CAST(6 AS INT) , 2^31-1) AS level
FROM m_tag t
JOIN m_tag_instance ti ON t.id = ti.tagid AND ti.itemtype = N'question'
WHERE t.name IN (N'adpq_1',N'adpq_2',N'adpq_3',N'adpq_4',N'adpq_5',N'adpq_6',N'adpq_7',N'adpq_8',N'adpq_9',N'adpq_10',N'adpq_11',N'adpq_12',N'adpq_13',N'adpq_14',N'adpq_15')
GROUP BY t.id
[array (
0 => 'question',
1 => 'adpq_1',
2 => 'adpq_2',
3 => 'adpq_3',
4 => 'adpq_4',
5 => 'adpq_5',
6 => 'adpq_6',
7 => 'adpq_7',
8 => 'adpq_8',
9 => 'adpq_9',
10 => 'adpq_10',
11 => 'adpq_11',
12 => 'adpq_12',
13 => 'adpq_13',
14 => 'adpq_14',
15 => 'adpq_15',
)]
Error code: dmlreadexception

Stack trace:

line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 331 of /lib/dml/sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
line 438 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
line 907 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
line 983 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
line 50 of /mod/adaptivequiz/classes/local/repository/tags_repository.php: call to sqlsrv_native_moodle_database->get_records_sql()
line 388 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\repository\tags_repository::get_question_level_to_tag_id_mapping_by_tag_names()
line 263 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\fetchquestion->retrieve_all_tag_ids()
line 299 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\fetchquestion->initalize_tags_with_quest_count()
line 594 of /mod/adaptivequiz/classes/local/attempt.php: call to mod_adaptivequiz\local\fetchquestion->fetch_questions()
line 339 of /mod/adaptivequiz/classes/local/attempt.php: call to mod_adaptivequiz\local\attempt->get_question_ready()
line 80 of /mod/adaptivequiz/classes/local/itemadministration/default_item_administration.php: call to mod_adaptivequiz\local\attempt->start_attempt()
line 79 of /mod/adaptivequiz/classes/cat_session.php: call to mod_adaptivequiz\local\itemadministration\default_item_administration->evaluate_ability_to_administer_next_item()
line 137 of /mod/adaptivequiz/attempt.php: call to mod_adaptivequiz\cat_session::run_item_administration()

[vtos]

Another one is

Debug info: SQLState: 42000<br>
Error Code: 8155<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]No column name was specified for column 2 of 'questionlatestversion'.<br>

SELECT SUBSTRING(t.name, CAST(6 AS INT) , 2^31-1) AS difficultylevel, COUNT(*) AS questionsnumber
FROM m_tag t
JOIN m_tag_instance ti ON t.id = ti.tagid
JOIN m_question q ON q.id = ti.itemid
JOIN m_question_versions qv ON qv.questionid = q.id
JOIN (
SELECT questionbankentryid, MAX(version)
FROM m_question_versions
WHERE status = N'ready'
GROUP BY questionbankentryid
) questionlatestversion ON questionlatestversion.questionbankentryid = qv.questionbankentryid
JOIN m_question_bank_entries qbe ON qbe.id = questionlatestversion.questionbankentryid
WHERE ti.itemtype = N'question'
AND ti.tagid IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15')
AND qbe.questioncategoryid = '7'
GROUP BY t.name
[array (
0 => 'ready',
1 => 'question',
2 => 1,
3 => 2,
4 => 3,
5 => 4,
6 => 5,
7 => 6,
8 => 7,
9 => 8,
10 => 9,
11 => 10,
12 => 11,
13 => 12,
14 => 13,
15 => 14,
16 => 15,
17 => '7',
)]
Error code: dmlreadexception

Stack trace:

line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 331 of /lib/dml/sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
line 438 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
line 907 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
line 983 of /lib/dml/sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
line 101 of /mod/adaptivequiz/classes/local/repository/questions_repository.php: call to sqlsrv_native_moodle_database->get_records_sql()
line 407 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\repository\questions_repository::count_questions_number_per_difficulty()
line 265 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\fetchquestion->retrieve_tags_with_question_count()
line 299 of /mod/adaptivequiz/classes/local/fetchquestion.php: call to mod_adaptivequiz\local\fetchquestion->initalize_tags_with_quest_count()
line 594 of /mod/adaptivequiz/classes/local/attempt.php: call to mod_adaptivequiz\local\fetchquestion->fetch_questions()
line 339 of /mod/adaptivequiz/classes/local/attempt.php: call to mod_adaptivequiz\local\attempt->get_question_ready()
line 80 of /mod/adaptivequiz/classes/local/itemadministration/default_item_administration.php: call to mod_adaptivequiz\local\attempt->start_attempt()
line 79 of /mod/adaptivequiz/classes/cat_session.php: call to mod_adaptivequiz\local\itemadministration\default_item_administration->evaluate_ability_to_administer_next_item()
line 137 of /mod/adaptivequiz/attempt.php: call to mod_adaptivequiz\cat_session::run_item_administration()

[amit]

Is there any way we can use database agnostic interface in running the queries instead of raw SQL? I tried converting some queries to MS-SQL specific version and made some progress, but a better approach would be a DB engine independent way, right?

[vtos]

No worries, the work is in-progress now and I'm pretty confident the fix will be included in the nearest release, and the plugin will start supporting MSSQL. My messages above were just for reference :slightly_smiling_face: