FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

The following problem with PHP was fixed.

> Subject: Bug #39213 [Asn->Csd]: NULL == '' in mssql extention
> 
> ATTENTION! Do NOT reply to this email!
> To reply, use the web interface found at
> http://bugs.php.net/?id=39213&edit=2
> 
> 
>  ID:               39213
>  Updated by:       fmk AT php.net
>  Reported By:      frediano dot ziglio at vodafone dot com
> -Status:           Assigned
> +Status:           Closed
>  Bug Type:         MSSQL related
>  Operating System: Linux
>  PHP Version:      5.1.6
>  Assigned To:      fmk
>  New Comment:
> 
> This bug has been fixed in CVS.
> 
> Snapshots of the sources are packaged every three hours; this change
> will be in the next snapshot. You can grab the snapshot at
> http://snaps.php.net/.
>  
> Thank you for the report, and for helping us make PHP better.
> 
> Microsofts libraries will return a length of 1 for the empty string.
> WHen the MSSQL extension is compiled with the FreeTDS library the
> returned values are correct for both empty string and null values.
> 
> 
> Previous Comments:
> --------------------------------------------------------------
> ----------
> 
> [2007-02-01 15:08:07] frode at coretrek dot com
> 
> We're running into the same issue. Is the patch that was provided by
> the bug reporter planned for inclusion in a future release?
> 
> --------------------------------------------------------------
> ----------
> 
> [2006-10-20 19:13:54] frediano dot ziglio at vodafone dot com
> 
> Mmm... perhaps a less invasive change is to add 
> 
> if (data == 0) {
>    ZVAL_NULL(result);
>    return;
> }
> 
> after 
> 
> char *data = charcol(offset);
> 
> line on the same function.
> 
> --------------------------------------------------------------
> ----------
> 
> [2006-10-20 19:08:15] frediano dot ziglio at vodafone dot com
> 
> Here you are a simplified version. It require a db but it create the
> only table it needs and delete it.
> 
> Expected result:
>   got --
> 
> <?php
> $conn = mssql_connect(<server>,<user>,<pass>) or die("opps");
> 
> mssql_query("CREATE TABLE #tmp(c VARCHAR(10) NULL)", $conn) or
> die("error querying");
> 
> mssql_query("INSERT INTO #tmp VALUES('')", $conn) or die("error
> querying");
> 
> $res = mssql_query("SELECT * FROM #tmp", $conn) or die("query");
> $row = mssql_fetch_assoc($res);
> 
> $s = is_null($row['c']) ? 'NULL' : $row['c'];
> echo "got -$s-\n";
> ?>
> 
> In
> http://cvs.php.net/viewvc.cgi/php-src/ext/mssql/php_mssql.c?vi
> ew=markup
> you have 
> 
> 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) {
> 
> note that under Windows Microsoft dblib use only older wire protocol
> version which do not support empty string so it returns them as a
> single space.
> 
> --------------------------------------------------------------
> ----------
> 
> [2006-10-20 15:25:38] tony2001 AT php.net
> 
> Thank you for this bug report. To properly diagnose the problem, we
> need a short but complete example script to be able to reproduce
> this bug ourselves. 
> 
> A proper reproducing script starts with <?php and ends with ?>,
> is max. 10-20 lines long and does not require any external 
> resources such as databases, etc. If the script requires a 
> database to demonstrate the issue, please make sure it creates 
> all necessary tables, stored procedures etc.
> 
> Please avoid embedding huge scripts into the report.
> 
> 
> 
> --------------------------------------------------------------
> ----------
> 
> [2006-10-20 15:16:45] frediano dot ziglio at vodafone dot com
> 
> Description:
> ------------
> when you get data you assume NULL if dbdatlen == 0 however is possible
> that data is just an empty string (''), in this case you should check
> that dbdatlen == 0 and dbdata == NULL.
> 
> Reproduce code:
> ---------------
> I use this test script (pwd.inc define just variable to connect)
> 
> <?php
> 
> // $Id: null.php,v 1.2 2006/10/20 14:38:22 freddy77 Exp $
> 
> require_once("pwd.inc");
> 
> $conn = mssql_connect($server,$user,$pass) or die("opps");
> 
> mssql_query("CREATE TABLE #MyTable (
>    myfield VARCHAR(10) NULL,
>    n INT
> )", $conn) or die("error querying");
> 
> 
> mssql_query("INSERT INTO #MyTable VALUES('',1)
> INSERT INTO #MyTable VALUES(NULL,2)
> INSERT INTO #MyTable VALUES(' ',3)
> INSERT INTO #MyTable VALUES('a',4)", $conn) or die("error querying");
> 
> $result = 0;
> 
> function test($sql, $expected)
> {
>         global $conn, $result;
> 
>         $res = mssql_query($sql, $conn) or die("query");
>         $row = mssql_fetch_assoc($res);
>         $s = $row['myfield'];
> 
>         if (is_null($s))
>                 $s = '(NULL)';
>         else if ($s == '')
>                 $s = '(Empty String)';
>         else
>                 $s = "'".str_replace("'", "''", $s)."'";
>         echo "$sql -> $s\n";
>         if ($s != $expected)
>         {
>                 echo "error!\n";
>                 $result = 1;
>         }
> }
> 
> test("SELECT top 1 * FROM #MyTable WHERE n = 1 -- ''", "(Empty
> String)");
> 
> test("SELECT top 1 * FROM #MyTable WHERE n = 2 -- NULL", "(NULL)");
> 
> test("SELECT top 1 * FROM #MyTable WHERE n = 3 -- ' '", "' '");
> 
> test("SELECT top 1 * FROM #MyTable WHERE n = 4 -- 'a'", "'a'");
> 
> exit($result);
> ?>
> 
> 
> Expected result:
> ----------------
> SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> (Empty String)
> 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'
> 
> 
> Actual result:
> --------------
> SELECT top 1 * FROM #MyTable WHERE n = 1 -- '' -> (NULL)
> 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'
> 
>