Moodle

Hi Alan,

I remember that there was some bug in FreeTDS or PHP causing that. After a looking for it a bit, I've found this:

The FreeTDS bug tracker (I ignited!):

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

The PHP bug that Freddy found and submitted to PHP bug tracker:

http://bugs.php.net/bug.php?id=39213

Note that the bugs is closed as fixed for PHP > 5.1.6

So it's beyond our capabilities to perform any change, in fact the mssql_n ADOdb driver hasn't any way to differentiate those false NULLs from the real ones, so the correct behaviour should be to re-compile PHP / FreeTDS, or to use one PHP version with the bug fixed or to use the FreeTDS alternative (ODBTP) that doesn't present this problem.

Closing this now, it would be great if you can test it with higher versions and confirm if it's working.

We're using PHP 5.2.0. The functions both start with:

if (dbdatlen(mssql_ptr->link,offset) == 0) { ZVAL_NULL(result); return; }

Does this need to be patched and recompiled?

The reason I thought this was an ADOdb issue was because we're only having issues with it on 1.9. We're running 1.8 on our production site and it works fine.

Uhm... really strange. PHP 5.2.4 has, in php_mssql_get_column_content_with_type()

if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) { ZVAL_NULL(result); return; }

And that should fix the problem, according with Freddy and the PHP bug.

But I've recompiled my PHP 5.2.4 (with that change applied also to the other function) and the problem persists

Also, I've tried under Moodle 1.8 and the same problem happens so I really think it isn't a Moodle related thing.

Just being really tired now. Tomorrow I'll re-re-compile everything, not sure if I've cleaned everything properly.

Ciao

Although I'm pretty sure it isn't Moodle, I reopen this to remember I need to test recompling my PHP box.

You're right, for some reason I never noticed this before. I'll talk to the sysadmin and see if he can upgrade PHP.

Yep, confirmed. I've:

1) Downloaded php 5.2 4 source code
2) Downloaded freeTDS 0.64 sorce code
3) Compiled and installed freeTDS
4) Compiled and installed PHP with --with-mssql=/usr/local (that way it uses freeTDS).
5) Performed tests and everything worked fine NULLs are returned as NULLs and empty strings are returned as empty strings.

So the bugfix is correct and everything should work after PHP 5.1.6. Forget the modification I suggested above, it isn't necessary for PHP5 (in fact I've deleted such comment to avoid confusion).

Going to add one comment in the Installing MSSQL Moodle Docs page to inform people about the PHP > 5.1.6 req.

Ciao

I've updated http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to inform about the PHP bug.

It would be great, Alan, if you can confirm that your newer PHP version (>5.1.6) fixes the problem.

TIA and ciao

We just upgraded to 5.2.4 yesterday, but the problem still exists. I checked mssql.c and it has the proper changes in it. We have php configured with --with-mssql=/usr (that's where freeTDS is installed).

This might explain some things:

create table nulltest (test varchar(5));
create table nulltestnvar (test nvarchar(5));
create table nulltestntext (test ntext);
create table nulltestnvarmax (test nvarchar(max));

insert into nulltest values('');
insert into nulltest values(null);
select * from nulltest;

– (repeat above three for all tables)

nulltest and nulltestnvar will yield '' and null, but the nulltestntext and nulltestnvarmax will give null for both values

Also, this is a problem with the mssql extension cause the mssql* functions give the same result

Uhm... really strange,

after recompiling FreeTDS and PHP 5.2.4 here, I performed tests against the config table (whose "value" field is a ntext column). And I got '' (empty strings) for a lot of settings.

Are you sure that you've recompiled PHP with FreeTDS support? The phpinfo page should show "FreeTDS" in the mssql section.

Going to perform your tests here now.

Ciao

Yeah, we're using FreeTDS: https://courses.mytwu.ca/enrol/twu/info.php#module_mssql

I reported this on the PHP bug tracker: http://bugs.php.net/bug.php?id=43043

Wow,

I cannot understand why yesterday I got empty and nulls 100% diferenciated. In fact my test was really simple:

var_dump($CFG);

and I got proper empty strings for a lot of config settings! Going to test it again (I switched to ODBTP after tests, grrr).

Ciao

And, after 10 more recompilations.... I'm not able to get it working!

So it seems that yesterday I confused, once again, my php extensions. Sorry for that.

Also, Alan, I've found this: http://bugs.php.net/bug.php?id=41539

Seems to be exactly the problem you describe in your PHP bug and it isn't fixed in PHP CVS (although the reported provides a working hack). Going to see what happens with that hack.

Ciao

Hi Alan,

I've been playing a bit the the cool explanations of the problem at http://bugs.php.net/bug.php?id=41539 and I think I've one initial workaround for the problem (a patch that requires recompiling both FreeTDS and PHP).

I've attached two patch files:

• freetds_dblib.c.patch: to be applied to FreeTDS 0.64 src/dblib/dblib.c
• php_mssql.c.patch: to be applied to PHP 5.2.4 ext/mssql/php_mssql.c

With those two patches applied and recompiling both FreeTDS and PHP and getting correct NULLs and empty-es in my tests and with the code you sent to http://bugs.php.net/bug.php?id=43043

Also, I've opened one bug request in FreeTDS tracker, to explain the problem and document it AFAIK. I'm not sure if that's the best solution but I hope it's the basis for a final (soon) one. You can find it at:

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

Ciao

Hi Alan,

it seems that Freddy (the FreeTDS man) has fixed it in current 0.64 download. I'm going to install PHP from scratch (without any hack at all) and try to compile current FreeTDS 0.64 to see what happens.

Complete info at: https://sourceforge.net/tracker/?func=detail&atid=407806&aid=1817075&group_id=33106

Thanks, let me know how it goes. I want to get this fixed before we deploy 1.9, it's not a blocker for me, but it would be nice.

Seems to be working. I've followed these steps:

1) Download latest stable (0.64) FreeTDS from http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
2) Patched it with the proper "post 0.64" patch from http://freetds.sourceforge.net/
3) configure; make; make install
4) Compiled PHP 5.2.5 with the option "--with-mssql=/usr/local"

Then I've executed the test.php script (attached above) and empties are empties and nulls are nulls for all the text types (varchar, nvarchar, ntext and nvarchar(max).

Cool!

I had our sysadmin patch and compile this stuff and now the tests all work

I think you can close this if you want.

Thanks for feedback, I've added some information in http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Unix to try people know about this problem with 0.64 FreTDS and the need to apply patches before compiling it.

Closing, ciao

For the record: FreeTDS 0.82-r3 + PHP 5.3.5 works for me out of the box without the patch (at gentoo)