mssql_bind empty string converting to NULL
I am currently using SQL Server 2000 Stored Procedures with PHP. Following the PHP doc, I use mssql_bind to assign value of parameters and then execute the Stored Procedure.
The problem is that I got this bug which prevents me to bind empty strings to parameters (they are converted to NULL when the Stored Proc gets called)
I dont't want to have to convert every parameter inside the Stored Procedures to check if its NULL then convert back to an empty string. I want to be able to use empty strings as well as NULL which both have their own meaning.
Does anyone know a workaround开发者_StackOverflow社区 for this in PHP ? PHP bugs website isn't very clear if that bug is fixed or when it will be fixed, I use PHP 5.2.11 (the lastest version from the 5.2 branch)
Assuming you do not want to do what you suggested, options left to you are
- Download a cvs snapshot of php and install it if viable, see if it is fixed. If not viable or fixed via cvs then...
- Use system() or exec() calls to use the stored procedures. If too much a hassle then...
- Don't use stored procedures, do your functionality in php or other scripting code.
- Alter the stored procedure to accept another value for '' strings and convert THAT to a '' string.
- Do what you didn't want to do. Harsh :P But I do not see another way.
The bug #44325 proposed a patch to solve the problem, but it was not a correct solution, as it seems the problem is in ntwdblib.dll, and not in the code of the php_mssql extension.
See the comment from alexr at oplot dot com :
I'm sorry. This bug is not fixed. This is a bug of the ntwdblib.dll. And after latest updates the bug is return.
When I bind a empty string, the stored procedure received a chr(0) char instead a empty string.
Please roll back last fixes. This bug is irrecoverable.
Considering this, I'm not sure there's much that could be done on the PHP side :-(
Last time I worked with PHP+MSSQL on Windows, I had quite a lot of problems, like some quite similar to this one, actually ;-(
Maybe a possible solution, for you, might be to switch to the newer SQL Server Driver for PHP driver ? Note that is only works on Windows, though...
I personally don't have a MSSQL server available to test but have you tried using PDO instead of mssql directly?
PDOStatement->bindValue( mixed $parameter , mixed $value [, int $data_type ] )
bindValue()
Yes, I realize that PDO is only a wrapper but who knows, could work! :)
I have a solution that will work.
I am using ADODB (but that doesnt matter) and I have hacked the mssql driver (adodb-mssql.inc.php in the adodb5 drivers folder).
here is the code
if($var === ''){
//stupid hack to prevent mssql driver from converting empty strings to null. now they arent empty strings but they will get trimmed down to legth 0 ;-)
$var = ' ';
$type = SQLVARCHAR;
$isNull = false;
$maxLen = 0;
}
simply check if you are trying to bind an empty string and if you are change it to be non empty. it doesn't matter what value you use I just use a space.
make the type a sqlvarchar isnull should be false and now the trick is to make maxlen 0 so that the mssql driver will trim your string to an empty string.
those variables should get passsed to the mssql_bind function if that wasn't obvious.
精彩评论