User Defined Function as table for external DB plugin

User Defined Function as table for external DB plugin

by Alan Zaitchik -
Number of replies: 0
We are using a Table-type User Defined Function in MS SQLServer in order to have the external DB enrollment plugin identify itself to the student enrollment data, in case we have more than 1 Moodle server delivering courses to the student body. Thus the "table" for Moodle1 might be configured to use the user defined function "moodle_enroll('moodle1')" whereas the Moodle2 server might specify "moodle_enroll('moodle2')". (The user defined function knows which courses are delivered on Moodle1 and which on Moodle2, so it returns the right table to Moodle.)
*BUT* notice that we have to place a single quote around the server name as the argument to the udf. In the MySQL database the single quote is escaped with a backslash, and every time anyone opens the external db enrollment plugin configuration page the quote is escaped yet again, i.e. the backslash is escaped with another backslash.
What's worse is that when we run enrol_database_sync.php the query end us like

SELECT Username FROM CUS_udfEnrollment(\'moodle3\') WHERE CoursecodeYrTrmSection = 'ACTG062_2007_10_A' AND Role = 'student'

and the script views the backslahes as an SQL syntax error.
Has anyone confronted this issue before?
I am considering adding a stripslashes cleanup to a cron job, to keep the number of backslashes down to a reasonable number (=1!) and adding some special lines of code to enrol.php to remove the backslashes in the generation of the SQL. Still, if there is a solution that doesn't involve hacking the enrol.php code I would love to hear about it.

Thanks,
Alan

Average of ratings: -