Moodle in English: Someone explain external database enrolment, please??

Someone explain external database enrolment, please??

by Howard Miller - Monday, 18 June 2007, 8:30 PM

I'm a bit confused by external database enrolment, not helped by the documentation being very terse to say the least.

I do have it working, but I'm slightly worried that I don't fully understand the process. I guess I have the following questions:

The docs say "External database enrolment happens at the moment when a user logs into Moodle.", what does that mean? What exactly happens when a user logs in?
Then it says "An easy way of checking how it's working is to try logging in as a student.". Ok, that's nice, but what am I expecting to see? I do get a few irritating notices about skipping courses at the top of the screen but that looks like a bug.
Next sentence - "External database enrolment also manages unenrolment. ". This worries me! What exactly does this mean? Does this mean that a student who is not in the database (any more) will be removed from the course? If so, is there any way to stop this? I am thinking about completed courses that I want to keep available in an "archive".
Next para. "You may synchronize all enrolments by executing the enrol_database_sync.php script". Ok, what does that do?
..and "This assumes that you have already synchronized all users from your authorization plugin.". Errr..... never mind
Elsewhere in the forum, automatic course generation gets mentioned, but I don't see any reference to this. How does this work - if it indeed exists?

Sorry if I sound a bit negative, but I'd like to understand and improve this a bit

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Helen Foster - Monday, 18 June 2007, 9:11 PM

Hi Howard,

Firstly, I am definitely no expert on enrolment plugins, so I hope that anything that I write either here or in the documentation which is wrong will be corrected by an expert... wink

Anyway, my understanding of external database enrolment is as follows:

External database enrolment happens at the moment when a user logs into Moodle - as opposed to interactive enrolment, which triggers only when a user tries to enrol on a course. A login-time lookup is performed against the external database.
An easy way of checking how it's working is to try logging in as a student. In Moodle 1.6 I used to use the "Login as" button on a student's profile page to prompt a login-time lookup. I would see the list of courses that the student was enrolled on matching the data in the external database. I've not tried this in Moodle 1.7 or 1.8 though.
External database enrolment also manages unenrolment. Yes, this does this mean that a student who is not in the database any more is removed from the course (again only tried in Moodle 1.6).

Hope this explains a little more and I look forward to docs.moodle.org/en/External_database being improved.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Alick Brown - Monday, 18 June 2007, 10:29 PM

Also trying to make sense of external database enrolment...

The help labels suggest enrolment takes username, course & role from external database and reads them in to Mooddle database.

Can someone tell me how does one enrol a student into several courses - in separate categories? [I'm trying to use odbc].

Thanks

Alick

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Clinton Graham - Tuesday, 19 June 2007, 12:19 AM

External database enrollment takes a username and assigns them the specified role in a particular course. It doesn't matter the category in which the course exists, so I'm a bit confused by the question.

Lets say you have a Fall, Summer and Spring category, each with courses... CS101FA, IT200FA in Fall, CS101SU in Summer, and CS102SP & GR400SP in Spring. We'll assume you've set the course idnumbers to be the shortnames as above. Let's also say you have users with usernames of StudentA, StudentB, StudentC, TeacherA, and TeacherB.

We'll set the local course field to "idnumber", the local user field to "username", and the local role field to "shortname".

Perhaps your external database view would have rows of:
CS101FA editingteacher TeacherA
CS101FA student StudentA
CS101FA student StudentB
IT200FA editingteacher TeacherA
IT200FA teacher TeacherB
IT200FA student StudentC
IT200FA student StudentA
IT200FA guest StudentB
CS102SP teacher TeacherA
GR400SP student StudentC
CS101SU editingteacher TeacherA
CS101SU editingteacher TeacherB

Thus TeacherA would be instructing StudentA and StudentB in CS101FA (in the Fall category), while in the course GR400SP StudentC has is enrolled and waiting on a teacher to be assigned. IT200FA has TeacherA being assisted by TeacherB to instruct StudentC and StudentA, with StudentB limited to guest access in the course.

Does that help?

Average of ratings: Useful (2)

Re: Someone explain external database enrolment, please??

by Alick Brown - Tuesday, 19 June 2007, 8:01 PM

Thanks Clinton (and everyone else here). I'm starting from a base level so your patience is appreciated.
My question about categories comes from the auto-create section of the enrolment page:
Auto-creation of new courses
enrol db autocreate: yes Courses can be created automatically if there are enrolments to a course that doesn't yet exist in Moodle.
enrol_db_category: History The category for auto-created courses.

My categories are subject-based - History, Economics, English etc...
They help make courses easier to find, and if I can automate them it takes the admin load off teachers like me who just produce courses and teach them
But ....Clinton, you don't recommend automation...
Iñaki - will your patch work for ODBC?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Tuesday, 19 June 2007, 8:41 PM

But ....Clinton, you don't recommend automation...

We use it with the patch mentionned above without any side-effects. The patch gives us the flexibility we need (and if it doesn't, we just extend it

Iñaki - will your patch work for ODBC?

It'll work with any database driver, as it's database agnostic (as long as you don't use non-portable SQL queries, and we don't). If it doesn't work for ODBC, then it's a bug and we will gladly fix it

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Martín Langhoff - Wednesday, 20 June 2007, 4:35 AM

I am intererested in getting this patch in my "new" database plugin (targetted for 1.9) but there's something I don't quite understand...

Don't you create the courses hidden so a teacher has a chance of putting some content in there before students get in?

BTW, the enrol/database for 1.9 can do templates that cover all the course content, not just settings

The only downside is that it won't work during user login - content restore really needs a lot of time, so it's done on sync.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 1:44 PM

Don't you create the courses hidden so a teacher has a chance of putting some content in there before students get in?

Right now we don't (nobody has asked for it here).

BTW, the enrol/database for 1.9 can do templates that cover all the course content, not just settings

We are working in exactly this too , by using the backup/restore infrastructure. The idea is to have differente course templates for each category, with some default content for every new course we create, the content depending on the particular category. I don't have direct knowledge of the details of this feature (it's a student's project that I don't mentor) but they are going to show me what they have today, so maybe we could compare the two approaches and find The Right Way(tm) to do it

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Martín Langhoff - Wednesday, 20 June 2007, 4:05 PM

Good one!

MattC has just gotten that going on top of the new enrol/database - see his commit on http://git.catalyst.net.nz/gitweb?p=moodle-r2.git;a=shortlog;h=mdl18-local

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Sunday, 24 June 2007, 11:33 PM

I've had a look at MattC's patch and he's doing it exactly the same way the people here. But they have discovered a couple of gotchas in import_backup_file_silently()

Section summaries are not restored.
Course blocks are not restored either.

I don't remember all the details they told me, but it all revolves around the fact that import_backup_file_silently() hardcodes:

$SESSION->restore->restoreto = 1

unless you ask for user data restore, and then restore_execute() skips some things.

We don't know if this is intended behaviour or a bug, so I told them to ask Eloy (as the backup/restore maintainer).

I'll keep you informed

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Janet Smith - Thursday, 14 February 2008, 12:14 PM

Hi,

I am using a slightly modified version of plug-in Iñaki created in Moodle 1.8. I really need it to create the courses as hidden so the instructor has a chance to add some content before the user's see it.

Has anyone added this logic? Any suggestions on where to start would be great.

Also, in the same plug-in, has anyone added logic to sort the courses alphabetically within the categories?

Thanks!

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Friday, 15 February 2008, 8:33 AM

I really need it to create the courses as hidden so the instructor has a chance to add some content before the user's see it.

This is rather easy

Search for the lines of code that read:

// override with local data $course->startdate = time() + 3600 * 24; $course->timecreated = time(); $course->visible = 1;

and simply change:

$course->visible = 1;

to:

$course->visible = 0;

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Janet Smith - Friday, 15 February 2008, 10:52 PM

Hi,

Thanks Iñaki for the quick and helpful response. I've got one more question. The database I am using for enrollment allows the ability to assign more than one instructor to a course. When I run the External Database Enrollment using the patch you provided, any course will more than one instructor doesn't come over into Moodle.

Have you found this? Is this something that could be added to the logic of the patch? I just need it the course in, even if it is only with the first instructor on the list.

Ideas?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Saturday, 16 February 2008, 10:16 AM

When I run the External Database Enrollment using the patch you provided, any course will more than one instructor doesn't come over into Moodle. Have you found this?

Hummm, no, I haven't found that. In fact, the patch should create the course no matter how many instructors there are. The patch creates the course as soon as a student or teacher needs to be assigned a role in that course. So when we the second instructor needs to be assigned the role, the course should already be created, and the instructor simply enroled with no further ado.

I'll re-test the patch in my test environment and will comment back.

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Janet Smith - Saturday, 31 May 2008, 2:19 AM

Hi Iñaki,

I am just circling back to this problem that you wrote back to me about in February. For some reason, when my file from the external database has 2 instructors, the course doesn't get created at all. Did you have any luck testing this in your environment? Any ideas why this might be the case?

Thanks,
Janet

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Sunday, 8 June 2008, 7:30 AM

I have just tested the patch with 1.9.1+ (current as of today) and it creates the courses fine, with 1, 2 or more teachers (or editingteachers, or students) in the external database.

I've tested it with MySQL as the external database, but this shouldn't make a difference as we are using this same patch with Oracle in production at the university.

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Janet Smith - Sunday, 8 June 2008, 9:19 AM

Thanks for testing it. I think the problem is likely to the query coming out of my external database. I'll take a look at that more now.

Thanks! Janet

Average of ratings: -

Re: Someone explain external database enrolment, please??

by SpookieUkie Shmi - Wednesday, 28 March 2012, 10:21 PM

I am having a weird issue with external database enrollment at the moment. When the users is "getting enroled in theory" by the script, they are getting access to all the courses instead of the single courseID specified. For ex, the course ID is ABC123 and the roleID is set to 5, which is the student. Futhermore, they are not enroled until they start any course on the list.

In the end, I would like them to just see the course in the list that they paid for.

I am wondering if this is an issue with the script or something that with how the Moodle is setup on the server. One potential solution is to use an enrolment key. We would prefer not to have an enrolment key for each course. It almost seems like its just setting up the user for access to the moodle as a student, with nothing getting set for acceess to the courses.

On another note, if I just sign up a user manually with a student role, I am also able to get all courses by default.

Any input or direction will be very helpful

Thanks

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Clinton Graham - Tuesday, 19 June 2007, 12:04 AM

I've been there and fought that battle.

External database enrollment simply means that Moodle will read an external database (via ADODB) to determine enrollments for roles in courses. This is generally managed by view in your database containing your student records. The contents of this view are compared against Moodle's own role assignment tables, and from these contents, Moodle's tables are updated.

Moodle connects to the external database for this comparison both 1) when a user logs into Moodle, and 2) whenever the moodle/enrol/database/enrol_database_sync.php script is run (usually in a cron job). This script synchronizes all enrollments for all users.

I would disagree about the usefulness of logging in as a student to test the process... particularly when using a secure authentication connection such as LDAP where the admin doesn't know the login info! Manually run the enrol_database_sync.php script to see the process run.

Unenrollment occurs when the student's role assignment no longer exists in the external database. If you really want to keep your role assignments permanently, that's fine... just keep the assignment in the external database view (or perhaps better, set up a "guest" style historical role in Moodle, and have your external database move folks to that new role after the class is over.)

If a course doesn't exist in Moodle which is referenced in your external database, the script will (buggishly, but I think this has been reported and fixed in later versions) report that as a notice. Alternately, if you have the settings configured to automatically create the course, the script will take the (optional) template course and copy it into the specified category as a new course for the associated enrollments. I don't recommend it... it's inflexible and course creation doesn't belong in the context of enrollments. Create the necessary courses beforehand (we use a script to connect to another view in the database).

Hope that helps a bit.

Average of ratings: Useful (1)

Re: Someone explain external database enrolment, please??

by Ângelo Rigo - Friday, 22 February 2008, 4:57 AM

Hi Clinton

How does the script that you mention connect to a external database to course creation purpose ?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Tuesday, 19 June 2007, 12:16 AM

The docs say "External database enrolment happens at the moment when a user logs into Moodle.", what does that mean? What exactly happens when a user logs in?

As Helen says, when the user logs in, there is a call to setup_enrolments(), which is called from require_login() through a series of intermediate functions. This is where the enrolment takes place. So if a user never logs in, s/he is never enrolled into any course (by the external database enrolment plugin).

- Then it says "An easy way of checking how it's working is to try logging in as a student.". Ok, that's nice, but what am I expecting to see? I do get a few irritating notices about skipping courses at the top of the screen but that looks like a bug.

I'd say this is a bug too. IMHO they should go to the system logs, instead of being print on screen. If you have everything correctly setup, the user should log in normally and see the list of courses s/he is enrolled into, just as you'd be using manual enrolment. At least that's what we get in our setup.

- Next sentence - "External database enrolment also manages unenrolment. ". This worries me! What exactly does this mean? Does this mean that a student who is not in the database (any more) will be removed from the course? If so, is there any way to stop this? I am thinking about completed courses that I want to keep available in an "archive".

It means that if a given user is no longer in the external enrolment database, as far as Moodle concerns, this user is no longer enrolled into any courses. The external database is the master for the enrolment data, so Moodle just 'synchronizes' to that data. If you don't want your users to be unenrolled, you need to remove the part of the code that unenrols people, or mark those enrolmentes that you want to 'archive' as 'manual' enrolments (so the external database enrolment plugins doesn't touch them).

- Next para. "You may synchronize all enrolments by executing the enrol_database_sync.php script". Ok, what does that do?

That performs the enrolment/unenrolment logic without the users needing to log in. So you 'synchronize' all[1] of your enrolments at once.

[1] All the enrolments marked as 'database', that is.

- ..and "This assumes that you have already synchronized all users from your authorization plugin.". Errr..... never mind

This is quite understandable. If you are going to enrol someone into a course, you need that someone to exist in Moodle beforehand. As users are created the first time they log in, unless you synchronize your users (to create/delete all of them without having to log in), there'll be no users to enrol. A bit convoluted, but completely logical

- Elsewhere in the forum, automatic course generation gets mentioned, but I don't see any reference to this. How does this work - if it indeed exists?

It exists (we are using it too

). If you enable course creation, whenever the plugin tries to enrol someone into a course, it checks whether the course exists or not. If it doesn't, it pulls some data from your external enrolment database and creates the course on the fly, and then enrols that someone into the course.

You can configure a couple of things or two (like the course template for course auto-creation and the category where you are going to put your course), but that's it in standard Moodle. We have a little patch that adds some additional settings like being able to specify a differente category and template for each course (available for 1.5 through 1.9dev). Should you be interested in it, you can get it from:

http://orodruin.escomposlinux.org/~iarenaza/moodle/

(there is no 1.9 directory there because there is no 1.9 version yet, but the patch for 1.8 applies cleanly to 1.9dev as of today).

Hope this helps

Saludos. Iñaki.

Average of ratings: Useful (2)

Re: Someone explain external database enrolment, please??

by Martín Langhoff - Tuesday, 19 June 2007, 7:59 AM

Then it says "An easy way of checking how it's working is to try logging in as a student.". Ok, that's nice, but what am I expecting to see?

I do get a few irritating notices about skipping courses at the top of the screen but that looks like a bug.

Not a bug. If you get errors about skipping courses, is because the external DB is listing courses that dont' exist.

Either create the courses with matching shortname or idnumber (depending on how you've configured things) or let enrol/database create an empty course.

"External database enrolment also manages unenrolment. "

If on Monday there's a row in the DB with "smith", "English-101", then smith get's enrolled in English-101. On Tuesday, if that row is gone, smith will get unenrolled. Easy, and simple.

If you want to keep those enrolments around, make sure that the external db keeps them

Here's a trick: we use the mode where you provide the role shortname as well -- so it says "smith", "English-101", "student". When the course it's over, we switch it to "smith", "English-101", "ro-student" -- and beforehand we've created a new role called ro-student, clone of "student" but that cannot change anything -- hence "read-only".

Of course, we don't "do" this manually, but control it from a SMS that knows the course start/end dates. The "table" we tell moodle to read from is a view - like moodle_student_course_vw here - note the CASE sections that say

WHEN enrolend < now() THEN 'student' ELSE 'ro-student'

Average of ratings: Useful (1)

Re: Someone explain external database enrolment, please??

by Howard Miller - Tuesday, 19 June 2007, 10:34 PM

Thanks Guys, this helps a great deal. I will probably apply my new found knowledge to the docs.

Can I just clear up a couple of points then...

The external database is I assume the highest authority - what's in there goes? So if I manually add a student to a course and they are not mentioned in the database they will be removed again when they next log in?

Expanding on my removing users question, what happens if I subsequently remove the id number from the course settings, so it can no longer match up with the external database - do all the students then stay regardless of their status in the external database or do they *all* get thrown out.

Martin, I'm sorry but I don't really buy what your saying about those messages. That would be fine if they where nicely presented, but they are just chucked out exactly as though they where forgotten about debugging messages. It doesn't look very good at all. If you create courses manually but enrol against the database (not unreasonably) there will be loads of 'missing' courses and it looks terrible.

EDIT:
Another question - does "login as" a user run the database lookup (as the docs rather imply), as opposed to actually logging in as a student.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 12:39 AM

So if I manually add a student to a course and they are not mentioned in the database they will be removed again when they next log in?

No, they won't (thankfully ). Moodle tracks the enrolment 'origin' of every enrolment (from 1.5 on at least). So the external database enrolment plugin only 'plays' with enrolments marked with 'database' origin. You can mix-and-match enrolments from different plugins at the same time on the same course from 1.6 on, and every sub-system will only touch its.

Expanding on my removing users question, what happens if I subsequently remove the id number from the course settings, so it can no longer match up with the external database - do all the students then stay regardless of their status in the external database or do they all get thrown out.

If the enrolment is marked as 'database' and Moodle can no longer find the course in the external database, then it's removed. You need to change those enrolments to 'manual' (for example) to make them stay.

Another question - does "login as" a user run the database lookup (as the docs rather imply), as opposed to actually logging in as a student.

With 1.6, it did. With the new roles thing, I don't really know (have never tried myself).

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 1:24 AM

You need to change the enrolments to 'manual'

Is there an interface in Moodle for that? I don't remember seeing anything.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 3:44 AM

I'm afraid there isn't. As far as I know, you need to modify the (Moodle) database directly for the affected users.

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Martín Langhoff - Wednesday, 20 June 2007, 4:31 AM

The external database is I assume the highest authority - what's in there goes? So if I manually add a student to a course and they are not mentioned in the database they will be removed again when they next log in?

No - each enrolment has its "type" so a manual enrolment won't be removed by a "database" enrolment. The database plugin will only remove what it's added. So if you want you can add manual enrolments.

what happens if I subsequently remove the id number from the course settings

The database enrolments will be removed, and (if it can) a new course will be created. In other words: Don't do it -- it won't be pretty.

Martin, I'm sorry but I don't really buy what your saying about those messages. That would be fine if they where nicely presented

I agree - it's a bug that they aren't nicely presented. Patches welcome

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 4:45 AM

K - I'll put my money were my mouth is, I'm on it

I'm still a bit hazy on what possible use these messages could be to an "ordinary" user though? Should they not be tied to the debug modes, or have I misunderstood their purpose?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Martín Langhoff - Wednesday, 20 June 2007, 10:43 AM

> I'm still a bit hazy on what possible use these
> messages could be to an "ordinary" user though?

Good point. It's an error we want to be able to show to the admin later. Log them as errors via add_to_log() perhaps, like we do with invalid logins?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 1:34 PM

In our patch, we just error_log() them (like many other things in the plugin do). But we could certainly user add_to_log() in addition to it.

Saludos. Iñaki.

Average of ratings: -

Re: external database UNENROLMENT

by Johnny Zephyr - Monday, 19 July 2010, 8:51 PM

HI People

My question is regarding unenrolment.

My external database enrolment is working fine. All my users are appearing in the correct course participants list so i'm happy.

I have done as stated which is 'remove the line from the external db' but my user is not automatically unenrolled from the database. So i'm not sure what i'm doing wrong.

Initial enrolment worked brilliantly. I edited the external DB and removed a few enrolments; re-ran the enrol_database_synch.php and its not changed anything in moodle enrolments.

i've then manually removed the user from the course, re-entered the user in my enrolments db and re-ran the script and the user has been successfully enrolled which is great!

i have enabled database_unenrol too so.... AHHHH

what am I doing wrong?

Thanks Guys.

ps: using 1.9.8 on xampp.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Wednesday, 20 June 2007, 4:46 AM

Hi Guys,

Im a total newbie to the moodle world and was hoping someone could point me in the right direction with external database enrolment.

The database I am trying to connect to is a FileMaker database. I have enabled it for ODBC access and am hosting it locally for testing purposes. I only have three fields in the database as listed below.

idnumber,username,shortname

CF101, Blue, Student

CF102, Blue, Student

CF101, Green, Student

In moodle I have created two courses CF101 and CF102. I have also created two users, Blue and Green whom have Student as their role. I have enabled enrolments to accept external database. My external database settings are.

enrol_dbtype: odbc

enrol_dbhost:127.0.0.1

enrol_db:Admin

enrol_dbname:ODBC

enrol_dbtable:ODBC

Under enrolement (remote) database fields,

enrol_localcoursefield: idnumber

enrol_localuserfield: shortname

enrol_db_localrolefield: Blank

I presume the local fields should be my FileMaker fields and the remote fields are the fields in mySQL?

enrol_remotecoursefield: shortname

enrol_remoteuserfield: username

enrol_db_remoterolefield: Blank

Ive got autocreation of new courses selected as yes.

When I login as a Student, i.e. Blue I get the list of courses displayed on the homepage, 101 and 102. When I click on either of them moodle asks me whether I would like to enrole - if my external database was working correctly I presume I would directly go in to the course. Any ideas what I could be wrong......I'm almost there but cant quite get it to work. I know ODBC is working with FileMaker as I've got MS Word to link to it

Many Thanks to all that respond

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Clinton Graham - Saturday, 23 June 2007, 5:06 AM

I need to be brief, but with regard to the config above, I would note that:
1) the local fields should be from your Moodle database.
2) the remote fields should be from your student database
3) The users Blue and Green should not have 'student' roles set manually in Moodle - this role assignment is what we are attempting to accomplish automatically.
4) The local and remote role field should be set to 'shortname' and 'shortname' in this example (and I think 'Student' should be all lowercase in your external DB).

Hope that helps.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Monday, 25 June 2007, 7:12 PM

Hi Clinton,

I've followed the steps exactly as you described. Taken off any Global roles for the students, made sure the data in shortname is all lowercase student. Still no luck. Both the remote and local fields are now mapped correctly, I've re-installed ODBC just incase.

Is there a way (possibly a plugin or alter code) to actually type in the ODBC source name in the system which is modbc. At the moment I know you can type in the tablename and filename, but I would know moodle was trying to connect to the right datasourcename.

Perhaps I should start a new thread - Connecting Moodle with ODBC as Im sure this may be useful for others?

Thanks

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Monday, 25 June 2007, 9:52 PM

I don't know if it's your *thing*, but when things get this bad I sometimes just write a short test script (outside of Moodle) to try and connect to the database. It will show up if your configuration is correct or not. It's just eliminating possibile points of failure one by one really.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Tuesday, 26 June 2007, 2:17 AM

Thanks Howard and all others that have tried to guide and help me,

I've given up getting moodle to access an odbc external datasource for enrolement. I've started coding insert statements in my FileMaker database which is then exported into Moodle MySQL via ODBC...although not a live connection, I know it works as I've done the user table, now Ive got to get on and do the courses.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Nathaniel Bird - Saturday, 28 July 2007, 12:27 AM

I can see why there might be some confusion when going though the settings. If I were to just go by the fieldnames, it wouldn't seem too confusing, but when you read the descriptions that are shown to the right they confuse things. For example, the enrol_localcoursefield has this as a description:

The name of the field in the course table that we are using to match entries in the remote database (eg idnumber).

The highlight is mine for to show where it appears confusing. The rest of the fields have similarly confusing descriptions. This is in Moodle 1.8. I'm not sure if it's been corrected by the latest build though.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Van Howell - Friday, 10 August 2007, 12:06 AM

I have downloaded your patch and applied it. I got some errors when I applied it. I attached the config.html.rej file. It is the same as the enrol.php.rej file. When I look at the Enrol Database config screen I see the extra fields. I populated them with the field names from my View and ran enrol_database_sync.php from the Linux command line. I got a whole list like this "Creating Course UNI2000.02-FA01...course UNI2000.03-FA01" But when I look at Moodle there are no courses there. I had one Course that i manually created and it had students assigned so at least part is working

I am using Moodle 1.8, on a LAMP server

My external database is MSSQL (SCT PowerCampus)

I need this done ASAP so any help will be greatly appreciated.

Van

config.html.rej

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Van Howell - Friday, 10 August 2007, 4:17 AM

I posted a problem with the patch before. I have since updated to v1.8.2 of moodle and severla Hunks in enrol.php have failed.

Van

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Monday, 13 August 2007, 2:16 AM

Sorry about that. Moodle is a moving target and I upgrade the patches from time to time, but not daily

I'll try to upgrade the patches in the following days.

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Van Howell - Monday, 13 August 2007, 10:42 AM

I have manually applied the Diff file I have not tested it yet. I will test it on Monday. If I have gotten it right I will send you a diff of my work.

Van

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Thursday, 16 August 2007, 6:34 AM

I have updated the patches to the latest versions as of today. You can find them at the usual place

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 4:49 PM

Another question to add to the list - if courses are automatically created. Where does the name of the course come from? Potentially the only information available in the database is the id number!

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 4:56 PM

The current behaviour is that the short and full name of the course are set to what you get from your external database. It doesn't have to be the id number (we are using the course 'ID number' for example, but one could use the short name or the full name too).

With our patch, you can specifiy which table fields hold the fullname and the shortname (among other things).

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Todd Burris - Sunday, 20 April 2008, 2:28 PM

Where can I find the latest version of this patch?

Thanks in advance...T

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Sunday, 20 April 2008, 11:29 PM

You can get the original patch from http://orodruin.escomposlinux.org/~iarenaza/moodle/enrol_db/

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Todd Burris - Monday, 21 April 2008, 9:41 PM

Does this patch work with Moodle 1.9?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Sunday, 8 June 2008, 6:14 AM

Yes it does

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 5:05 PM

I wrote some more documentation for this:

http://docs.moodle.org/en/External_database

...if anybody can be bothered, could you give it a read through and check is makes sense and/or is correct.

Thanks

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Iñaki Arenaza - Wednesday, 20 June 2007, 6:12 PM

The section on the synchronization script doesn't make much sense to me. It talks about user synchronization (auth/db/auth_db_sync_users.php) when it should talk about enrolment synchronization (enrol/database/enrol_database_sync.php).

For the latter to work, you need to make sure your users exist beforehand (or at least, all the users present in the enrolment data). One way of doing it is by using the former, but you could create all of your users manually too.

So I'm going to change that section, if you don't mind

Saludos. Iñaki.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 6:20 PM

I don't mind at all - let's get it right

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Wednesday, 20 June 2007, 6:36 PM

Thanks Howard,

At least I have my fields the right wayaround, I thought local was the external and vice versa...

You have a note, which says that "you will need to compile php with the appropriate options through ODBC". I use the latest WAMP server. Does anyone know if I need to to alter this configuration to get External DB working? I know I have my ODBC setup working correctly.

Thanks

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Wednesday, 20 June 2007, 6:41 PM

*OR* through ODBC

The note just meant that you cannot automatically access databases in all those formats - you need to get PHP to talk to whatever external database type you are using. How this happens differs according to the database type, but this mostly comes down to enableing the appropriate option in PHP or connecting via ODBC.

What type of external database are you using?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Wednesday, 20 June 2007, 6:43 PM

Hi Howard Im using FileMaker. I've enabled ODBC access to it, ive also checked WAMP and has the ODBC extension installed.

Kinda puzzled as to what I do next...Ive matched the fields only have 3 fields in my filemaker database which I want moodle to recognise.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Friday, 22 June 2007, 3:53 AM

Hi its me again,

Thought I'd try and connect to an access database via ODBC to see how the whole external database works. What do I place for the dbhost....the access database is on my local computer as well as moodle, so I've typed in 127.0.0.1 and also tried localhost but cant seem to connect.

Am I right in think Moodle doesn't IMPORT the data, just accesses it? If it does import it, what table can I import this data into (I can easily script an export out of FileMaker as long as I know which table to import it in.)

Sorry for the noob questions, really starting to get into Moodle now

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Friday, 22 June 2007, 4:16 PM

Guys,

Ive read through the entire thread and others through the search, and as suspected, database enrolement just reads the data it doesn't import it.

Still banging my head against a brick wall with trying to use the Access or FileMaker database for enrolement. Whats the difference between odbc and ado_access connection. I have setup a ODBC with the access so I presume I should be using ODBC?

I migt have to concede I dont think I'm going to get Moodle to talk to FileMaker/Access via ODBC effectively, so my next plan of action would be to script an automatic import from my database into moodle. Is there a repository table I can import my data and then fire off a script that will manupulate that data into the rellevant related tables?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Howard Miller - Friday, 22 June 2007, 5:15 PM

Jalil,

I'm not ignoring you! I've just never used ODBC from PHP - I guess not many other have either.

adodb just harnesses the underlying PHP functions for whatever type of connection you use, so it helps to be familiar with how they work. Have you read the ODBC documentation?

http://uk.php.net/odbc

or digging about in Google:

http://www.phpfreaks.com/tutorials/61/0.php

http://phplens.com/phpeverywhere/node/view/9

http://bryanmills.net:8086/archives/2003/11/microsoft-access-database-using-linux-and-php/

Hopefully one of these might give you a clue.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jalil K - Friday, 22 June 2007, 5:26 PM

Thanks Howard for the links,

I'll give it a couple of more days before I throw in the towel, if I get a small db working, I'll try and document for further users.....

Cheers

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Helen Foster - Thursday, 21 June 2007, 5:00 PM

A big THANK YOU to everyone for all the explanations and for improving the documentation. approve

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Lionel MOLLARD - Friday, 15 February 2008, 1:14 AM

Hi everybody,

I'm sorry, but i still can't configure successfuly this feature, with all the explanations found.

I've spent a lot of time to read the forums and websites dealing with database enrolment, with no success for me.

I have a Microsoft Access database containing some fields including the user name, the course short name...

How do i have to set it up in the Moodle's enrolment plugins configuration. What do I have to fill in these fields ?

enrol_dbtype:
enrol_dbhost:
enrol_dbuser:
enrol_dbpass:
enrol_dbname:
enrol_dbtable:

I've tried many conbinations, with no success.

Can you help me please ?

Thanks a lot in advance.

Lionel

Average of ratings: -

Re: Someone explain external database enrolment, please??

by HJWUCGA INC. - Wednesday, 9 July 2008, 4:59 AM

I too am having problems. It seems simple enough but the documentation is not that "specific". Perhaps someone can help us out here.

1. The fields under the "Enrolment (remote) database fields" section, what table or tables are they referring to?

2. From number 1 above, what are the field names associated with these table(s)?

3. could someone provide us a sample of what the tables in the enrol_localcoursefield, enrol_localuserfield, enrol_db_localrolefield may look like FROM THE table you mentioned?

This should help us visualize the mapping from the moodle db to an external one.

4. After the fields are mapped, do we simply just run https://www.mywebsite.com/enrol/database/enrol_database_sync.php ?

5. Where do we check to make sure the enrolment is inserted? mdl_role_assignments?

here's my sample set

+---------+----------+------+
| user | course | rol |
+---------+----------+------+
| adavis | testdbreg | 5 |
| brando | testdbreg | 5 |
| rdeniro | testdbreg | 5 |
| hford | testdbreg | 5 |
| cbale | testdbreg | 3 |
+---------+----------+------+

Thanks

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Chris Bucin - Thursday, 10 July 2008, 6:39 PM

Hi everyone.

I too am a bit confused by the external enrollment plugin.

Could somebody confirm if this will allow me to assign a group to each student in addition to the course and roll. I can't see anywhere in the setup that allows this. The application I am using moodle for requires everyone to be in a group so without being able to do that I will have to look into another solution or hack that support into the script.

Any advice is greatly recommended.

Many thanks,

Chris

Average of ratings: -

Re: Someone explain external database enrolment, please??

by todd vainisi - Tuesday, 21 October 2008, 10:34 PM

I have this same issue... I have a seperate subscription site, when a user signs up there, I want to also create the proper user records in moodle, register them to a group, and enroll them in a specific course. I am not able to "pre-supply" moodle with the user records.

I am beginning to think it would be a lot easier to just add the proper records using my own script. However, although I can find the table that connects users to groups (and thereby courses, I suppose) I cannot find a table that directly links users and course enrollments. I would expect to see a table somewhere that has userid and courseid. All I can find so far, as I said is the combination of groups and groups_members.

Can anyone shed some light on how I should do this?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jordi Martínez - Monday, 27 October 2008, 8:28 PM

Hi,

On 1.8+ you can find mdl_groups_members table with groupid and userid fields.

For courses you have mdl_groups_courses_groups with groupid and courserid fields.

Is that what you need?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by todd vainisi - Monday, 27 October 2008, 10:32 PM

But what table is used when there is no group?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Jordi Martínez - Friday, 31 October 2008, 5:35 PM

Hi tood,

Sorry but I'm not a specialist of Moodle DB.

I was searching and I found some enrol information on mdl_course_display (there you can find a relation between courseid and userid) but I'm not sure if it's enough to enroll an user (or it's not relevant).

It's a clue but you'll have to continue searching.

Sorry, I've done I could.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by John Andrewartha - Monday, 19 April 2010, 6:48 AM

Hi,
I to seem to have a small problem with external database.
I managed to get the Joomla Moodle working. I am angeling for a sinlge signin.

I can authenticate into Moodle from Joomla. The but the user has no role and can't see any courses or catogories.

It appears I need to have the Joomla username etc.. recreated in the Moodle database. How?

John

Average of ratings: -

Can moodle created users be added to external database

by Sarah Hunt - Tuesday, 11 May 2010, 1:09 PM

Can a user created within moodle be inserted into ext DB by this method?

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Sophia Tilson - Friday, 1 October 2010, 11:13 AM

In answer to this part of you're question if you are using Moodle 1.9.9:
Next sentence - "External database enrolment also manages unenrolment. ". This worries me! What exactly does this mean? Does this mean that a student who is not in the database (any more) will be removed from the course? If so, is there any way to stop this? I am thinking about completed courses that I want to keep available in an "archive".

In Moodle 1.9.9, go to site administration, courses, enrolments, then choose to 'edit' external database. Scroll to the bottom of that page and there two options under general options, choose yes to the second option, enrol_db_disableunenrol. This will disable the automatic removal of students.

Average of ratings: -

Re: Someone explain external database enrolment, please??

by David Kendall - Saturday, 2 October 2010, 3:16 AM

Sophia,

We use custom .php scripts to "freeze" the enrolments at the end of each term. This is accomplished by updating the moodle database's mdl_enrol_assignments table setting the enrol field to "manual". This will prevent an enrolment from dropping if we no longer have it in an external database. I can share our .php script if you feel you need more information. So our process is the following:

We name our courses starting with the term (e.g. FA-10_MW4103_A) which then populates that name in the shortname, fullname, and idnumber fields during the external db synch. The teachers can edit the short and full names later.

As we reach the end of the current term, we run the script that changes the enrol field value from "database" to "manual". This prevents the student's enrolment from dropping when we no longer have it in the external db.

At the start of the next term, we run the script to "hide" enrolments from students by updating the visible field value to 0 in the mdl_course table of the moodle db. It's our policy to not allow students to access past resources after the following term starts.

I hope this helps and please let me know if you want a copy of our .php script.

Sincerely,

Dave Kendall

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Sophia Tilson - Friday, 8 October 2010, 10:02 AM

Dave,

Thank you, thats sounds like a useful .php script; it appears you have to manually run this script to make it work at the end of each term (and then the beginning of the next). Is there a way to make this process more automatic, using up less 'tech' time?

On another issue, in our line of business, course completion, pass marks etc. are pretty important to meet company compliance (courses that must be completed). We are setting up an external MYSQL database that will inform Moodle (1.9.9) of the next round of users (that is the easy part), but we want Moodle to then inform this external db of the results (marks, completion dates etc)...Do you know if that's possible (well anything is possible...just haven't worked it out yet), but maybe, has it already been done?

Regards

Sophia Tilson

Average of ratings: -

Re: Someone explain external database enrolment, please??

by David Kendall - Friday, 8 October 2010, 11:51 PM

Sophia,

We have automated the process by creating a function in our SIS's SQL server that returns the start and end dates of our terms and using it in our custom .php scripts. Those scripts are part of the scheduled task that runs the external database synchronization. The custom script compares the results of the function to the current date and performs the appropriate 'freeze" or "hide" depending on whether it's the last day of the current term or the day before the start of the next term.

As for informing the external database of marks, completion dates, etc, we have not done that so I can't offer any help through experience on that. I would assume that if your techs know how to extract and import data into MySQL, that someone could figure it out.

Hope this helps!

Dave Kendall

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Katy Butterworth - Wednesday, 1 June 2011, 10:10 AM

Hi there

I am trying to get moodle 2.0 to connect to our filemaker database. I am having no luck and am not sure where to even look for the errors to see if it is making a connection or not. I am not sure I have even got the right driver to connect to fm? Moodle is on a linux box. Filemaker databases served on FM Server Advanced v11. I was hoping Howard might be able to help me troubleshoot as it appears he is also integrating with filemaker? Are you able to advise what driver you are using please and how you see any connection error logs? A newbie!

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Alick Brown - Saturday, 6 August 2011, 7:18 AM

I am trying to get moodle 2.0 to connect to our filemaker database. I am having no luck....

Me too. Anyone able to share a success story?

Thanks

Alick

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Fatema Siddiqua - Wednesday, 23 January 2013, 3:28 PM

Hello everybody, I am using Moodle 2.2.6 and trying to do external database enrolment. I have Externel Database as Oracle in my local PC. After Reading the doc I tried and did the following settinds:

Databse driver: ODBC-ORACLE

Database Host: 10.32.1.246

Local field mapping

Local course field: idnumber

Local user field: idnumber

Local role field: shortname

For Remote Enrolment Sync:

Remote user enrolment table: ad_student_msts

Remote course field: COURSE_CODE

Remote user field: SOL_ROLL_NO

Remote role field: BLANK

For Plugins> Authentication>External Database, I have done the following settings:

Host:10.32.1.246

Database: ODBC_ORACLE

DB name: orcl

DB user:dusol

Table: ad_student_msts

Username field: SOL_ROLL_NO

Password field: EMAIL_ID

Password format: Internal

rest all to default...

Then I have also manually run cron.php

Now, Its showing 0/0 instances/enrolment for external databe in Manage Enrol Plugins.

I think, it should now send an e-mail for password to users email id. only after that a user may be able to login (But did not received any mail).

Is there any other way to check that connection have been made or not?

I am a newbie in Moodle and stucked badly with external enrolmant plugins.

Plzz someone help me with step by step documentation. I am using xampp on windows OS.

Thanking You All in Advance......

Average of ratings: -

Re: Someone explain external database enrolment, please??

by Trevor Wilson - Wednesday, 27 February 2013, 12:00 AM

Hi, Fatema

I think you'd need to map ID number in Authentication>External Database to SOL_ROLL_NO so it can match entries in the user table.

Trevor

Average of ratings: -