#4 Gismo 3.2 is not working with Moodle 2.5.1+ under MsSQL...

[Cihat Okan ARIKAN]

Great plugin that I' was using my old moodle 2.2.. But under 2.5.1+ under MSSQL, it is not working and debug messages are follows.. As I seen, the problem was occured due to GROUP BY clauses in queries and if I write open-forms of AS clauses (for example bote_log.time instead of timedate in GROUP BY section..) to all queries then sql queries are working but export_data.php is also working but never finished.

Is there any suggestion for these?

Debug info: SQLState: 42S22
Error Code: 207
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'actid'.
SQLState: 42S22
Error Code: 207
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'activity'.
SQLState: 42S22
Error Code: 207
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'timedate'.

SELECT TOP 20000 MAX(bote_log.id), bote_log.time AS timedate, MAX(bote_log.time) AS time, bote_log.userid AS userid, bote_course_modules.instance AS actid, bote_log.module AS activity, COUNT(bote_course_modules.instance) AS numval FROM bote_log, bote_course_modules WHERE bote_course_modules.id = bote_log.cmid AND bote_log.course = '2' AND bote_log.action IN (N'view',N'view discussion') AND bote_log.module IN (N'chat',N'forum',N'wiki') AND bote_log.id > 0 AND bote_log.id <= 9138 GROUP BY actid, activity, timedate, userid
[array (
0 => 2,
1 => 'view',
2 => 'view discussion',
3 => 'chat',
4 => 'forum',
5 => 'wiki',
)]
Error code: dmlreadexception
Stack trace:
line 423 of \lib\dml\moodle_database.php: dml_read_exception thrown
line 260 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
line 367 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
line 788 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
line 836 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
line 338 of \blocks\gismo\lib\gismo\server_side\GISMOdata_manager.php: call to sqlsrv_native_moodle_database->get_records_sql()
line 34 of \blocks\gismo\lib\gismo\server_side\export_data.php: call to GISMOdata_manager->sync_data()
Output buffer: GISMO - export data (start)!
Nothing to be purged, logs never expire!
MEMORY USAGE BEFORE: 15'028'272DEBUG: Reset max_log_id Export all logs MEMORY USAGE (AFTER COURSES EXTRACTION): 15'056'176 Course ID: 1 NOT EXPORTED Course ID: 2

---

[Christian Milani]

Hello!

Unfortunately i don't have any mssql server to test it... as you said if you change the GROUP BY clause to:
GROUP BY bote_course_modules.instance , actid, bote_log.module, bote_log.time, bote_log.userid

the exporter doesn't give any error? but nothing is exported?
can you run the query manually and let me know?

christian

[Cihat Okan ARIKAN]

Hi, thank you for your interest..

Address:
http://mysite/moodle/blocks/gismo/lib/gismo/server_side/export_data.php?password=*

---

After I get the SQL Query from error message it is executed directly on MSSQL Manager:

[SQL] SELECT TOP 20000 MAX(bote_log.id), bote_log.time AS timedate, MAX(bote_log.time) AS time, bote_log.userid AS userid, bote_course_modules.instance AS actid, bote_log.module AS activity, COUNT(bote_course_modules.instance) AS numval FROM bote_log, bote_course_modules WHERE bote_course_modules.id = bote_log.cmid AND bote_log.course = '2' AND bote_log.action IN (N'view',N'view discussion') AND bote_log.module IN (N'chat',N'forum',N'wiki') AND bote_log.id > 0 AND bote_log.id <= 9557 GROUP BY actid, activity, timedate, userid

[Err] 42S22 - [SQL Server]Invalid column name 'actid'.
42S22 - [SQL Server]Invalid column name 'activity'.
42S22 - [SQL Server]Invalid column name 'timedate'.

---

If I change actid, activity and timedate clasues with their actual values, query is working normally.. And I modified Gismodata_manager.php which is attached with my post.. The error message is changed and it is caused by LIMIT & OFFSET clauses:

Debug info: SQLState: 42000

Error Code: 102

Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '20000'.

SELECT MAX(bote_log.id), time AS date_val, MAX(time) AS time, COUNT(id) AS count, userid FROM bote_log WHERE course = 2 AND bote_log.id > 0 AND bote_log.id <= 9558 GROUP BY userid, time LIMIT 20000 OFFSET 0
[array (
)]
Error code: dmlreadexception

Stack trace: ◦line 423 of \lib\dml\moodle_database.php: dml_read_exception thrown
◦line 260 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
◦line 367 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
◦line 788 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
◦line 836 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
◦line 401 of \blocks\gismo\lib\gismo\server_side\GISMOdata_manager.php: call to sqlsrv_native_moodle_database->get_records_sql()
◦line 34 of \blocks\gismo\lib\gismo\server_side\export_data.php: call to GISMOdata_manager->sync_data()

Output buffer: GISMO - export data (start)!
Nothing to be purged, logs never expire!
MEMORY USAGE BEFORE: 14'944'552DEBUG: Reset max_log_id Export all logs MEMORY USAGE (AFTER COURSES EXTRACTION): 14'972'488 Course ID: 1 NOT EXPORTED Course ID: 2 MEMORY USAGE (MIDDLE ACCESSES ON ACTIVITIES): 14'979'216 MEMORY USAGE (MIDDLE ACCESSES ON ACTIVITIES): 14'979'544 MEMORY USAGE (MIDDLE ACCESSES ON ACTIVITIES): 14'979'216 MEMORY USAGE (MIDDLE ACCESSES ON ACTIVITIES): 14'979'104 MEMORY USAGE (AFTER ACCESSES ON ACTIVITIES): 14'979'104

---

And after this, I remove LIMIT and OFFSET clauses from the query,
then execute the export_data.php again but it is not working because it is like working but
nothing exported and nothing written on the screen.. Page loading is rounding and page waiting to finish but not finish...

And When I try to execute queries on MSSQL Manager after changes made, (without AS clauses and LIMIT & OFFSET clauses) it is returning data values normally without any errors..

After about 5 minutes, IE has returned the error on the screen:
(php maximum script running time is 300, so it is waiting to finish it)

---

Timed out while waiting for session lock.
Wait for your current requests to finish and try again later.
Debug info:
Error code: sessionwaiterr
Stack trace:
line 1363 of \lib\dml\sqlsrv_native_moodle_database.php: dml_sessionwait_exception thrown
line 609 of \lib\sessionlib.php: call to sqlsrv_native_moodle_database->get_session_lock()
line ? of unknownfile: call to database_session->handler_read()
line 190 of \lib\sessionlib.php: call to session_start()
line 482 of \lib\sessionlib.php: call to session_stub->construct()
line 66 of \lib\sessionlib.php: call to database_session->construct()
line 755 of \lib\setup.php: call to session_get_instance()
line 25 of \config.php: call to require_once()
line 2 of \blocks\gismo\lib\gismo\server_side\export_data.php: call to require_once()

---

[Christian Milani]

Unfortunately searching in google I discovered that LIMIT is not working in mssql...

Please try to modify LIMIT with TOP as you can see in this example:
http://stackoverflow.com/questions/455008/how-to-use-limit-keyword-in-sql-server-2005

maybe you should try something like this for LIMIT,OFFSET:
http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server

[Cihat Okan ARIKAN]

Hi again, I'm changing lots of things in the GISMOdata_manager.php and at last I did it worked.. But some of menu options seem still doesn't have data (Students: Access by students, Students: Access overview, Completion: Resources). I've attached modified file with my post.. I've broken all the loop sections after one turn, because if it is remains as old way, script does not finish and I think same datas continuously get looping..
Maybe you can check and improve the mine modified queries in better work state..
Thanks..

[Rebecca O'Dea]

Hello,

I changed the code and it works well in MOODLE 2.5 and MSSQL

[Christian Milani]

thanks! did you change just the file FetchStaticDataMoodle.php or even GISMOdata_manager.php (for the export)

[Rebecca O'Dea]

i modified GISMOdata_manager.php, ajax.php