Moodle

Ciao Eloy
wherever I go I finish knocking at your door.
Nice to meet you again.
As always, I am available for every further investigation and/or request.
Ciao.

worked fine for me about two weeks ago with mssql_n driver

this could be a driver issue too, please supply full version details from the affected installation:

• php version
• apache or IIS version
• cgi, isapi, etc
• os version
• mssql driver type
• sql server version
• freetds vesion if used
• etc.

Without these details there is no easy way to diagnose this, sorry

I am far from my town. I'll post my answer next monday.
Thank you.

Follows the list of informations I received from the friend who manage the server.

• php version
  PHP 5.2.5

• apache or IIS version
  IIS 6.0

• cgi, isapi, etc
  ISAPI php5isapi.dll

• os version
  Microsoft Windows Server 2003 R2 Standard Edition

• mssql driver type
  FreeTDS

• sql server version
  SQL Server 9.00.3054.00 (2005 Enterprise Edition)

• freetds vesion if used
  FreeTDS 7.0

Ciao.

Hi,

I see that this has not been fixed yet. I'm here at the University of WInchester today, and they have a setup with a similar spec to that listed above - and trying to set up a new moodle we have exactly the same problem - new settings screen keeps cycling around, as if it is an upgrade, every time we visit the notifications screen.

I've followed the MSSQL instructions in moodle docs, ebsuring that the database has the required settings and that the relevant settings in PHOP have been done.

But we just keep getting this problem.

Sean K

I agree with you Sean
this issue is still a problem for my institute too.
I just leaved the problem there.
I don't know any workaround.

OK, this is something to do with Nulls in the database. Even though I have the database set up so that ANSI NULLS Enabled = true and Quoted Identifiers Enabled = True (as per the instructions) if a field in mdl_config contains a null, it keeps coming up in the "New Settings" screen under notifications. If you edit the table in SQL server and insert something other than a null - such as a space - the field no longer shows up in the new settings screen.

But standard settings (such as setting self registration to disable) inserts a null into that field...

Any thoughts anyone?

Sean M Keogh
pteppic.net

thanks Sean,
with this new hint, a fix should be more affordable for moodle core developers.
Thanks again.

I am going to prod the developers via the helpdesk on this issue - if Moodle is treating Nulls in the database incorrectly...or if something in the database translation layer is, then it could potentially be quite damaging.

At the moment, in this setup with MSSQL, if Moodle sees a Null in a field in mdl_config, it treats it as if that field does not exist and needs to be created...

What happens if fields in other tables are treated the same way I have no idea...but I can't imagine that it would be anything good.

Sean M Keogh
pteppic.net

NULL means value not present, there should never be any nulls in config table,
could you please try to delete the values with nulls directly from config table?

If your database substitutes empty strings '' with NULLs Moodle can not work, sorry.

Personally I would not recommend using MSSQL/Oracle on production servers unless you have somebody who can diagnose/fix problems like this.

Hi Petr,

Unfortunately we don't have a say on what database systems organisations use...and it is quite common in the UK for large Colleges and universities to have MSSQL in use Campus wide and for them to refuse to use anything else, despite our best efforts to promote something else like MySQL.

This particular installation has been set up in accordance with the instructions in Moodle docs.

There are many fields in the mdl_config table that default to not having any values - such as alternateloginurl, forgottenpasswordurl, allowedemailaddresses, recaptchapublickey, langlist, the information for zip and unzip paths, smtp server info and a host of others. They do not have any values present, but the fields are there nevertheless. i am assuming that as they have no values, that the value of each is null...or am I misinterpreting?

The University has an MS SQL DBA, but he cannot see anything wrong with the database.

Any suggestions where to look next?

Sean K

Moodle is not storing NULLs there, it is storing '' empty string - we need to get the same value (empty string) back.

Yu can try to execute:

set_config('grrr', '');
var_dump(get_config('grrr'));

it should print setting 'grrr' with value '' empty string, there must not be NULL returned. If it returns NULL your MSSQL is misconfigured.

These are all of the fields which appear to be empty in the database. If I delete them from mdl_config, and refresh the notifications page, they all reappear, so i assume that Moodle is recreating them.

registerauth
alternateloginurl
forgottenpasswordurl
allowemailaddresses
denyemailaddresses
recaptchapublickey
recaptchaprivatekey
nonmetacoursesyncroleids
hiddenuserfields
gradeexport
googlemapkey
langlist
locale
lams_serverurl
lams_serverid
lams_serverkey
resource_popup
filter_censor_badwords
sitepolicy
cronremotepassword
defaultallowedmodules
displayloginfailures
notifyloginfailures
pathtoclam
quarantinedir
themelist
editorfontsize
editorhidebuttons
zip
unzip
pathtodu
aspellpath
smtphosts
smtpuser
smtppass
supportpage
proxyhost
proxyuser
proxypassword
memcachedhosts
hotpot_excelencodings
quiz_grademethod
quiz_password
quiz_subnet

The table definition in MSSQL is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mdl_config](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](255) NOT NULL DEFAULT (''),
[value] [ntext] NOT NULL,
CONSTRAINT [mdl_conf_id_pk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Allow Nulls is set to no, so I am assuming that actually these are not nulls but empty strings as you said. In which case what is going on?

This is in a brand new empty moodle, with no users and no courses...which is why I am able to mess around like this, obviously.

If MSSQL is intepreting Moodle's commands incorrectly and creating the fields and their values wrongly, can you give me any clue as to what might be wrong? As I say, the MSSQL people here cannot help.

Sean K

Running your test produces:

bool(false)

Sean K

grrr, big sorry - the code is:

<?php
require 'config.php';
set_config('grrr', '');
var_dump(get_config('', 'grrr'));

and result:
string(0) ""

It sounds to me that some PHP/FreeTDS combinations were buggy and caused conversions from empty strings to null in the past.

Searching for those issues I've arrived to MDL-11810 , where Alan and me were testing a lot of things. Finally, the conclusions were:

• Use PHP > 5.1.6 (there is one bug in previous versions).
• Use (at least) FreeTDS 0.64 + official patches.

Are you fulfilling both reqs? Specially the FreeTDS + official patches is the tricky one, if I don't remember wrongly.

Both reqs are listed in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix

Just one idea. Ciao

Hi Petr,

Running that returns:

NULL

I take it that's a bad thing...

Looking in MS SQL Studio at the contents of the table, the field values are not being stored as Nulls, but as empty strings. But they appear to be read back as NULLs if the results of your test are right.

Could it be the database abstraction layer in Moodle for MSSQL is interpreting things incorrectly?

Or is it more likely that MSSQL is storing the values correctly but dleivering them wrongly?

Sean K

We are on FreeTDS 0.7 and PHP 5.2.1 here and yet we seem to be having the same problem that was reported in that other issue linked to above.

Any thoughts?

Sean K

returned NULL means there is no bug in PHP code, most probably a driver or server configuratio issue.

Eloy, do you have some idea?

Uhm... I don't know about FreeTDS 0.7 (in fact I didn't know it exists!) ...

just downloading the latest stable release (0.82):

ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

then, applying the patches for that release:

http://freetds.sourceforge.net/post82.diff.gz

And going to test how it performs.

That's exactly what I did to have the 0.64 version working originally.

BTW, what are you using? Linux, Windows...?

Back soon.... ciao

This is a Windows 2003 server, talking to another Windows 2003 server with MS SQL Server 2005 on it.

cheers

Sean K

FreeTDS was downloaded from here: http://kromann.info/php5_2-Release_TS/php_dblib.dll

as mentioned in the documentation on Moodle Docs.

Hi Again Sean,

I've tested these combinations:

• FreTDS 0.64 + official patches.
• FreeTDS 0.82 (without official patches).
• FreeTDS 0.82 + official patches

All them with PHP 5.2.6.

And all them return: string(0) "" for Petr's test above (correct result).

So I guess you need to upgrade FreeTDS at least to 0.82 and, if possible 0.82 + official patches. Or also, downgrade to FreeTDS 0.64 with patches (they are required for 0.64). I've tried to find FreeTDS 0.7 but haven't been able to do so.

Ciao

Hi Eloy,

OK, I'll look into that.

many thanks for the quick work.

I'll report back.

Sean K

Ah, for sure those are outdated (compiled without patches applied) versions (the kromann ones). Going to see if I find newer ones.... for windows.

Ciao

Uhm,

all kromann binary builds seem to be really old (August 2007):

http://kromann.info/download.php?strFolder=php5_2-Release_TS&strIndex=PHP5_2

I guess they were created "before" I submitted the bug report to FreeTDS guys (October 2007):

http://sourceforge.net/tracker/?func=detail&atid=407806&aid=1817075&group_id=33106

Trying to contact with Frank Kromann to see if he can provide newer builds... while I continue searching for alternative downloads (I really don't know how to compile that extension under Windows, else...).

Ciao

Hi Eloy,

I've not been able to find any newer ones so far, and I don't have the ability to compile them up from source.

The latest one I can find is over a year old (and still has the problem).

Sean K

No luck, I've searched all related pages about compiling FreeTDS for Windows and everybody out there is using Franks' builds.

Going to email him right now. Ciao

Until then, Sean... perhaps you could try with the ODBTP alternative instead of FreeTDS (at least for Win32 environments).

Yes, I will give that a test.

Thanks

Sean K

I've requested Fran's help by email (sent CC to you, Sean). Cross your fingers!

Ciao

Hi Eloy,

Yest more version problems - the php_odbtp.dll binariey for windows that is available seems to be for a much earlier version of PHP - it won't load with php 5.2.1

I've run out of time for today, but will try again soon - I hope Frank replies soon.

best regards

Sean K

Hi Sean,

no luck with Fran yet. Also I've tried to compile freetds under windows myself but it's clearly beyond my skills. Anyway... there is one ODBTP binary build for Win32 and PHP 5.2.x. You can find it here:

http://odbtp.cvs.sourceforge.net/viewvc/odbtp/odbtp/php/bin_win32/php-5.2.x/
(not tested here)

I hope it can be the solution until we get some solution in the FreeTDS side...

Ciao

I am sitting in Istanbul and have run into exactly the same problem. Same setup as Sean and 2 very capable MS SQL admins at hand with the same outcomes and issues.

For clarification, does the message not disappearing cause any problems (apart from being a nuissance) or does it actually prevent Moodle to function at some stage?

Thank you!

I think it's really minor problem in the settings area... no big difference between NULL or empty there... but I can imagine problems in other parts if we get those empty strings transformed to NULLs from DB and then sent back as NULLs to DB (mostly they can be converted by Moodle itself back to proper empty strings).

But in some place I can imagine it causing problems (if === is used instead of empty() ) and so on. So I'd try the ODBTP alternative for Windows PHP servers... or try to build PHP & FreeTDS (newer version) under Windows.

In fact I continue here trying to compile PHP... I'll tell you if finally I'm able to do so. For now match results are:

Windows compilers 1 - Eloy 0

Ciao

I've requested for help also to FreeTDS guys, see http://lists.ibiblio.org/pipermail/freetds/2008q4/023955.html

Windows compilers 3 - Eloy 0

Ciao

Eloy,

There's one issue that you should be aware of with ODBTP: the Windows ODBTP .dll that Robert Twitty has on the ODBTP project page only works with the thread-safe version of PHP - it has not been compiled for the non-thread-safe version of PHP. Robert has promised to fix this for the next version, but ODBTP updates don't come very often (that last one, version 1.1.4, was released in January 2006). This whole thread safety issue is discussed on the ODBTP forum in this thread: http://sourceforge.net/forum/forum.php?thread_id=2220521&forum_id=239284

The reason that I bought this issue up is that the best way to run PHP on Windows systems seems to be to install FastCGI with the NON-THREAD-SAFE version of PHP - the performance you get from that combination is much better than with the thread-safe version of PHP. I noticed a BIG improvement in Moodle when I switched to FastCGI and the non-thread-safe version of PHP (before the switch I was using the ISAPI module and the thread safe version of PHP). This performance increase was the main reason why I switched back to FreeTDS from ODBTP. The only thing that still keeps me awake at night is the threat of the ntext vs nvarchar issue with MSSQL (MDL-9845 & MDL-11270) coming back to haunt me again.

Eloy, one more thing:

Have you seen this: http://www.nabble.com/need-help-building-php-on-windows-ts16046847.html
It looks like Jason was also trying to compile a more recent version of php_dblib.dll. Maybe you can ask him if he managed to get it to work and what he did to get the dll to compile? That thread is a few months old, but maybe Jason has resolved the problem by now...

Hi Luis,

yes, I saw that (and some more). In fact, I think he's the same Jason requesting for help in the FreeTDS forums (see link in my previous link). I was going to wait a bit hoping to get response from Frank Kromann/FreeTDS before asking him, but, yes, I'm going to email him NOW.

Thanks!

Sean,
In response to your "...the php_odbtp.dll binary for windows that is available seems to be for a much earlier version of PHP - it won't load with php 5.2.1" message, which version of PHP are you using - the thread safe or non thread safe version?

The php_odbtp.dll binary only works with the thread-safe version of PHP (5.2.5 and 5.2.6 at least, I haven't tested it on 5.2.1). If you're running the non-thread-safe version of PHP that might explain why you can't load ODBTP.

Hi again,

Good news: Finally I've been able to compile php_dblib.dll under Windows (it has been a "personal" war). I'll post complete instructions soon (in order to allow everybody to do so).

Bad news: Both FreeTDS 0.82 (with post release patches applied) and FreeTDS 0.83 (development version) doesn't compile under Windows. I've filled one bug about that: https://sourceforge.net/tracker/index.php?func=detail&aid=2209564&group_id=33106&atid=407806

Good news: It seems that both FreeTDS 0.64 (with post release patches applied) and FreeTDS 0.82 release can be compiled. I'll try/test that in some hours.

Until then: Can you please, download one test and paste results here. Instructions are:

• Download http://tracker.moodle.org/secure/attachment/12427/test.php
• Put it into your root moodle folder.
• Using the browser launch: http://yourmoodlesite/test.php
• Paste results (HTML source of the page, please).

TIA!

The view source output from my Windows Server 2003 Standard Edition SP2 box is:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->NULL
==testnvarcharmax==
null->NULL
empty->NULL

I also get the same results on a WinXP SP2 box. MS SQL Server 2005 is installed on the XP box but not on the Windows Server box.
And I get the same results from both Firefox 3.0.3 and IE 7.0.5730.11
I'm running PHP 5.2.6 on box boxes.

Great Luis, thanks, so are texts the ones failing!

I've sent you an email with one preliminary version of the lib, threaded and for use under PHP 5.2.x. If you can repeat test with it.... TIA!

Ciao

HI Eloy,

My Client has just tried your test and also got:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->NULL
==testnvarcharmax==
null->NULL
empty->NULL

Could you send us a copy of your newly compiled lib?

cheers

Sean K

Hi Sean,

I've sent you the library. Note that it's only for testing purposes. Any feedback will be welcome. I'll post instructions to build them ASAP.

Ciao

Eloy

I loaded the NTS php_dblib.dll and I get this result:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->string(0) ""
==testnvarcharmax==
null->NULL
empty->string(0) ""

Looks like it's fixed!

I'm going to leave the new NTS .dll running on my dev box for a while to see if there are any other issues.
I'll also see if I can get another box up with the TS version of PHP to see if php_dblib_freetds_0.82_php52_ts.zip also gives the same result.

The above result is for PHP 5.2.6 NTS running with FastCGI (and Moodle 1.9.3 Build 20081015).

The mssql section in my PHPInfo() output also looks normal. It shows the Library Version as being FreeTDS and all the directives look normal.

my contribution:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->NULL
==testnvarcharmax==
null->NULL
empty->NULL

Details about my mssql driver type and version are stated above.
Ciao and thank you.

Hi Eloy,

We've just tried it with the TS version, and the results were:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->string(0) ""
==testnvarcharmax==
null->NULL
empty->string(0) ""

so it looks like it worked for us too.

Sean K

P.S. pteppicnet should have worked as a skype ID - I'll check.

I loaded the thread safe version of PHP 5.2.6 with the ISAPI module (php5isapi.dll) and the thread safe of the new php_dblib.dll on a WinXP SP2 box and I get the same good result:

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->string(0) ""
==testnvarcharmax==
null->NULL
empty->string(0) ""

So it looks like the following combinations of PHP work with the new version of the FreeTDS .dll:
PHP 5.2.6 NTS with FastCGI and FreeTDS NTS
PHP 5.2.6 TS with ISAPI and FreeTDS TS

I haven't tested PHP 5.2.6 NTS with ISAPI and FreeTDS NTS because we should encourage Moodle admins to use FastCGI instead of ISAPI - PHP (NTS) performs much better with FastCGI than with ISAPI (according to my humble tests). And there's no point in running the thread safe version of PHP with FastCGI as FastCGI was created for running non thread safe apps in a thread safe environment.

finally our (I am with the valiant systems analyst: Arnold) answer too.

--->>>> WITH PHP 5.2.5 the new library doesn't load at all. <<<<---
WITH
-> php 5.2.6
-> IIS 6.0
-> ISAPI php5isapi.dll (thread safe)
the output is here.
(credits to Arnold for his quick work)

==testvarchar==
null->NULL
empty->string(0) ""
==testnvarchar==
null->NULL
empty->string(0) ""
==testntext==
null->NULL
empty->string(0) ""
==testnvarcharmax==
null->NULL
empty->string(0) ""

Thanks Daniele, Sean and Luis,

I think that those tests are enough to think the problem is fixed with the newer builds of the library. B-)

One think I don't understand completely is why it doesn't work for you with PHP 5.2.5, Daniele. My first test after building the libs was to try them with the WindowsMoodlePackages, that, right now, are using PHP 5.2.5 (running in xampp - apache).

And it worked, as shown in screenshoot (in fact should work with ANY 5.2.x version).

Anyway, going up to 5.2.6 as min "official" requirement sounds ok for me (in fact it's a correct version for future Moodle 2.0). So next steps are:

1) Document the building process.
2) Meet somebody with a complete MSVC6 license.
3) Build the official libraries and offer them to the Community.

Ciao

On monday afternoon I'll pass this problem to the friend Arnold.
I'll post here any further feedback!

Daniele,
Did you download and copy the NEW php_dblib.dll file that Eloy supplied for us to test into your \ext folder? Maybe you're still using the old version - that's the only explanation that I can think of for the failure.
If you check the properties of the php_dblib.dll file it should display 5.2.6.6 as the version number.

Ciao Luis
thank you for you suggestion.
Yes, I used the file provided by Eloy.
Well... I didn't check the properties but, I had it from him.
uuumm strange behaviour.

Daniele,

One more thing you could try to confirm that the new php_dblib.dll doesn't load under PHP is to load PHP 5.2.5 (non thread safe) with FastCGI (instead of php5isapi.dll) and then see if the non thread safe php_dblib.dll will load.

In my little world we are getting much better responses from Moodle when we run it on the non thread safe version of PHP and FastCGI (instead of ISAPI).

Just for the record... there are, also, new builds of ODBTP, both TS and NTS. You can find them at:

TS: http://odbtp.cvs.sourceforge.net/viewvc/odbtp/odbtp/php/bin_win32/php-5.2.x/
NTS: http://odbtp.cvs.sourceforge.net/viewvc/odbtp/odbtp/php/bin_win32/php-5.2.x-nts/

Ciao

Hi Eloy,

I have been following this thread with interest. We recently have been testing installations in similar environments as those discussed and have been seeing similar results.

I was just politely wondering if a copy of your newly compiled php_dblib.dll you have been working with may be available for testing?

Regards,

Nathan F

Hi Nathan,

apologises for not being able to send you a copy of those drivers for now. They were created with one "limited" version of MSVC6 from my Univ and I'm not confident about me having perms to distribute that software openly.

Hopefully this situation will be sorted soon, thanks to some MS licenses I'm waiting since some days ago. Then the drivers will be exposed publicly, 100% guaranteed.

I hope you understand my decision (and reasons). Please, stay tuned. Thanks!

Ciao, Eloy

No Problems, Thanks again.

Nathan F

Eloy,

The new .dll hasn't blown up my dev box yet so it seems to be quite stable - and I HAVE tried to break it! ;->

Did you ever get a response from Frank Kromann? I'm still waiting...

Can you publish the procedure that you used to build the dll's? Thanks...

Am I wrong or... the same problem is present in the last (downloaded just now) Moodle 2.0 on mysql too?
I can not dispose of "Optional subsystems" from the "Site administration" block!

Hi Daniele,

I think you're wrong. That's a new settings page in 2.0 just that. It will always present there (it's the main switcher for a lot of things (subsystems) that in previous versions are present in different settings pages.

Ciao

Hi Luis,

great to hear that driver is working fine in your dev box. About Frank, no, I haven't received response from Frank nor from FreeTDS guys. And yes, I've to write the procedure... sorry by the delay (I'm so lazy....). I'll try to write that this week.

Ciao

Thanks Eloy!!!

Hi, I've added the compilation instructions at:

http://docs.moodle.org/en/Development:Compiling_FreeTDS_under_Windows

Feel free to improve them. And, of course... if any of you is able to generate those extensions and share them with us... we can add them to the official FreeTDS for Windows page, replacing Frank Kromann ones.

Resolving this a fixed. Feel free to comment anything hre or in Docs page. Ciao

Eloy
even if you found a solution to the problem,
new users will still find the problem as it is.
IMHO this bug should remain open until the new driver will overwrite the current one in the official release.

php_dblib.dll
2008-12-16
Visual C++ 6.0 SP6
Using these intructions...
http://docs.moodle.org/en/Development:Compiling_FreeTDS_under_Windows
PHP 5.2.9
freetds-0.82

output from build of new DLL
someone might know if the warning are significant or not
the new DLL seems to work okay...

Thanks Trevor for your contribution.
Are we allowed to use freely your patch?
Should be added to the moodle installation readme, the replacement to the official dll with your one?
Thank you in advance!

Daniele
The DLL was compiled using a licensed version of VS6.0
The PHP license says binaries should come with the license so I have attached that
The FreeTDS GNU License is also attached
So should be okay to distribute
As for replacement of the official DLL, more testing first maybe.

Hi

Thanks for the DLL Trevor! B-)

Is people here using it? Yesterday I pointed to some more users (see MDL-15489) to use it. If everything goes ok, I think we can promote it to docs.moodle.org and encourage people using MSSQL+Win32 to switch ASAP.

BTW, is that the thread safe or no thread safe DLL, Trevor? Note it's a switch in the php configuration. I guess that, depending of web servers people will need both.

Also, Trevor, do you want any credit about the DLL compilation? I think it's ok to publicly give thanks to you/your institution, as you want. Thanks again!

Ciao

I am experiencing this same MSSQL/FreeTDS problem with NULL values.

I downloaded Trevor's updated (FreeTDS) php_dblib.dll and it does not work with my thread-safe installation of PHP.

Can anyone post a thread safe version of php_dblib.dll?

Thanks-

Hi

The first one was using the switch --enable-zts, so according to the instructions it should be thread safe, I am using it with PHP 5.2.9 thread safe and haven't struck any issues yet.

I have just uploaded a non thread safe version, compiled with the --disable-zts switch, along with the output text file.
I don't use the non thread safe PHP so am unable to test this one sorry.

LOL, credit would be nice, but I didn't do much, up to you, .

Cheers

Thanks Trevor!

My problem with your initial TS version was likely due to the fact that I am running PHP 5.2.6 and not, as you noted, PHP 5.2.9.

Thanks for the information and the additional posting.

For those of us who are running MSSQL2005 this is far from a small problem. Prior the the availability of this fix I was unable to use the Backup/Restore functions. I am guessing that there many other problematic behaviors that will be cleared up by this fix.

Thanks again!

Hi,

are you, ppl, using Trevor's TS and nonTS drivers? Any problem? Can anybody confirm how are they working with PHP 5.2.6 ? In theory all the PHP 5.2.x series should work ok.

It would be terrific to get some feedback in order to update Docs ASAP. TIA!

Ciao

Eloy,

I've been using it on two Moodle 1.9.3+ instances (on Windows 2003 & IIS) on my dev server since it was released in December and so far haven't found any problems that I can link to the .dll. But I haven't yet had the time to really try and break it.

Hi Eloy,

My client at Winchester Uni has tried it with their PHP 5.2.1 and says it is working fine.

cheers

Sean K

Eloy-

Unfortunately the TS version of Trevor's posted dll did not work for my installation. I am running IIS6 with PHP 5.2.6 and Moodle 1.9.1.

Thanks,

-Doug Winther

Just installed Trevor's TS version. Yeah! Many thanks to all the great effort reflected in this thread. Keep up the superb work.

My config...

PHP Thread Safe - v5.2.8

eAccelerator - v9.5.3

Microsoft IIS-7 / ISAPI

MSSQL v9.0.3068

FreeTDS (unclear on how to determine version: guessing 0.82)

-jgm

Hi,

it seems that the extension built with PHP 5.2.8 has some problems if running with previous versions of PHP.

Also, luckily, and thanks to Remote-Learner (Moodle Partner) and specially to Bryan Williams, donating to Moodle one Visual C++ 6.0 Pro license... I'm attaching here one DBLIB_TS and DBLIB_NOTS compiled with PHP 5.2.6 and FreeTDS 0.82

It would be great if you can test them in your environments to check if finally we can find one working lib under any PHP 5.2.x version, in order to publish it and close this bug. Thanks in advance!

Ciao

Eloy,

Thanks. I tried DBLIB_NOTS with PHP 5.2.8 NTS and FastCGI on Windows Server 2003 and MSSQL 2005 SP1 and it seems to be working ok.

According to http://www.freetds.org/tds.html there is a version 9.0 of TDS (for MSSQL 2005), but which version of TDS is this DBLIB built on?

And what version should we use for the "tds version" field in the freetds.conf file - 8.0 or 9.0 (or even 7.0)?
I've tried specifying both 8.0 and 9.0 in freetds.conf and Moodle and PHP seem to be ok with both - but I haven't really tried to break the setup yet.

Which version of TDS is this DBLIB built on?

sure. Going to share your attachment with my system administrator! Thanks.

Ok, after testing a bit more it seems that only tds version 8.0 works.

When I specify
tds version = 9.0
in my freetds.conf file and then login to Moodle (after restarting IIS) I get the following messages on /admin/index.php:

Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

SELECT * FROM mdl_course WHERE category = '0'
line 686 of lib\dmllib.php: call to debugging()
line 481 of lib\dmllib.php: call to get_recordset_sql()
line 421 of lib\dmllib.php: call to get_record_sql()
line 377 of lib\datalib.php: call to get_record()
line 3785 of lib\weblib.php: call to get_site()
line 123 of lib\adminlib.php: call to build_navigation()
line 2834 of mod\assignment\lib.php: call to upgrade_plugins()
line 431 of lib\adminlib.php: call to assignment_upgrade_submodules()
line 432 of admin\index.php: call to upgrade_activity_modules()

and:

mod/assignment/type/online plugin needs upgrading
Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

SELECT * FROM mdl_events_handlers WHERE handlermodule = 'assignment_type/online'
line 686 of lib\dmllib.php: call to debugging()
line 609 of lib\dmllib.php: call to get_recordset_sql()
line 577 of lib\dmllib.php: call to get_recordset_select()
line 910 of lib\dmllib.php: call to get_recordset()
line 78 of lib\eventslib.php: call to get_records()
line 110 of lib\eventslib.php: call to events_get_cached()
line 155 of lib\adminlib.php: call to events_update_definition()
line 2834 of mod\assignment\lib.php: call to upgrade_plugins()
line 431 of lib\adminlib.php: call to assignment_upgrade_submodules()
line 432 of admin\index.php: call to upgrade_activity_modules()

These messages aren't displayed when I specify tds version = 8.0 in my freetds.conf file.

Does this imply that FreeTDS is built on version 8.0 of TDS?

Hi Luis,

thanks for your tests! B-)

The DBLIBs attached above have been built with FreeTDS 0.82 (officially released in May 2008). It's the latest official release AFAIK. I also tried to compile the same version (0.82) with cumulative patches applied (available at http://freetds.sourceforge.net/) and 0.83 (currently under development) and none of them complied ok under windows. I posted one bug about that at:

http://sourceforge.net/tracker/index.php?func=detail&aid=2209564&group_id=33106&atid=407806

About the TDS (not FreeTDS) underlying protocol... I think tds=9 is only available in development versions, and tds=8 is the correct (latest available) in current FreeTDS 0.82 drivers. So I guess that, when you specify tds=9 in your configuration, the driver is using the "default" one (tds=5 or so). So tds=8 seems to be the correct choice, though in my test server and in Moodle Docs it's said to use tds=7 (perhaps because tds=8 wasn't available in old FreeTDS 0.64).

So, summarising:

• We are using the latest FreeTDS version possible (0.82 release).
• If the bug above is fixed we'll evolve to the 0.82+paches and to 0.83 once released.
• tds=8 seems to be correct (though tds=7 should work too)

Ciao

Eloy-

I am running Moodle 1.9.4 with IIS6, MSSQL2005 and PHP 5.2.6 with thread safety enabled.

My initial tests show that the DBLIB_TS version of php_dblib.dll is working fine.

Thanks for your efforts with this needed fix, it is greatly appreciated.

-Doug

Thanks for feedback everybody.

Yesterday I got reply from TDS about the bug preventing 0.82 with patches and 0.83 (development) to compile under Windows. It seems that 0.82 with patches is going to work soon B-) so:

1) I'll build it when available.
2) Will request you one (the last I hope) round of tests
3) If everything goes ok I will post those builds into public (Docs and so on) places.

I'll comment here once new versions are available. I think that having the latest 0.82 + bug fixes is the best candidate. Thanks everybody for testing. Ciao

Hi,

I'm attaching here these files:

• DBLIB_TS_082_20090227.zip
• DBLIB_NOTS_082_20090227.zip

both are builds of the MSSQL driver using FreeTDS 0.82 + post release patches applied until 20090227).

I've had some minor problems compiling that ( more info at http://lists.ibiblio.org/pipermail/freetds/2009q1/024467.html ) but finally got it compiled.

Abusing once more, any feedback from you, about how those libraries (for PHP 5.2.x!) are working would be great! (specially the NULL problem that ignited all this, hehe).

TIA and ciao

Hi, I'm deleting some of the attachments in the bug, to leave only current drivers... ciao

Adding here the test.php script (to be executed in the moodle root dir via web). It should return null for all null tests and empty for all empty tests.

Ciao

No, please Eloy, leave us the file to let us make use of it to provide you a quick feedback.
I don't remember where I saved it last time.

Thanks.

Hehe, sorry. Too late. Anyway I've all them here so np. Just want to test the latest ones. Ciao

DBLIB_NOTS_082_20090227.zip gives me this output on Windows Server 2003, IIS6, MSSQL 2005, FastCGI and PHP 5.2.8:

==testvarchar== null->NULL empty->string(0) "" ==testnvarchar== null->NULL empty->string(0) "" ==testntext== null->NULL empty->string(0) "" ==testnvarcharmax== null->NULL empty->string(0) ""

So, it seems to be ok.

Are the compilation instructions on http://docs.moodle.org/en/Development:Compiling_FreeTDS_under_Windows still the same?

Thanks Luis,

if all the null tests return NULL and all the empty tests return string(0) then, yes, test is ok.

About instructions, yes, they are the same BUT, take a look to:

http://lists.ibiblio.org/pipermail/freetds/2009q1/024467.html

There is one ongoing discussion about one bug I found in recent versions of the MSVC project. Hopefully they will fix it soon and instructions won't need any change.

Oki,

it seems that FreeTDS have been really quick and have updated the MSVC project file to work perfectly (in patch 20090302).

So I'm:

• Uploading here the latest drivers (with patches upto 20090302 applied).
• Change slightly the Docs to show all the details of current build.
• Delete previous builds (new ones are exactly equal to the ones attached yesterday)

Ciao

Done. Now:

• Docs have been updated to be more accurate.
• New drivers are available here.

FINAL round. Feedback some results about TS/NOTS versions, publish and close this. Ciao

Eloy,

Thanks. Your NTS archive (DBLIB_NOTS_082_20090302.zip) contains the php5.dll file and the TS archive
(DBLIB_TS_082_20090302.zip) contains php_dblib.dll. Is this correct?

Shouldn't the NTS (DBLIB_NOTS_082_20090302.zip) archive also have the php_dblib.dll file? The php5.dll file is one of the core PHP files, not the FreeTDS extension (unless something has changed...)

Re-uploading the NOTS version, I packaged it incorrectly.

I've updated Moodle Docs to point to new files (will improve that later today):

http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows

Ciao

Well, finally... I think we can resolve this as fixed. Has been one looong story. Thanks everybody by your help and support. B-)

I've added the official download URLs of the drivers to:

http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows

so I'll be deleting the attachments here in a couple of minutes.

Also, I've added one mini-credits section in the Docs to say thanks to everybody. Feel free to edit it to add/fix anything.

Ciao

Just one more thing!

Point 4 of the documentation (http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows) describes what users must put in the C:\freetds.conf file. One of the fields it mentions is:

tds version = 7.0

This should be:

tds version = 8.0

because the version of FreeTDS that we are using here is built on version 8.0 of the TDS protocol.

I'm using tds version = 8.0 in my .conf file and I can connect to my database...

Well spotted! I've changed that, both for unix builds and win32 builds). Thanks!

I have Windows Server 2003 R2 SP2, PHP 5.2.9-2, MS SQL 2005 (Installed on a different server) and FreeTDS.

Everything checks out until I get to the install and I get the 4004 error.

4004: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

ADOConnection._Execute(SELECT * FROM mdl_config WHERE id = '-1', false) % line 891, file: adodb.inc.php

ADOConnection.Execute(SELECT * FROM mdl_config WHERE id = '-1') % line 1469, file: dmllib.php

insert_record(config, Object:object) % line 660, file: moodlelib.php

set_config(unicodedb, 1) % line 198, file: index.php

I am able to overcome that by changing the data type of Value in the mdl_config table from ntext to nvarchar.

But then it gets hung up on mod/assignment/type/online plugin needs upgrading.

I have to change the data type back to ntext and then delete the Online folder from /mod/assignment/type it gets past that screen but then it starts getting 2714 Errors (There is already an object name 'mdl_question_calculated' in the datbase.

That's where I totally get stuck.

Hi Dustin,

is it possible that you are using the non-freetds driver instead of the one specified in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows

You can see that easily by creating one script containing just one line:

phpinfo();

and looking for "mssql" it should show you something like "Version: FreeTDS" if you are running the correct one. Please check the documentation like above and confirm that you have configured everything properly.

Also, take a look to the http://docs.moodle.org/en/Talk:Installing_MSSQL_for_PHP page, where recently another moodler had some problems, finally solved by looking to logs and installing the correct php5 lib.

Hope it helps, ciao

Eloy,

Thanks for the quick responce! I have the correct version of FreeTDS as it does show up under mssql in the phpinfo.

I turned on error logging and got this error - hopefully you can tell me how to fix it

[08-May-2009 10:11:30] PHP Warning: PHP Startup: apc: Unable to initialize module
Module compiled with module API=20060613, debug=0, thread-safety=1
PHP compiled with module API=20060613, debug=0, thread-safety=0
These options need to match
in Unknown on line 0

Thanks,
Dustin

Well I got rid of that error by actually using the correct nts php_apc.dll.

However, now I don't get any errors in the log but I still have the 4004 error I started with.

Help...Again

Ah, as you have seen in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows there are TWO libraries available to download (one is thread safe and the other no).

It seems that you are running PHP without thread safety, so I guess you will need to use the non-thread-safe version of the driver (from the link above).

In any case, I'm not a Win32 specialist, but it sounds to me that some guys in this thread commented that Win32 + IIS works pretty well in threaded mode, with fastcgi or something like that (perhaps you should try that, I insist I don't know really about that, just noticing it to you).

In any case, please, check if using the correct dblib (the non-threaded one) does the trick for you. TIA and ciao

Ok so now I have tried the following:

PHP 5.2.9-2 NTS
PHP 5.2.9-2 TS
PHP 5.2.8 NTS

All running on Windows Server 2003 R2 SP2, PHP 5.2.9-2, MS SQL 2005 (Installed on a different server), FreeTDS, and IIS 6.0

All had no errors in the PHP error log but I still got the 4004 error when installing Moodle.

Anyone have any ideas? I am running out...

Dustin,

What have you got in your freetds.conf file? Does it contain this line:

client charset = UTF-8

Luis,

This is what is in my freetds.conf file.

[global]
host = 10.110.0.53
port = 1433
client charset = UTF-8
tds version = 8.0
text size = 20971520

php_dlib.dll for php 5.3.x
Both thread safe and non-thread safe included
Built with VC9

USE WITH CAUTION!

Non thread safe tested and working, thread safe untested

Hi Alastair,

thanks a lot! Amazing!

Is it ok if I:

• Put both files (separated) in download.moodle.org
• Update http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows to point to them?

That way we can trace how people are using them while keeping everything well organised (see, very bottom of: http://download.moodle.org/stats.php)

Once more, thanks!

Edited: grrr, me and links...

Hi Alastair,

as commented, I've copied your builds to download.moodle.org and updated the Moodle Docs page pointing to them (http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows)

Also I've added you to the "thanks/credits" footer (you deserve it 100%). If, for any cause, you don't want to be there or prefer another info to be shown, just tell me/do it yourself. It's a wiki!! :-P

And that's all, many, many thanks! Ciao

And, if anybody watching this bug is able to test the TS version for PHP 5.3 it would be great! TIA!

Please include also php_pdo_dlib.dll

Hey, thanks for the great resource here.

I have noticed there are no VC6 builds of php_dblib for PHP 5.3.x, which sucks because anyone running Apache on Windows needs VC6 versions (unless they decide to compile apache from source).

So, I've attached both TS and NTS builds I just got going with VC6 against php v5.3.5, using the guide on Moodle. They work great here!

Please feel free to link it on the main doc.

Hi Matt,

many thanks for building them and for updating the build instructions @ Moodle Docs.

Can anybody test those new builds and feedback about the experience here? After getting some positive report, I'll be happy to put them available @ download.moodle.org and link to them from the Docs.

Thanks!