Sample external enrollment database

Sample external enrollment database

by Greg Bowers -
Number of replies: 6

Hi,

Does anyone have a sample data for an enternal enrollment database table? We were attempting to use the same database view for both external user authentication and external database enrollment and are now encountering problems. If you could just email me a spreadsheet of a few sample records to show how it works that would be greatly appriciated.

Thanks much!

greg@richmondwebservices.com

Average of ratings: -
In reply to Greg Bowers

Re: Sample external enrollment database

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I use the following SQL code (specific for MySQL) to create a test external database when I want to debug thins in that area (with a few hundreds record more, but you get the idea):

CREATE TABLE mdl_user_course_rol (
user varchar(32) default NULL,
course varchar(32) default NULL,
rol varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO mdl_user_course_rol VALUES ('iarenaza', 'LD2011A', 'teacher');
INSERT INTO mdl_user_course_rol VALUES ('iarenaza', 'LD2011B', 'student');
INSERT INTO mdl_user_course_rol VALUES ('studa', 'LD2011A', 'teacher');
INSERT INTO mdl_user_course_rol VALUES ('studa', 'LD2011B', 'student');
INSERT INTO mdl_user_course_rol VALUES ('admin', 'LD2001A', 'editingteacher');

The value of the 'user' column is matched againt the user 'username' value, the 'course' column is matched against the course 'shortname' value and the 'rol' column against the rol 'shortname' value.

Saludos. Iñaki.

Average of ratings: -
In reply to Iñaki Arenaza

Re: Sample external enrollment database

by Bill Jones -
Iñaki,

I've been having trouble getting the External DB service running, so I copied your helpful tables into a new table I created in our Moodle database and pointed the External DB enrollment plugin to it. I still get no response from it.

I've traced the login process through several steps and established that the login procedure is able to find the plugin and begin the "setup_enrolments" function in /enrol/database/enrol.php. However, it appears to jump out at line 82 with the script:
if (!$rs->EOF) { // We found some courses

(Apparently, $rs is at end-of-file and it didn't find any courses.) It looks to me that the problem is in the SQL queries in lines 64-79. I'm stumped on how to troubleshoot these, and was hoping that by copying your generic database I could eliminate any field type issues, etc. But that hasn't worked. It's also difficult to use print_r() or var_dump() commands for debugging here since they are quickly bypassed in the login process. Any ideas?

Thanks very much,
Bill Jones

My Table
*** Table Structure ***
Field Type Collation Null Default
user varchar(32) utf8_general_ci Yes NULL
course varchar(32) utf8_general_ci Yes NULL role varchar(50) utf8_general_ci Yes NULL
 
*** Data ***
user course role
11 20083099992 teacher
152 20083099992 student
153 20083099992 student
Average of ratings: -
In reply to Bill Jones

Re: Sample external enrollment database

by Jonathan Moore -
I have run into some problems with the module in the past that sound similar to this. At the time I was able to trace it to needing a newer version of mysql. I think the problem has been fixed in later code updates, but at the time I had to go to mysql 5.x load to get the enrolments to work with an external database. This was on an early 1.9 release.
Average of ratings: -
In reply to Jonathan Moore

Re: Sample external enrollment database

by Bill Jones -
Jonathan,

Thanks for your response. The mySQL I'm running is 5.0+. I'm hoping the general query log will show something. I'll copy the actual queries that Moodle sends and then run them from the SQL screen in phpMyAdmin to see what's coming back.

Cheers,
Bill

MySQL
  • Server version: 5.0.51a-community
  • Protocol version: 10
Moodle
  • 1.9+ Build: 20080514

Average of ratings: -
In reply to Bill Jones

$useridfield error in /enrol/database/enrol.php script

by Bill Jones -
Hello,

An update--my problem has been that the external enrollment is working. I've isolated the problem down to a line in the 
/enrol/database/enrol.php
script:

line 42 - $useridfield = $enroldb->quote($user->{$CFG->enrol_localuserfield});

Usually, this line should produce the userid from from the external database, but whenever I run it I get a value of '1000', for all users and all roles. When I substitute a real userid from my external database into the query that this $useridfield line feeds into I get correct results.

Any help on how the PHP here functions would be helpful.

Thanks,
Bill Jones
Average of ratings: -
In reply to Bill Jones

Solved: $useridfield error in /enrol/database/enrol.php script

by Bill Jones -
Hello,

Duh, I had the wrong field name entered in the External DB settings. The default they suggest is idnumber, but that field is empty in our tables which is why I got the "1000" value back. When I reset the name, everything came back ok.

Thanks,
Bill Jones
Average of ratings: -