mssql_bind empty string converting to NULL

Question

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 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)

Thank you

Answer 1

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...

Answer 2

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! :)

Answer 3

Assuming you do not want to do what you suggested, options left to you are

1. Download a cvs snapshot of php and install it if viable, see if it is fixed. If not viable or fixed via cvs then...
2. Use system() or exec() calls to use the stored procedures. If too much a hassle then...
3. Don't use stored procedures, do your functionality in php or other scripting code.
4. Alter the stored procedure to accept another value for '' strings and convert THAT to a '' string.
5. Do what you didn't want to do. Harsh :P But I do not see another way.

Answer 4

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.