Moodle

Enrolment using remote database does not work if remote database fields are not in upper case

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.9
  • Fix Version/s: None
  • Component/s: Enrolments
  • Labels:
    None
  • Environment:
    Database for Moodle itself - MySQL,
    Remote database for enrolments Oracle10, using enrol_dbtype - oci8po (set on administration page Courses->Enrolments->External Database (edit) )
  • Affected Branches:
    MOODLE_19_STABLE

Description

We are using external database (Oracle) for enrolments. When launcing enrol/database/enrol_database_sync.php (from command line), few error messages appeared that object does not contain specified fields.
Example -
"PHP Notice: Undefined property: stdClass::$kkods in /home/moodle/moodle/enrol/database/enrol.php on line 260"
"PHP Notice: Undefined property: stdClass::$login in /home/moodle/moodle/enrol/database/enrol.php on line 317"

We have added functionality to script enrol/database/enrol.php. Lines of code in original Moodle v1.9 enrol/database/enrol.php are 225, 282 respectively.
$extcourse = $extcourse_obj->{$CFG->enrol_remotecoursefield}; - #225
array_push($extenrolments, $crs_obj->{$CFG->enrol_remoteuserfield}); - #282

These errors disappeared and script worked fine when on enrollments administration page Courses->Enrolments->External Database (edit) (admin/enrol_config.php?enrol=database) remote database fields "enrol_remotecoursefield" and "enrol_remoteuserfield" were saved in upper case.

There was no need to save remote database fields in v1.8 in uppercase. Possibly it this bug deals with new version of adodb database drivers in Moodle v1.9 comparing with drivers used in Moodle v1.8.

Issue Links

This issue duplicates:
MDL-16043 Enrolment sync need to be cross-db Minor Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Petr Škoda (skodak) added a comment -

I guess we should add our ORA hacks there too, right Eloy?

Show
Petr Škoda (skodak) added a comment - I guess we should add our ORA hacks there too, right Eloy?
Hide
Permalink
Alan Barrett added a comment -

I have been debugging a similar/the same issue: http://moodle.org/mod/forum/discuss.php?d=102993

However there is an additional problem not solved by the above workaround...

The line "if ($rs = $enroldb->Execute("SELECT {$CFG->enrol_remotecoursefield} as enrolremotecoursefield ..."
dynamically generates the field enrolremotecoursefield in upper case as ENROLREMOTECOURSEFIELD!!!
So I had to add the following six lines to enrol.php...

if (empty($fields_obj->enrolremotecoursefield)) {
$courselist[] = $fields_obj->ENROLREMOTECOURSEFIELD;
}
else {
$courselist[] = $fields_obj->enrolremotecoursefield;
}

to replace the single line...
$courselist[] = $fields_obj->enrolremotecoursefield;

It is not my system that has the problem and I am sending debug files to a user so I am not completely clear on this but it seems that the problem occurs on all of the following drivers...
ORACLE using the odbc driver and the odbc_oracle driver
MSSQL?? using the ODBC driver and the ODBC_MSSQL driver

I believe the ultimate problem is caused by preconfigure_dbconnection() in setuplib.php. It sets the ADODB_ASSOC_CASE as appropriate for the main Moodle database. However if an external database is being used (e.g. Oracle via ODBC) then the value of ADODB_ASSOC_CASE may not be appropriate for that database (which might need ADODB_ASSOC_CASE=0 to force lower case).

I have not yet figured out how to deal with this issue. I am not even sure it is the root cause of the problem, because I do not have direct access to the system with the problem. I a still working on this.

Alan.

Show
Alan Barrett added a comment - I have been debugging a similar/the same issue: http://moodle.org/mod/forum/discuss.php?d=102993 However there is an additional problem not solved by the above workaround... The line "if ($rs = $enroldb->Execute("SELECT {$CFG->enrol_remotecoursefield} as enrolremotecoursefield ..." dynamically generates the field enrolremotecoursefield in upper case as ENROLREMOTECOURSEFIELD!!! So I had to add the following six lines to enrol.php... if (empty($fields_obj->enrolremotecoursefield)) { $courselist[] = $fields_obj->ENROLREMOTECOURSEFIELD; } else { $courselist[] = $fields_obj->enrolremotecoursefield; } to replace the single line... $courselist[] = $fields_obj->enrolremotecoursefield; It is not my system that has the problem and I am sending debug files to a user so I am not completely clear on this but it seems that the problem occurs on all of the following drivers... ORACLE using the odbc driver and the odbc_oracle driver MSSQL?? using the ODBC driver and the ODBC_MSSQL driver I believe the ultimate problem is caused by preconfigure_dbconnection() in setuplib.php. It sets the ADODB_ASSOC_CASE as appropriate for the main Moodle database. However if an external database is being used (e.g. Oracle via ODBC) then the value of ADODB_ASSOC_CASE may not be appropriate for that database (which might need ADODB_ASSOC_CASE=0 to force lower case). I have not yet figured out how to deal with this issue. I am not even sure it is the root cause of the problem, because I do not have direct access to the system with the problem. I a still working on this. Alan.
Hide
Permalink
Alan Barrett added a comment -

I have come up with a proper solution. Rather than work around ADODB_ASSOC_CASE having the wrong value for the ODBC connection, I have extended the above fix to temporarily UPPERCASE the Object Property names used to access the Record Object. The Property names come from $CFG->enrol_remotecoursefield and $CFG->enrol_remoteuserfield).

So I replaced the line...
$extcourse = $extcourse_obj->{$CFG->enrol_remotecoursefield};
with...
if (empty($extcourse_obj->{$CFG->enrol_remotecoursefield})) {// Oracle might return field name in UPPERCASE!
$extcourse = $extcourse_obj->{strtoupper($CFG->enrol_remotecoursefield)};
}
else {
$extcourse = $extcourse_obj->{$CFG->enrol_remotecoursefield};
}

AND I replaced the line...
array_push($extenrolments, $crs_obj->{$CFG->enrol_remoteuserfield});
with...
if (empty($crs_obj->{$CFG->enrol_remoteuserfield})) { // Oracle might return field name in UPPERCASE!
array_push($extenrolments, $crs_obj->{strtoupper($CFG->enrol_remoteuserfield)});
}
else {
array_push($extenrolments, $crs_obj->{$CFG->enrol_remoteuserfield});
}

AND for completeness I include the change made in the above comment, replace the line...
$courselist[] = $fields_obj->enrolremotecoursefield;
with...
if (empty($fields_obj->enrolremotecoursefield)) { // Oracle might return field name in UPPERCASE! $courselist[] = $fields_obj->ENROLREMOTECOURSEFIELD; }
else { $courselist[] = $fields_obj->enrolremotecoursefield; }

The 3 changes are included in the attached enrol.php (from the 20080813 Moodle 1.9.2 Weekly build).

This fix is safe because it is only triggered if the expected object property does not exist and then it assumes that the reason for the code failure is that Oracle or some other culprit has returned the record with the column names in upper case.

NOTE: For this fix the administrator does NOT need to change the (Courses->Enrolments->External Database) remote database fields to UPPERCASE (e.g. "enrol_remotecoursefield" and "enrol_remoteuserfield"). This will avoid people hitting the bug and then having to try workarounds.

I believe the reason that this bug did not appear in Moodle 1.8 was because of the setuplib.php changes for ADODB_ASSOC_CASE.

I probably should have used isset() but I have tested with empty().

Alan.

Show
Alan Barrett added a comment - I have come up with a proper solution. Rather than work around ADODB_ASSOC_CASE having the wrong value for the ODBC connection, I have extended the above fix to temporarily UPPERCASE the Object Property names used to access the Record Object. The Property names come from $CFG->enrol_remotecoursefield and $CFG->enrol_remoteuserfield). So I replaced the line... $extcourse = $extcourse_obj->{$CFG->enrol_remotecoursefield}; with... if (empty($extcourse_obj->{$CFG->enrol_remotecoursefield})) {// Oracle might return field name in UPPERCASE! $extcourse = $extcourse_obj->{strtoupper($CFG->enrol_remotecoursefield)}; } else { $extcourse = $extcourse_obj->{$CFG->enrol_remotecoursefield}; } AND I replaced the line... array_push($extenrolments, $crs_obj->{$CFG->enrol_remoteuserfield}); with... if (empty($crs_obj->{$CFG->enrol_remoteuserfield})) { // Oracle might return field name in UPPERCASE! array_push($extenrolments, $crs_obj->{strtoupper($CFG->enrol_remoteuserfield)}); } else { array_push($extenrolments, $crs_obj->{$CFG->enrol_remoteuserfield}); } AND for completeness I include the change made in the above comment, replace the line... $courselist[] = $fields_obj->enrolremotecoursefield; with... if (empty($fields_obj->enrolremotecoursefield)) { // Oracle might return field name in UPPERCASE! $courselist[] = $fields_obj->ENROLREMOTECOURSEFIELD; } else { $courselist[] = $fields_obj->enrolremotecoursefield; } The 3 changes are included in the attached enrol.php (from the 20080813 Moodle 1.9.2 Weekly build). This fix is safe because it is only triggered if the expected object property does not exist and then it assumes that the reason for the code failure is that Oracle or some other culprit has returned the record with the column names in upper case. NOTE: For this fix the administrator does NOT need to change the (Courses->Enrolments->External Database) remote database fields to UPPERCASE (e.g. "enrol_remotecoursefield" and "enrol_remoteuserfield"). This will avoid people hitting the bug and then having to try workarounds. I believe the reason that this bug did not appear in Moodle 1.8 was because of the setuplib.php changes for ADODB_ASSOC_CASE. I probably should have used isset() but I have tested with empty(). Alan.
Hide
Permalink
Alan Barrett added a comment -

enrol/database/enrol.php that fixes the UPPERCASE bug for Moodle 1.9 ODBC to Oracle

Show
Alan Barrett added a comment - enrol/database/enrol.php that fixes the UPPERCASE bug for Moodle 1.9 ODBC to Oracle
Hide
Permalink
Alan Barrett added a comment -

This is fixed by MDL-16043

Show
Alan Barrett added a comment - This is fixed by MDL-16043
Hide
Permalink
Iñaki Arenaza added a comment -

This is a duplicate of MDL-16043. As that one is fixed, we should mark this one as fixed too

Saludos.
Iñaki.

Show
Iñaki Arenaza added a comment - This is a duplicate of MDL-16043. As that one is fixed, we should mark this one as fixed too Saludos. Iñaki.

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.5#665-sha1:422aead) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.