#25 Empty values on DB, returns NULL

[Eloy Lafuente]

Hi,

we are using FreeTDS (the php_mssql extension replacement) in order to
support mssql in our development.

While everything seems to be working pretty well, we have found that
fields with empty strings in DB are retrieved by your product converted to
PHP NULLs, and is breaking a lot of stuff in our PHP code, because any
isset() operation against such fields simply break.

We have tried it also directly with the tsql interface and results are the
same (while other drivers or Query Analyser shows data properly).

I've been looking for some configuration variable or something else
across all your documentation but I haven't been able to find anything
about this problem.

Test is pretty simple, just insert one empty string to any field, retrieve
such record and NULL will be there.

TIA and ciao :-)

[Frediano Ziglio]

Logged In: YES
user_id=75766

Perhaps you are using a wrong TDS protocol version.
Try 7.0 or 8.0

freddy77

[Eloy Lafuente]

Logged In: YES
user_id=758266

Uhm, really strange!

I'm pretty sure that my freetds.conf file has been since the beginning:

[global]
tds version = 8.0
client charset = UTF-8
host = 10.0.2.2
port = 1433

and I was getting such NULLs contents in my previous tests (MSSQL 2000).
Since some days I'm using MSSQL 2005 and, after recompiling PHP with TDS
support I'm getting the correct empty values.

Perhaps it was something related with MSSQL 2000? I cannot test it here
again :-(

Anyway, thanks for your support, freddy! :-)

[Frediano Ziglio]

Logged In: YES
user_id=75766

As confirmed it was a configuration/version problem.

freddy77

[Edward Rudd]

Logged In: YES
user_id=8488

I am still having this issue with my databases.

Running MS SQL 2000 (SP4) and verious versions of php
(4.3.8, 4.3.10, and 5.1.4) with an updated version of the
mssql extension (based on php 4.4.0 for the 4.3.x series and
straight up php 5.1.4 for the 5.1.4 machine)

all machines are running freetds 0.63 w/ a multiple result
set patch applied that you supplied a while ago to me (so it
freetds doesn't segfault).

Now, the "empty string being returned as NULL" happens on
ALL boxes.

I just upgraded the FC5/php 5.1.4 system to freetds 0.64 and
it is still producing this issue.

I have changed the tds version to all available values and
it doesn't fix the issue.

4.2 and 4.6 cause the empty string to be returned as a
single space.

5.0 doesn't allow me to connect at all
7.0 and 8.0 return the empty string as a NULL.

If this is NOT an issue with freetds, have you an idea where
it would be caused in the php-mssql extension??

[Frediano Ziglio]

Logged In: YES
user_id=75766

Mmmm... could you post some code snip?
I'm starting thinking that it could be a design problem of
lower library.
I opened again the bug report.

freddy77

[Edward Rudd]

Logged In: YES
user_id=8488

PHP code snip?
$db =&
DB::connect("mssql://username:pass@server:1433/mydatabase");
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$row =& $db->getRow("SELECT top 1 * FROM MyTable WHERE
myfield = ''");
echo is_null($row['myfield']) ? '(NULL)' : '(Empty String)';

With Table
CREATE TABLE MyTable (
myfield VARCHAR(10) NULL
);
INSERT INTO MyTable VALUES('')

[Frediano Ziglio]

Logged In: YES
user_id=75766

I installed a php-mssql package and running this test (in
phptests):

<?php
require_once("pwd.inc");
require_once("DB.php");

$db = &DB::connect("mssql://$user:$pass@$server/tempdb");
if (PEAR::isError($db)) {
die($db->getMessage());
}
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$db->query("CREATE TABLE #MyTable (
myfield VARCHAR(10) NULL
);
");

$db->query("INSERT INTO #MyTable VALUES('')
INSERT INTO #MyTable VALUES(NULL)
INSERT INTO #MyTable VALUES(' ')
INSERT INTO #MyTable VALUES('a')");

function sql($s)
{
if (is_null($s))
return '(NULL)';
if ($s == '')
return '(Empty String)';
return "'".str_replace("'", "''", $s)."'";
}

$row =& $db->getRow("SELECT top 1 * FROM #MyTable WHERE
myfield = ''");
echo sql($row['myfield'])."\n";

$row =& $db->getRow("SELECT top 1 * FROM #MyTable WHERE
myfield IS NULL");
echo sql($row['myfield'])."\n";

$row =& $db->getRow("SELECT top 1 * FROM #MyTable WHERE
myfield = ' '");
echo sql($row['myfield'])."\n";

$row =& $db->getRow("SELECT top 1 * FROM #MyTable WHERE
myfield = 'a'");
echo sql($row['myfield'])."\n";
?>

it returns:

[... phptests]$ TDSVER=4.2 php null.php
' '
(NULL)
' '
'a'
[... phptests]$ TDSVER=8.0 php null.php
(NULL)
(NULL)
(NULL)
'a'

so the problem is.... dblib! Using dblib is impossible to
distingue between empty string and NULL (in dblib a field is
NULL if length/size is 0) so the db used to return a space
(' ') for empty strings however our internal library support
empty string but dblib do not consider that empty string
should be converted to single space string in order to avoid
'' -> NULL. So I'm going to
- rewrite php test to avoid PEAR::DB dependency (or write a
new C dblib test)
- translate '' -> ' ' in dblib
- backport to 0.64

freddy77

[Frediano Ziglio]

Logged In: YES
user_id=75766

Could you try the attached patch? It seems to work with my test.

freddy77

[Frediano Ziglio]

proposed patch

[Edward Rudd]

Logged In: YES
user_id=8488

hmm.. didn't seem to work.

now TDSVER 8 acts the same as TDSVER 4.2 where the empty
string '' is returned as a " "
output from test after patch.
===
' '
(NULL)
' '
'a'
===
Now I did (before patching) run a test using the bsqldb app
(which uses dblib) and things were *seemingly* being
returned correctly there. (it's hard to tell as bsqldb padds
the output with extra spaces:( )

Do you have a C version of this test so I can test dblib
directly?

[Frediano Ziglio]

Logged In: YES
user_id=75766

Well... this was the intent, however I find a possible
solution. There are two way to read data:
- dbdatlen/dbdata
- dbbind/dbnullbind
php use dbdatlen/dbdata.
Using dbdatlen/dbdata dblib returns
datlen data
NULL 0 NULL
'' 0 pointer
' ' 1 pointer
so php could distingue between '' and NULL. However this
require recompiling php...
From ext/mssql/php_mssql.c (see
http://cvs.php.net/viewvc.cgi/php-src/ext/mssql/php_mssql.c?revision=1.166&view=markup\)
in php_mssql_get_column_content_with_type replace

if (dbdatlen(mssql_ptr->link,offset) == 0) {

with

if (dbdatlen(mssql_ptr->link,offset) == 0 &&
dbdata(mssql_ptr->link,offset) == NULL) {

freddy77

PS: I'm going to try...

[Frediano Ziglio]

Logged In: YES
user_id=75766

Well, with php change this is what I get

[... phptests]$ TDSVER=4.2 php null.php
SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> ' '
SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL -> (NULL)
SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' ' -> ' '
SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a' -> 'a'
[... phptests]$ TDSVER=8.0 php null.php
SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> (Empty String)
error!
SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL -> (NULL)
SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' ' -> ' '
SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a' -> 'a'

beside error I think is what you espect! I had to change a
bit my test cause '' == ' ' in mssql.

freddy77

[Frediano Ziglio]

Logged In: YES
user_id=75766

I opened bug report #39213 at php.
Detail at http://bugs.php.net/bug.php?id=39213

freddy77

[Edward Rudd]

Logged In: YES
user_id=8488

OK.. finally got a change to look at your suggestions for
PHP.. Using a STOCK freetds 0.64 build w/ your initial fix of
--
if (dbdatlen(mssql_ptr->link,offset) == 0) {

however in order to fix the problem should be

if (dbdatlen(mssql_ptr->link,offset) == 0 &&
dbdata(mssql_ptr->link,offset) == NULL) {
--
in php_mssql_Get_Column_content_with_type and
php_mssql_get_column_content_without_type

and it works for me as well.. SWEET. that is very odd that
a ''=' ' in MSSQL. At least now things will work better:)
Thanks for all your help.. Now, what exactly IS causing
that 'error!' prompt??

[Edward Rudd]

Logged In: YES
user_id=8488

ahh never mind.. figured it out.. Thanks for all you help