Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -
Number of replies: 34
I've updated the previously posted patch and tested it with 1.4.3+(3/1/05) and 1.4.4 and everything seems to be working fine. The mainchange was the addition of the SQL script and upgrade PHP script forpaypal enrolment, somehow I had skipped those database scriptsoriginally.

The only reason I'm posting this as a new post instead of replying to the other is so that 1.4.4 will be in the title.

Notes from the original post:

I've created a new MSSQL patch for Moodle for 1.4.3+ (3/1/05 download) or 1.4.4 and it will probably work with any 1.4.3 version.

Overall testingof the patch has been light but it appears to be working properly in ourenvironment and we will be running it on a fairly active server soon.I'd appreciate it if you all would notify me of any bugs you find inthis version. This version will upgrade cleaning from the 1.2.1 MSSQLversion and will also work as a clean install of 1.4.3+ or 1.4.4.

Directions:
  • With a new Moodle MSSQL install you'll need to create a new database for moodle and a new user and give that user db_owner access to that database.
  • Download Moodle 1.4.3+ or 1.4.4 and extract it to some directory.
  • Download the patch and extract it to the Moodle directory.
  • Download patch for Windows if you don't have it (http://gnuwin32.sourceforge.net/packages/patch.htm).
  • In the Moodle directory run patch -p1 < Moodle_1.4.3+_MSSQL_v2.patch
  • Configure and run Moodle as per standard Moodle instructions.
The patch is attached to this post.

Thanks,
E.J. Wilburn
ej_wilburn@kofax.com
Kofax Image Products
www.kofax.com
Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by leslie nielson -

hi

I donwloaded 1.4.4 and applied your patch without any problems .

However when i set the dbtype to ado_mssql i only get blank pages surprise are there any other settings i need in order to use ado ?
If i change your lib/db/ado_mssql.sql and .php to mssql.sql and .php i get as far as the variables page where im stuck with the same error again as in my previous post http://moodle.org/mod/forum/discuss.php?d=22567 

thx

Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -
Here's an example of my config section:

$CFG->dbtype    = 'ado_mssql';       // mysql, postgres7 or mssql (for now)
$CFG->dbhost    = '<database server>';   // eg localhost or db.isp.com
$CFG->dbname    = 'moodle';      // database name, eg moodle
$CFG->dbuser    = 'moodle';    // your database username
$CFG->dbpass    = '<password>';    // your database password
$CFG->dsn        = 'PROVIDER=SQLOLEDB;Data Source='.$CFG->dbhost.';Initial Catalog='
                  .$CFG->dbname.';User ID='.$CFG->dbuser.';Password='.$CFG->dbpass.';';
$CFG->prefix    = 'mdl_';        // Prefix to use for all table names
//$CFG->debug     = 100;
$CFG->dbpersist = false;         // Should database connections be reused?

Here are the extensions I'm loading in php.ini:

extension=php_bz2.dll
extension=php_db.dll
extension=php_gd2.dll
extension=php_mssql.dll

You'll also need MDAC installed on the server sysetm (or verify that you have the ability to create a SQL Server ODBC that works on that system).

Also, turn on all errors in PHP by setting the following line:

error_reporting  =  E_ALL

The dbtype must be ado_mssql, not mssql.

You should also verify that the database name you've provided is a valid database on the server you listed and that the user you listed has access to that database and is setup with the db_owner role in that database and that the password you provided is correct.

Thanks,
E.J. Wilburn
ej_wilburn@kofax.com
Kofax Image Products
Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by leslie nielson -

it looks like he cant create the initial tables or something mixed
once the first tables are there he will install just fine but untill then i just get blank pages

weird

thx

Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -
That's very odd.  My testing was done in these steps for both 1.4.3+ and 1.4.4:

1)  Create SQL user "moodle".
2)  Create SQL database "moodle", give "moodle" user db_owner permissions to this database.
3)  Extract the moodle release.
4)  Apply the patch.
5)  Configure database settings in config.php.
6)  Launch moodle.

That's worked with both releases and I haven't run into any problems at all creating tables.
Average of ratings: -
In reply to E.J. Wilburn

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr.Wilburn

Thank you for your patch for installing Moodle with MS SQL Server. I am trying to install Moodle on the following configuration:

1. Windows 2000 machine (moodle runs in this)
2. MS SQL Server 2000 running on a Windows Server 2003 (the database resides here)
3. IIS 5.0

The patch runs successfully but when I run "http://<machine>/moodle/install.php" I get the following error.

Fatal error: main(): Failed opening required '<drive>Inetpub\wwwroot\moodle_mssql\lib\adodb\adodb.inc.php' (include_path='.;c:\php4\pear') in <drive>Inetpub\wwwroot\moodle_mssql\install.php on line 81

I have checked permissions on adodb and lib folders and everything seems to be ok. I hope you can throw some light on this.

Thanks in advance

Manu

Average of ratings: -
In reply to CPD Student Assistant

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by E.J. Wilburn -

One thing you might do is check the permissions on the adodb.inc.php file directly.  Install.php wasn't modified by my patch but that file was.  I believe I remember hearing reports that some win32 versions of patch can change file permissions on patched files.  Verify that the anonymous IIS user has read access to that file.

-E.J. Wilburn

Average of ratings: -
In reply to E.J. Wilburn

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr. Wilburn

Thank you for your quick reply. Sorry I made a mistake in one of the emails, its http://<machine>/moodle_mssql/install.php on both instances. However I will try to check the permissions on this adodb file and also check the config file and get back on this.

Thanks again

Manu
Average of ratings: -
In reply to CPD Student Assistant

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr. Wilburn

I did get past the "failure to open streams" error and was able to get to the stage where I have to configure the database connection. I already have an MSSQL 2000 box with a new database "moodle" created (without any tables) and my account has owner access to this database. However the database configuration page of moodle does not show MSSQL as an option. Just the old ones MySQL and Postgres. I was assuming your patch would make MSSQL an option in the installation drop down menu , is this the case?

Or should I be creating a config.php and then proceeding with the installation process (having put all the db info into the config.php beforehand) ?

Any help on this would be greatly appreciated

Manu
Average of ratings: -
In reply to CPD Student Assistant

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by E.J. Wilburn -
Ah, I forgot about that config builder screen.  Just look at the config.php-dist (or whatever the dist file is) and use the comments to build your config.php file by hand.
Average of ratings: -
In reply to E.J. Wilburn

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr. Wilburn

I was able to to install the MSSQL patch and use moodle with a MSDE SQL but when I try to connect to a remote SQL server (2000 database) i get a

"Error: Database Connection Failed
It is possible that the database is overloaded or otherwise not running properly
The site administrator should also check that the database details have been correctly specified in config.php"

Could you throw some light on this. I tried connecting using my connection parameters in another test.php file and it connected to the same remote database ok.

Thanks

PS: I tried tracing the error but couldnt find out which files were being called from index.php. I checked adodb.inc.php and there is a reference to $this->-nconnect function whose definition I could not locate.
Average of ratings: -
In reply to CPD Student Assistant

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by E.J. Wilburn -

I don't know what the issue is, perhaps the user you're logging in as doesn't have permission to access the moodle database?  Maybe a firewall is enabled on the server that's blocking connections?  Can you run osql from the client and log into the server as your moodle user and then use your moodle database? ie:

osql -U<moodle user> -S<remote server>

<connected>

use <moodle database name>

go

If all of that works fine then it's probably something odd with your connection settings in config.php.  Could you post your config.php (without the db password of course)?

There's no reason beyond server configuration issues that you'd be able to get moodle running with SQL Server (MSDE or PE) on your local system but be unable to make it work with a remote SQL Server as it's using the exact same connection procedure with the only difference being a physical network connection and server configuration issues.

Average of ratings: -
In reply to E.J. Wilburn

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr Wilburn

Thank you for the response. I am exploring potential problems with server config with our network administrator now. In the meantime I am pasting the config file below.

$CFG->dbtype    = 'ado_mssql';       // mysql, postgres7 or ado_mssql (for now)
$CFG->dbhost    = '192.168.0.18';   // eg localhost or db.isp.com
$CFG->dbname    = 'moodle';      // database name, eg moodle
$CFG->dbuser    = 'moodle';    // your database username
$CFG->dbpass    = '<password>';    // your database password
$CFG->dsn        = 'PROVIDER=SQLOLEDB;Data Source='.$CFG->dbhost.';Initial Catalog='
                  .$CFG->dbname.';User ID='.$CFG->dbuser.';Password='.$CFG->dbpass.';'; // DSN used for ado_mssql
$CFG->prefix    = 'mdl_';        // Prefix to use for all table names

Manu
Average of ratings: -
In reply to CPD Student Assistant

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by E.J. Wilburn -
Does the SQL Server have an instance name?  An easy way to verify wether it does or not is to open up Services (Start->Settings->Control Panel->Administrative Tools->Services) and see if it's listed as "MSSQL$<Instance Name>" or "Microsoft SQL Server".  If it's the first then you'll need to specify that instance name in your config.php (ie:  $CFG->dbhost = 'server/instance_name'; ).  If it doesn't have an instance name and you're still having problems try changing your dbhost line to use a real host name instead of an IP address.
Average of ratings: -
In reply to E.J. Wilburn

Re: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4 - Problem with this

by CPD Student Assistant -
Mr. Wilburn

I am happy to inform you that I got the patch working. Folks who run the server had an elaborate security setup and it required opening up some ports for Moodle connections I guess. Anyways now we got it working and then I hit another snag - when I try to Install the book module it does not have the mssql files in the mod/book/db directory. Have you got it working with the Book module? Is there some config issue I am missing?

Thanks in advance

Manu
Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by CPD Student Assistant -

I was wondering if anobody was using Moodle 1.4 with MSSQL 2000 and PHP 5.0.4 -

The configuration works fine with Moodle 1.4 with MySQL and PHP 5.0.4 (on the same machine I tried MSSQL with). I just get blank pages. I am able to run it successfully w/ MSSQL 2000 when I use Php 4.

I would be interested if anybody got this working, or if anyone is having similar problems.

Thanks

Manu

Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by leslie nielson -

i was wondering if everything was working allright for you since i still have alot of problems

  1. i cant assign teachers and/or course creators ( if i manualy add a course creator in the database he does show up in the course creator list but when i want to assign him as a teacher to a course moodle gives the error :
    Could not get users!
  2. chat          i cant read messages, they do get posted to the database correctly but he wont show them in the chat screen(the mysql version does work)
  3. forum        the standard created forum works correct but i cant create a new 1 because he is missing the lower part of the page.
  4. glossary      als o missing lower part of the page (missing save button)
  5. wiki           after saving a new wiki he will give a blank page after hitting refresh he will give a session securite warning asking me if im sure i want to that and returns me to the main page.

im still trying to find fixes for these and will continu to test.

thx

*edit

it seems that when i change the language to english instead of dutch that for example the forum and glossary save button do show up.

Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -
Those language related problems might be issues with the dutch language pack.  I didn't make any language related changes at all and if one language is working and another is not I'd tend to blame the language that's not working rather than this patch.  Regardless, I can't really debug issues with a Dutch language settings since I don't understand Dutch. smile

I'll look into the other issues and see what I can find.

-E.J. Wilburn
ej_wilburn@kofax.com
Kofax Image Products
Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by leslie nielson -

hi

E.J. Wilburn

i downloaded gnuwin32 patch  and i got diffutils now

i have been trying to create my own patch file but have been unable to

can u pls tell me how u create your patch?

Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -

Well, install gnuwin32 patch and make sure it's in your system path.  Copy the patch file to your root moodle directory (ie:  C:\Inetpub\wwwroot\Moodle ).  Open a command prompt and go to your root moodle directory and type:

In the Moodle directory run patch -p1 < Moodle_1.4.3+_MSSQL_v2.patch

That should apply the patch, then you'll need to create your config.php using config.php-dist as your model and you should be ready to go.  Full directions are in my original post.

Thanks,

E.J. Wilburn

Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by leslie nielson -

thx for the reply

but what i ment was how did u create the patch file ?
i have no problem applying the patch but i would like to make my own patch file

thx

Average of ratings: -
In reply to leslie nielson

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by E.J. Wilburn -

Ah, Ok. smile  Here's a good link on the subject: Diff and Patch

Basically you create two directories.  One directory is the original source tree (say the Moodle 1.4.4 release) and the second is the directory with a modified version of that source tree.  You then run something like:

diff -ruN original_source_dir modified_source_dir > patch_file_name.patch

-E.J.

Average of ratings: -
In reply to E.J. Wilburn

This forum post has been removed

The content of this forum post has been removed and can no longer be accessed.
In reply to Deleted user

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by Chris Simpson -

I too would be very interested in a patch for 1.5 or 1.6 to support mssql.

Is there any reason why mssql cannot be a standard option on installing Moodle? 

Php supports mssql, so surely it wouldn't be too difficult (he says!) to implement the installation script of moodle to include this database type.  Adhoc patches may cause issues on updating Moodle to future versions, so would it be possible for the Moodle Development Team to implement this in a forthcoming release?

Please?

Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by Anna Tiddi -
Hi,
I'm very new to moodle and would like to use it with MSSQL 2000. I think I've run the Moodle 1.4.3+ patch, created a new database (moodle), made the moodle user dbo.owner. However, whenever I open moodle (it's installed on my desktop) I get the following message:

"Error: Database connection failed.

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php"

The configuration info is the following:

$CFG->dbtype    = 'ado_mssql';       // mysql or postgres7 (for now)
$CFG->dbhost    = 'localhost';   // eg localhost or db.isp.com
$CFG->dbname    = 'moodle';      // database name, eg moodle
$CFG->dbuser    = 'moodle';    // your database username
$CFG->dbpass    = 'moodle';    // your database password
$CFG->dsn        = 'PROVIDER=SQLOLEDB;Data Source='.$CFG->dbhost.';Initial Catalog='.$CFG->dbname.';User ID='.$CFG->dbuser.';Password='.$CFG->dbpass.';';

$CFG->prefix    = 'mdl_';        // Prefix to use for all table names
$CFG->dbpersist = false;         // Should database connections be reused?

When I look at the phpinfo file, I see the following info for MSSQL:
MSSQL Supportenabled
Active Persistent Links 0
Active Links 0
Library version 7.0

Any idea how I can fix this problem please?

Many thanks.

Anna Tiddi
West Midlands Public Health Observatory
anna.tiddi@wmpho.org.uk
www.wmpho.org.uk


Average of ratings: -
In reply to E.J. Wilburn

Works in test, but not in moodle

by Gregory Krohne -
I'm running the latest version of moodle (v1.5.2, I think). I applied the match as instructed. I'm getting a database error running moodle, even though a test script says the connection is okay. Does anyone know how I could narrow down the problem?

This test script works fine:

<?php
    include('D:\inetpub\wwwroot\moodle\lib\adodb\adodb.inc.php');

    $db = &ADONewConnection("ado_mssql");
    print "<h1>Connecting DSN-less $db->databaseType...</h1>";
    $db->debug=true;
 
    $myDSN='PROVIDER=SQLOLEDB;'
        .'Data Source=host.domain;'
        .'Initial Catalog=moodle;'
        .'User ID=moodle;'
        .'Password=password;'
        ;

    $db->Connect($myDSN);
 
    $rs = $db->Execute("select * from sysusers");
    $arr = $rs->GetArray();
    print_r($arr);
?>

I have the same config as everyone else, but I'll paste it here anyway:

$CFG->dbtype    = 'ado_mssql';
$CFG->dbhost    = 'moodle_host.domain';
$CFG->dbname    = 'moodle';
$CFG->dbuser    = 'moodle';
$CFG->dbpass    = 'password';
$CFG->dsn       = 'PROVIDER=SQLOLEDB;'
                . 'Data Source='.$CFG->dbhost.';'
                . 'Initial Catalog='.$CFG->dbname.';'
                . 'User ID='.$CFG->dbuser.';'
                . 'Password='.$CFG->dbpass.';'
                ;
$CFG->prefix    = 'mdl_';

As you can see, the dsn is exactly the same, but moodle still throws:

Error: Database connection failed.

Ideas? Thoughts? Suggestions?

Average of ratings: -
In reply to Gregory Krohne

Re: Works in test, but not in moodle

by Gregory Krohne -
Here is part of the problem: My setup.php doesn't look for $CFG->dsn

In lib\setup.php, I made the following change:

old:

    if (!isset($CFG->dbpersist) or !empty($CFG->dbpersist)) {    // Use persistent connection (default)
        $dbconnected = $db->PConnect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    } else {                                                     // Use single connection
        $dbconnected = $db->Connect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    }

new:

    if (!empty($CFG->dsn)) {    // For ADO MS SQL Server connections
        $dbconnected = $db->Connect($CFG->dsn);
    } else if (!isset($CFG->dbpersist) or !empty($CFG->dbpersist)) {    // Use persistent connection (default)
        $dbconnected = $db->PConnect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    } else {                                                     // Use single connection
        $dbconnected = $db->Connect($CFG->dbhost,$CFG->dbuser,$CFG->dbpass,$CFG->dbname);
    }


That change alone allows moodle to create the initial tables, but I still get syntax errors in the 'ALTER TABLE' commands.
Average of ratings: -
In reply to Gregory Krohne

config.php

by Gregory Krohne -
These are the setup files I've been pounding away on all day. I haven't gotten moodle to finish upgrading, though it isn't reporting errors anymore.

The first one is config.php

Average of ratings: -
In reply to Gregory Krohne

datalib.php

by Gregory Krohne -
datalib.php
Average of ratings: -
In reply to Gregory Krohne

Re: datalib.php

by Gregory Krohne -
Further update required. This update eliminates these errors in datalib.php:

Undefined property: stdClass::$name

Undefined property: stdClass::$value

ado_mssql apparently doesn't return an array the way that datalib.php is expecting. I added a check for ado_mssql to produce the expected results.

Old:

850 $objects[$key] = (object) $record;

New:

850 if ($CFG->dbtype === 'ado_mssql') {
851 $objects[$key] = (object) Array("name" => $record[0], "value" => $record[1]);
852 } else {
853 $objects[$key] = (object) $record;
854 }
Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by Langdon Stevenson -
Hi E.J.

I have only recently gotten back to testing the MS SQL Server Patch with Moodle 1.4.4 and have come across a strange and consistant problem (it happen on different machines, one Win XP with Apache, one Win2K with IIS, both with SQL Server 200).  I posted it to the install forum, but realise now that I should probably have posted it here instead.

Problem:

All administration actions performed with users (making them administrators, creators, teachers, student) doesn't work.  Moodle doesn't appear to be able to retrieve users from the database to populate the lists in each of the admin screens for assigning the users to their role. 

As mentioned elsewhere in this thread I get the error: "Users not found".

Do you have any suggestions as to why this might be, and where I would start looking to track down the problem?

Any assistance you can offer is greatly appreciated.

Regards,
Langdon
Average of ratings: -
In reply to E.J. Wilburn

Re: Databases: Updated MSSQL Patch for Moodle 1.4.3+ and 1.4.4

by Chris Denney -

E.J.

Any idea if your patch will work with v1.5.2?

I have an existing SQL server I'm wanting to use instead of MySQL.

-Chris

Average of ratings: -