|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2007-05-30 09:27 UTC] phpbug dot 41539 at sub dot noloop dot net
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 } PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Dec 26 13:01:30 2024 UTC |
So I had a go at hacking on the C source for the mssql extension, and I think I have come up with some sort of solution. First of all, it appears that the regular FreeTDS dblib api has no way to distinguish "" from NULL for varchar(max). dbdatlen() returns 0 either way, and dbdata() returns a pointer to 0x0. Looking at the source code for the FreeTDS tsql application, it became obvious that when result set columns contains a real NULL, the "column_cur_size" is always less than 0. Relevant piece of code from tsql.c: if (col->column_cur_size < 0) { if (print_rows) fprintf(stdout, "NULL\t"); continue; } Unfortunately, negative column_cur_size is masked in dblib. Relevant piece of code from dblib.c's implementation of dbdatlen(): if (colinfo->column_cur_size < 0) ret = 0; else ret = colinfo->column_cur_size; So it seems we need to access the low level TDS structures to tell the difference. Playing around with gdb, it seems that column_cur_size is 0 for strings and -1 for real NULLs. So I came up with an ugly hack for php_mssql.c. It does produce the expected output, but it's been a long time since I did anything serious in C, so I don't know if it is the correct way of doing things. Tested on Windows 2003 x86, compiled with Microsoft Visual C++ 2005 (Compiler version 14.00.50727.42 for 80x86), FreeTDS 0.64, running against an instance of SQL Server 2005, and c:\freetds.conf contains: [global] tds version = 7.0 I also added a "dbdata(..) == NULL" condition to the if() in php_mssql_get_column_content_without_type(), just because that function seems to be similar to what was fixed in http://cvs.php.net/viewcvs.cgi/php-src/ext/mssql/php_mssql.c?r1=1.152.2.13.2.2&r2=1.152.2.13.2.3&pathrev=PHP_5_2 but I don't know if it is necessary or even correct. Here is the diff against PHP 5.2.3. diff -u /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.c ./php_mssql.c --- /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.c 2007-02-24 03:17:25.000000000 +0100 +++ ./php_mssql.c 2007-06-04 15:37:25.265625000 +0200 @@ -814,8 +814,21 @@ static void php_mssql_get_column_content_with_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC) { if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) { - ZVAL_NULL(result); - return; +#ifdef HAVE_FREETDS + /* double check that it is a real null, it could also be a zero-length varchar(max) string */ + TDSCOLUMN *colinfo; + TDSRESULTINFO *resinfo; + + resinfo = ((DBPROCESS*)(mssql_ptr->link))->tds_socket->res_info; + colinfo = resinfo->columns[offset-1]; + if (colinfo->column_cur_size < 0) { +#endif + ZVAL_NULL(result); + return; +#ifdef HAVE_FREETDS + } +#endif + } switch (column_type) @@ -935,7 +948,7 @@ static void php_mssql_get_column_content_without_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC) { - if (dbdatlen(mssql_ptr->link,offset) == 0) { + if (dbdata(mssql_ptr->link,offset) == NULL && dbdatlen(mssql_ptr->link,offset) == 0) { ZVAL_NULL(result); return; } diff -u /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.h ./php_mssql.h --- /devel2/x2www/src/php-5.2.3/ext/mssql/php_mssql.h 2007-01-01 10:36:03.000000000 +0100 +++ ./php_mssql.h 2007-06-04 15:37:07.343750000 +0200 @@ -31,8 +31,17 @@ #define PHP_MSSQL_API #endif +#if HAVE_FREETDS +/* mega hack to get hold of TDSCOLUMN->column_cur_size to check for NULL column values in VARCHAR(MAX) */ +#define _FREETDS_LIBRARY_SOURCE 1 +#include <tds.h> +#include <sybfront.h> +#include <sybdb.h> +#include <dblib.h> +#else #include <sqlfront.h> #include <sqldb.h> +#endif typedef short TDS_SHORT; #ifdef HAVE_FREETDS Finally, does anyone know if there's a chance that php_dblib.dll will be distributed with the regular php.net win32 binary release in the future?Patch Developed w/ freetds maintainer to fix this issue --- mssql/php_mssql.c.orig 2006-04-04 14:49:12.000000000 -0400 +++ mssql/php_mssql.c 2006-10-24 16:41:18.000000000 -0400 @@ -818,7 +818,7 @@ static void php_mssql_get_column_content_with_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC) { - if (dbdatlen(mssql_ptr->link,offset) == 0) { + if (dbdatlen(mssql_ptr->link,offset) == 0 && dbdata(mssql_ptr->link,offset) == NULL) { ZVAL_NULL(result); return; } @@ -941,7 +941,7 @@ static void php_mssql_get_column_content_without_type(mssql_link *mssql_ptr,int offset,zval *result, int column_type TSRMLS_DC) { - if (dbdatlen(mssql_ptr->link,offset) == 0) { + if (dbdatlen(mssql_ptr->link,offset) == 0 && dbdata(mssql_ptr->link,offset) == NULL) { ZVAL_NULL(result); return; }I compiled (freetds 0.64 + post64.diff.gz) and then recompiled my (unmodified) php 5.2.5 using "--with-mssql=/path/to/the/freetds/installation", and whatever you did in post64.diff.gz seems to fix the problem. The test case in my original submission now prints: array(3) { ["id"]=> int(1) ["txt"]=> string(0) "" ["txtmax"]=> string(0) "" } array(3) { ["id"]=> int(2) ["txt"]=> NULL ["txtmax"]=> NULL } as expected. Thank you very much! Is there a timeline/ETA for the next stable release of FreeTDS (0.65)? Also, if any php developers are reading this: It would be great if the win32 binary distribution of PHP could be compiled and linked with FreeTDS instead of the Microsoft DBLIB libraries :)