ID: 41539
User updated by: frode at coretrek dot com
Reported By: frode at coretrek dot com
Status: Assigned
Bug Type: MSSQL related
-Operating System: Linux
+Operating System: Linux and win32
-PHP Version: 5.2.2
+PHP Version: 5.2.3
Assigned To: fmk
New Comment:
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?
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
