Databases: SQL Server Adaptation

Databases: SQL Server Adaptation

by Hakki Ocal -
Number of replies: 10

We are working to make the Moodle work with SQL Server (7 and above) and rewrote the SQL script for Enterprise Manager. (It is here: http://www.hocal.net/moodle_sql.rar )

Moodle now can open the DB, looks at the tables. tries to retrieve the data; as ADODB would like to help her; but at one point--I *think*--a hard-coded fanaticism tongueout.gif (only a joke at the expense of the Linux-MySQL crowd!) interferes, as in here:

http://www.hocal.net/moodle_error_2.gif

I am not right! OK, then where is my mistake?

You want to see our config and setup files? They are here:

http://www.hocal.net/viu_config_setup_php.rar

PHP *works* with anything! Why shouldn't Moodle?

Thanks for your help..

Average of ratings: -
In reply to Hakki Ocal

Re: Databases: SQL Server Adaptation

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
No hard-coded fanaticism here. smile

1) You need to turn on short_tags in your PHP settings (see Install docs).

2) There's still a lot of SQL you need to translate: see mod/*/db/mssql.sql

3) You should also make accompanying mssql.php files (in lib/db and mod/*/db)

Cheers,
Martin
Average of ratings: -
In reply to Martin Dougiamas

Re: Databases: SQL Server Adaptation

by Hakki Ocal -

I take this is also just a statement of fact:

"If you're on Linux or another Unix then I'll assume you can figure all this out for yourself! wink "  (moodle.org -> Moodle Documentation -> Installing Moodle)

Anyway, thanks for the tips..

We will do our best and let you know about the results we get.

Thanks again.

 

Average of ratings: -
In reply to Hakki Ocal

Re: Databases: SQL Server Adaptation

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
A statement of fact? No, just a joke by the author in the absence of any better documentation. (See this link about some moves to improve the documentation for beginners on any platform http://moodle.org/mod/forum/discuss.php?d=1163)

Good luck with porting the database setup to MS SQL - if you send me the additional files I can add them to the distribution.
Average of ratings: -
In reply to Martin Dougiamas

Re: Databases: SQL Server Adaptation

by Lynne Barkett -

I'm modifying the sql files for sql server. All seems to be going well, I'm slowly working my way through them. And then I come to the forum module and the prefix_forum definition:

type enum('single','news','general','social','eachuser','teacher') NOT NULL default 'general',

enum is not a valid column type in sql server. Oh dear. Any suggestions? All I find is that you can use bit and use only two possible values (0 and 1).

I'm going to try varchar, but I don't know if this will work...

Have I found the piece of code that is going to rule out the use of MS SQL Server?

Lynne Cooney-Barkett
United States Sports Academy
http://www.ussa.edu

This message was cross-posted to "Installation Problems"

Average of ratings: -
In reply to Lynne Barkett

Re: Databases: SQL Server Adaptation

by Lynne Barkett -

After all my modifications to the .sql and .php files in /lib/db and /mod/*/db, I get a far as the initial tables being created. Then I wind up with:

Notice: Undefined property: value in D:\Inetpub\DELPHI\moodle\lib\setup.php on line 73

Average of ratings: -
In reply to Lynne Barkett

Re: Databases: SQL Server Adaptation

by Lynne Barkett -

OK, past that, now I'm getting:

Moodle 1.1.1

ERROR: Could not update release version in database!!

Continue

Average of ratings: -
In reply to Lynne Barkett

Re: Databases: SQL Server Adaptation

by Lynne Barkett -
And now I'm back to the error in my previous post...
Average of ratings: -
In reply to Lynne Barkett

Re: Databases: SQL Server Adaptation

by Les Kopari -
Have you tried using a constraint to replace the enum?

Looks like you're using an example from  mod/forum/db/mysql.sql:

    type enum('single','news','general','social','eachuser','teacher')

which I changed in the Oracle version, mod/forum/db/oci8po.sql to:

    type varchar2(64) default 'general' not null,
    constraint type_check CHECK (type IN (
    'single','news','general','social','eachuser','teacher')),

It seems to work in my test cases with Oracle SQL, but don't have Moodle
running with it yet, so you would need to test that out with your SQL Server
yourself.

Please let me know how turns out for you.

Les.
Average of ratings: -
In reply to Les Kopari

Re: Databases: SQL Server Adaptation

by Lynne Barkett -

I basically gave up and decided to use MySQL. I did replace that one column def with constraints, but that wasn't the only issue. There was another that used "open" as a column name, which is not allowed in SQL Server. No telling how much moodle code changing the name was going to break. I decided that if I absolutely HAD to have the data in SQL Server for other apps (and I will need it there), then I was better off using my time writing stored procedures to mirror the data. Moodle is just in demo mode at USSA right now, anyway.

It gets even better: our student IDs are stored in a Paradox table. I tried every possible way to connect to it from moodle. I tried just writing my own PHP to connect. I manage to connect successfully to the table about once every 5 minutes. Sigh. So I wrote ASP to do the query, which I call from PHP. Ugly? Yes, it is. But it works.

Average of ratings: -
In reply to Lynne Barkett

Re: Databases: SQL Server Adaptation

by Les Kopari -
> There was another that used open as a column name, which is not allowed  in
> SQL Server.

Similar problems converting to Oracle...wonder if we should upload these somewhere to
form a checklist.  I've got one for the conversion process from MySQL to Oracle, but
the problem there is 30-character names for MySQL's 64 character...haven't found a
workaround for that one that doesn't require changing Moodle code.

> So I wrote ASP to do the query, which I call from PHP.

This sounds like something that might be generally useful to other Moodlers...wonder
if you can sanitize it and upload it here in case someone else has a similar config.

> Ugly? Yes it is. But it works.

Sounds like you got the job done..

Thanks for the follow-up.

Les.
Average of ratings: -