1

Closed

fetch does not return BIT column default value in SQL 2005

description

Create a table with a 'bit' type column with a default value. Query the INFORMATION_SCHEMA using PHP/PDO sqlsrv driver. In SQL Server 2005, the Column_Default column is not returned by fetchAll(). In SQL Server 2008, for the exact same configuration and exact same database (backed up and restored to SQL Server 2008 on the same machine), the Column_Default is returned properly.

create table test (
bit_column bit default 1,
varchar_column varchar(10)
);

Here's the SQL (from Yii framework / CMSSqlSchema.findColumns )

SELECT *, columnproperty(object_id(table_schema+'.'+table_name), column_name, 'IsIdentity') as IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Test' AND TABLE_SCHEMA='dbo';

Prepare() and execute(). Then $result=$statement->fetchAll(PDO::FETCH_ASSOC)

in SQL Server 2005, the $return array will not contain any Column_Default values
in SQL Server 2008, the $return array will contain a Column_Default value for bit_column with value 1

Environment:
Windows XP SP3 + all hotfixes
Apache 2.2.11
PHP 5.2.9-1
php_pdo_sqlsrv_52_ts_vc6.dll version 2.0.1802.200
SQL Server 2005 (and) SQL Server 2008 R2
Closed Nov 2, 2011 at 11:09 PM by
Fixed in SQL Server 2008 R2

comments

jguerin wrote Nov 2, 2011 at 11:08 PM

This issue is being manifested by the SQL Server Engine, and not the driver. Given this issue is fixed in a following version of SQL Server, I feel that we can close this issue as 'fixed'.