#41539 [Opn->Asn]: MSSQL ext. with freetds treats '' (string of length 0) as NULL in VARCHAR(MAX)

ID: 41539 Updated by: tony2001@php.net Reported By: frode at coretrek dot com -Status: Open +Status: Assigned Bug Type: MSSQL related Operating System: Linux PHP Version: 5.2.2 -Assigned To: +Assigned To: fmk Previous Comments: ------------------------------------------------------------------------ [2007-05-30 09:27:01] frode at coretrek dot com Description: ------------ While http://bugs.php.net/bug.php?id=39213 fixed the issue with a '' value (a string of length 0) being returned to PHP as NULL, the issue persists for data stored in columns of type VARCHAR(MAX) (and NVARCHAR(MAX)). I don't think this is a freetds issue, since running tsql on the command line shows NULL and empty strings correctly after running the PHP code below. TSQL output is: % ./tsql -H someserver -p someport -U testnull locale is "LC_CTYPE=no_NO.ISO8859-1;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C" locale charset is "ISO-8859-1" Password: 1> select * from testnull; 2> GO id txt txtmax 1 2 NULL NULL Also, is there a reason for why php_dblib.dll is not being shipped on the win32 binary builds? Apart from this bug, php_dblib appears to be the most complete mssql extension available. All the others I've tried have critical shortcomings (php_mssql does not support nchar/nvarchar (unicode) columns at all, and also, while the third party php_odbtp_mssql does work properly (actually it even returns NULLs correctly), it is an order of magnitude slower than php_dblib). Reproduce code: --------------- <? header('Content-Type: text/plain'); $ms = mssql_connect('some-sqlserver2005-hostname', 'testnull', 'testnull'); mssql_select_db('testnull', $ms); mssql_query('DROP TABLE testnull', $ms); mssql_query('CREATE TABLE testnull (id integer, txt varchar(255) NULL, txtmax varchar(max) NULL)', $ms); mssql_query('INSERT INTO testnull VALUES (1, \'\', \'\')', $ms); mssql_query('INSERT INTO testnull VALUES (2, NULL, NULL)', $ms); $rs = mssql_query('SELECT * FROM testnull', $ms); while ($row = mssql_fetch_assoc($rs)) { var_dump($row); } Expected result: ---------------- array(3) { ["id"]=> int(1) ["txt"]=> string(0) "" ["txtmax"]=> string(0) "" } array(3) { ["id"]=> int(2) ["txt"]=> NULL ["txtmax"]=> NULL } Actual result: -------------- array(3) { ["id"]=> int(1) ["txt"]=> string(0) "" ["txtmax"]=> NULL } array(3) { ["id"]=> int(2) ["txt"]=> NULL ["txtmax"]=> NULL } ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=41539&edit=1