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 and win32
PHP Version: 5.2.3
Assigned To: fmk
New Comment:
I don't get it - the patch you posted is partly already in 5.2.3, i.e.
it's already there for php_mssql_get_column_content_with_type. But not
in php_mssql_get_column_content_without_type. But that's not the real
issue here:
As I mentioned in the original submission, bug 39213 fixed the problem
for most datatypes but doesn't help for the datatype NVARCHAR(MAX).
That's because FreeTDS does not return NULL for dbdata() on an
NVARCHAR(MAX). Please, take another look at my second comment to this
bug, where I posted an ugly work-around that requires the use of
FreeTDS' underlying libTDS, which seems to be the only API that can tell
the difference between a NULL and a "" in BLOBs (which is what the
NVARCHAR(MAX) datatype basically is); the difference is that the size is
-1 for NULL values, and 0 for "" values, but the dblib API converts -1
to 0 in dbdatlen().
Thanks again for looking at this bug a second time :)
Previous Comments:
------------------------------------------------------------------------
[2007-06-18 20:27:16] erudd at netfor dot com
Isn't this a dup of #39213.
------------------------------------------------------------------------
[2007-06-18 20:16:31] erudd at netfor dot com
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;
}
------------------------------------------------------------------------
[2007-06-04 14:16:12] frode at coretrek dot com
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?
------------------------------------------------------------------------
[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
