Bit-parameter with PHP 7.0.5 x64 NTS and SQLSRV 4.0.3 x64

[eskimoe]

It seems the 4.0.3 version of the PHP7-branch has problems with bit-parameters...
I am using PHP 7.0.5 x64 NTS, plus the 4.0.3 SQLSRV x64.
When using the following code:

$tsql = <<<SQL
DECLARE @bit_true bit = ?, @bit_false bit = ?, @bit_cast_true bit = CAST(? AS bit),
   @int_true int = ?
SELECT 'bit_true'=@bit_true, 'bit_false'=@bit_false, 'bit_cast_true'=@bit_cast_true,
   'int_true'=@int_true, 'direct_true'=?, 'direct_false'=?,
   'direct_bit_cast_true'=CAST(? AS bit)
SQL;
$stmt = sqlsrv_query($conn, $tsql, [true, false, true, true, true, false, true]);
$row = sqlsrv_fetch_object($stmt);
echo '<pre>';
var_dump($row);
echo '</pre>';

Output with PHP 5 and the old sqlsrv:

object(stdClass)#5 (7) {
  ["bit_true"]=>int(1)
  ["bit_false"]=>int(0)
  ["bit_cast_true"]=>int(1)
  ["int_true"]=>int(1)
  ["direct_true"]=>int(1)
  ["direct_false"]=>int(0)
  ["direct_bit_cast_true"]=>int(1)
}

Output with PHP7 and the new sqlsrv:

object(stdClass)#5 (7) {
  ["bit_true"]=>int(1)
  ["bit_false"]=>int(1)
  ["bit_cast_true"]=>int(1)
  ["int_true"]=>int(2)
  ["direct_true"]=>int(2)
  ["direct_false"]=>int(2)
  ["direct_bit_cast_true"]=>int(1)
}

As you can see, even the old sqlsrv interprets the parameters as ints, and returns int-values, too,
but at least it reads the parameters correctly...
With PHP7, it only does weird things! when casting to bit with t-sql, it uses '1' for true AND false,
when casting to int, or using the bits directly, it uses '2' for both true and false??
Thanks!

[meet-bhagdev]

@eskimoe good catch! We were able to repro this. Will work towards fixing it.

[eskimoe]

Thanks! One other thing, didn't want to open a new ticket, but just say so if I should...
It's regarding the sqlsrv constants, specifically the sql-data-type constants:
with the old sqlsrv-extension, there were the constants SQL_, for example SQL_BIT or
SQL_DECIMAL... one could use them for testing column-types with sqlsrv_field_metadata for example! (they are listed in the helpfile under sqlsrv_field_metadata);
Additionally, there are the SQLSRV_SQLTYPE_-constants (to be found in the help,
Constants-Category!), and those are present in the new sqlsrv, too!
I think they both had the same int-values, but I am not entirely sure... weird anyway, having two constants for the same sqltype!

Anyways, most of the SQL_*-constants I could replace with it's SQLSRV_SQLTYPE-counterpart,
but not SQL_DECIMAL (== 3)... SQLSRV_SQLTYPE_DECIMAL doesn't seem to exist!

[meet-bhagdev]

@eskimoe : SQLSRV_SQLTYPE_DECIMAL should exist. Can you give us an example of what you’re trying to do? Also, which version of the sqlsrv extension are you referring to as the old one?

[eskimoe]

Here we go:

$tsql = 'SELECT CAST(1.1 AS decimal(5,2))';
$stmt = sqlsrv_query($conn, $tsql);
$fieldMetadata = sqlsrv_field_metadata($stmt);
echo '<pre>';
var_dump($fieldMetadata);
echo '===3 ? ' . ($fieldMetadata[0]['Type'] == 3 ? 'true' : 'false') . "\r\n";
echo '==SQL_DECIMAL ? ' . ($fieldMetadata[0]['Type'] == SQL_DECIMAL ? 'true' : 'false') . "\r\n";
echo '==SQLSRV_SQLTYPE_DECIMAL ? ' . ($fieldMetadata[0]['Type'] == SQLSRV_SQLTYPE_DECIMAL ? 'true' : 'false') . "\r\n";
echo '</pre>';

When executing this code on php 7.0.5 with sqlsrv 4.0.3, only the first condition matches (==3),
and I get two "Use of undefined constant" notices.
When using PHP 5.6.20 with sqlsrv 3.2, the first and second conditions match, and I get one notice about SQLSRV_SQLTYPE_DECIMAL being undefined.

[meet-bhagdev]

Hi eskimoe,

Seems like this behaviour is casued by the fact that PHP 7 does not ship with php-odbc by default. Here is a stack overflow link that might help our casue here - http://stackoverflow.com/questions/34200997/php-7-0-odbc-driver-for-windows
Essentially once you add the php-odbc.dll in the php.ini file under the dynamic extensions, and --enable-odbc=shared flag in the config, you should be able to resolve the aforementioned issue. The PHP download page has two packages, php source, and binary distribution; php-odbc.dll is included in the binary distribution

Let us know if you are still running into issues and we will work through it.

Cheers,
Meet

[meet-bhagdev]

@eskimoe The issue with binding the bit parameter should be now fixed! Check out our latest release and keep us posted! :)

[eskimoe]

okay, the bit-binding seems to work, very nice!

regarding the sqltype constants, you are right of course, all the constants beginning with "SQL_" come from the odbc-extension!
While comparing the constants beginning with "SQLSRV_SQLTYPE_" in sqlsrv 3.2 to the ones from this beta-driver, I made the following discoveries:

First, SQLSRV_SQLTYPE_BINARY doesn't exist in either sqlsrv-extension.

Additionally, all the sqltypes that require a parameter in the sqlserver, like:
SQLSRV_SQLTYPE_CHAR, SQLSRV_SQLTYPE_DECIMAL, SQLSRV_SQLTYPE_VARCHAR
don't exist either, or at least give back some weird numbers, when used like SQLSRV_SQLTYPE_VARCHAR('max'), as described in your (old) SQLSRV_Help.chm!

And finally, a lot of the sqltypes have weird and incorrect values in both versions, like smalldatetime = 8285 in sqsrv 3.2, and 8781060644957 in sqlsrv 7.0.6, but your sqlsrv_field_metadata returns ["Type"]=>int(93) for it!
The same goes for all the date/time types (like timestamp, date, time, dtoffset etc), and the money-types, as far as I can see... thanks!

[eskimoe]

Oh, and I used get_defined_constants(true) for that...

[meet-bhagdev]

Hi @eskimoe , we believe this issue is fixed in our latest RTW release - https://github.com/Azure/msphpsql/releases/tag/v4.0-RTW. Can you help us validate?