#40394 [Opn->Asn]: mssql_bind() converts empty strings to null

ID: 40394 Updated by: tony2001@php.net Reported By: rnerovich at gmail dot com -Status: Open +Status: Assigned Bug Type: MSSQL related Operating System: XP/2003 server PHP Version: 5.2.0 -Assigned To: +Assigned To: fmk New Comment: Assigned to the maintainer. Previous Comments: ------------------------------------------------------------------------ [2007-02-07 23:19:24] rnerovich at gmail dot com Just tried the new snapshot with the same result. 885: [02/07] [17:17:30] *** array ( 0 => 'ükÁ#ðÌæM†TèÇS±í°', 'ucode' => 'ükÁ#ðÌæM†TèÇS±í°', 1 => NULL, 'phone' => NULL, 2 => NULL, 'account' => NULL, 3 => '423998059-130628001', 'ealoc' => '423998059-130628001', 4 => '2007-02-07 17:17:21', 'time' => '2007-02-07 17:17:21', 5 => 'g÷r}Q\'N°¥@Iñ*¬', 'recid' => 'g÷r}Q\'N°¥@Iñ*¬', 6 => NULL, 'prbstr' => NULL, ) ------------------------------------------------------------------------ [2007-02-07 22:48:56] tony2001@php.net Please try using this CVS snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://snaps.php.net/win32/php5.2-win32-latest.zip ------------------------------------------------------------------------ [2007-02-07 22:36:18] rnerovich at gmail dot com Description: ------------ When using mssql_bind() to pass an empty string to a stored procedure, the empty string is converted to NULL. This problem has been reported by others, but either closed or marked as BOGUS, which is not the case, as I've reproduced this on both development and deployed systems. Reproduce code: --------------- CREATE PROCEDURE REC_PROC_INSERT_MS_INTF @UCODE UNIQUEIDENTIFIER, @PHONE VARCHAR(10), @ACCOUNT VARCHAR(25), @EALOC VARCHAR(38), @TIME DATETIME, @RECID UNIQUEIDENTIFIER, @PRBSTR VARCHAR(30) AS SELECT @UCODE as ucode, @PHONE as phone, @ACCOUNT as account, @EALOC as ealoc, @TIME as time, @RECID as recid, @PRBSTR as prbstr RETURN 1 GO $sp = mssql_init('REC_PROC_INSERT_MS_INTF',$dblink->dbresource); mssql_bind($sp,"RETVAL",&$rc,SQLINT4,TRUE,TRUE,4); mssql_bind($sp,"@UCODE",$ucode,SQLVARCHAR,FALSE,FALSE,strlen($ucode)); mssql_bind($sp,"@PHONE",$phone,SQLVARCHAR,FALSE,FALSE,10); mssql_bind($sp,"@ACCOUNT",$account,SQLVARCHAR,FALSE,FALSE,25); mssql_bind($sp,"@EALOC",$ealoc,SQLVARCHAR,FALSE,FALSE,38); mssql_bind($sp,"@TIME",$currtime,SQLVARCHAR,FALSE,FALSE,strlen($currtime)); mssql_bind($sp,"@RECID",$recId,SQLVARCHAR,FALSE,FALSE,strlen($recId)); mssql_bind($sp,"@PRBSTR",$problem,SQLVARCHAR,FALSE,FALSE,30); $result = mssql_execute($sp); if($result){ $row = mssql_fetch_array($result); global $log; $log->Writelog(var_export($row,true)); } Expected result: ---------------- I would expect the empty strings to stay empty. This causes major problems with NON-NULLABLE SQL fields!!! array ( 0 => 'ükÁ#ðÌæM†TèÇS±í°', 'ucode' => 'ükÁ#ðÌæM†TèÇS±í°', 1 => '', 'phone' => '', 2 => '', 'account' => '', 3 => '423998059-130628001', 'ealoc' => '423998059-130628001', 4 => '2007-02-07 16:20:54', 'time' => '2007-02-07 16:20:54', 5 => 'g÷r}Q\'N°¥@Iñ*¬', 'recid' => 'g÷r}Q\'N°¥@Iñ*¬', 6 => '', 'prbstr' => '', ) Actual result: -------------- when any of the strings (not uniqueidentifiers) are set to an empty string ($phone = '') the following is received from the var_export() array ( 0 => 'ükÁ#ðÌæM†TèÇS±í°', 'ucode' => 'ükÁ#ðÌæM†TèÇS±í°', 1 => NULL, 'phone' => NULL, 2 => NULL, 'account' => NULL, 3 => '423998059-130628001', 'ealoc' => '423998059-130628001', 4 => '2007-02-07 16:20:04', 'time' => '2007-02-07 16:20:04', 5 => 'g÷r}Q\'N°¥@Iñ*¬', 'recid' => 'g÷r}Q\'N°¥@Iñ*¬', 6 => NULL, 'prbstr' => NULL, ) the GUIDS look like garbage, but are correct. The strings however are all NULL if they were sent in as '' This is what var_export() reports if all strings are set to ' ' (one space) array ( 0 => 'ükÁ#ðÌæM†TèÇS±í°', 'ucode' => 'ükÁ#ðÌæM†TèÇS±í°', 1 => ' ', 'phone' => ' ', 2 => ' ', 'account' => ' ', 3 => '423998059-130628001', 'ealoc' => '423998059-130628001', 4 => '2007-02-07 16:20:54', 'time' => '2007-02-07 16:20:54', 5 => 'g÷r}Q\'N°¥@Iñ*¬', 'recid' => 'g÷r}Q\'N°¥@Iñ*¬', 6 => ' ', 'prbstr' => ' ', ) ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=40394&edit=1