PHP-SQLSRV Stored Procedure with input variable of type `bit`

Question

I have to port a LAMP-based project that uses PHP-MSSQL to connect to a SQL Server db. It has to be ported to a WAMP-based machine that uses PHP-SQLSRV to connect to a SQL Server db.

I can get/set data by executing stored procedures. It seems like most types of input variables are pretty simple to get working. But I cannot get input params of type bit to work.

I have tried using various values in PHP that are 'truthy' or 'falsey', but non of them get me past the error:

'truthy' things that didn't work:
- 1
- "1"
- true
- "True"
'falsey' things that didn't work:
- 0
- "0"
- false
- "False"

I have tried using various ways to add an item to the $params array, including omitting optional arguments as well as defining the PHP and SQL data types. Nothing works.

My error message from SQL Server (converted to a JSON object) is:

[{
  "0":"42000",
  "SQLSTATE":"42000",
  "1":8114,
  "code":8114,
  "2":"[Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type varchar to bit.",
  "message":"[Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type varchar to bit."
}]

To generate this error message, I passed a value of 0 or 1, neither of which is a varchar or even a string.

To maintain maximum compatibility I do not want to alter the Stored Procedure to Cast/Convert data on SQL Server. I want this to work from PHP keeping Stored Procedure unchanged.

PHP code:

<?php
  $link = sqlsrv_connect($host,Array("Database"=>$database,"UID"=>$username,"PWD"=>$password));
  $sql = " { call StoredProcedureName ( @input_param_of_bit_type=? ) } ";
  $param1 = 1;
  $params = Array(
    Array(&$param1, SQLSRV_PARAM_IN)
  );
  $stmt = sqlsrv_prepare($link,$sql,$params);
  if ($stmt===false) {
    // handle error
    print_r(sqlsrv_errors,true);
  } else {
    if (sqlsrv_execute($stmt)===false) {
      // handle error.  This is where the error happens
      print_r(sqlsrv_errors,true);
    } else {
      // success! It never gets here, though.
    }
  }
?>

Might have to modify the queries to include a CAST(0 AS BIT) to force the bit datatype. — Marc B
– Marc B, Commented Oct 24, 2011 at 17:36
@Marc B: actual SQL query lives in Stored Procedure on SQL Server. I want to keep Stored Procedures unchanged. — Teddy
– Teddy, Commented Oct 24, 2011 at 17:50
Please post the calling PHP, the problem probably lies there, especially if you're parameterizing your proc call. — Code Magician
– Code Magician, Commented Oct 24, 2011 at 18:58

Teddy · Accepted Answer · 2011-10-25 19:23:47Z

1

Re-ordering the order of the named parameters made this work. I had to use SQL Server Management Studio software and Profile Tracing to see what was different about executing the Stored Procedure from php-sqlsrv vs. executing it directly from within SQL Server Management Studio.

After I put the input parameters in the exact order used by SQL Server Management Studio, everything worked OK.

answered Oct 25, 2011 at 19:23

Teddy

18.6k4 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kop48 · Accepted Answer · 2011-10-25 17:20:09Z

0

The appears to be that the T-SQL above is using the default string type, which is being sent to the server in a way that won’t convert. The best fix is to specify the PHPTYPE and SQLTYPE in the parameter:

$params = Array( Array(&$param1, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_BIT) );

Please try that change and see if your query executes correctly.

Thanks!

edited Oct 25, 2011 at 17:20

answered Oct 24, 2011 at 20:05

kop48

4953 silver badges8 bronze badges

2 Comments

Teddy Over a year ago

Tried this, but it made no difference. I can execute other Stored Procedures using named parameters. It is just the Stored Procedures that have input params that are of type bit that cause the error.

Teddy Over a year ago

I have tried this, but there error is still occurring. Can you verify that I haven't found a bug in php-sqlsrv? That is, can you execute a Stored Procedure that takes an input param of type bit?

Collectives™ on Stack Overflow

PHP-SQLSRV Stored Procedure with input variable of type `bit`

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related