Paramater Datatype Lookup returns incorrectly - by Michael Thomas -Mockodin-

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 527188 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/26/2010 10:08:13 AM
Access Restriction Public


Context: querying from Windows 2008 running Perl

Driver: SQL Server Native Client 10.0 (Microsoft SQL Server 2008 Feature Pack - April 2009)

Perl Module DBD::ODBC
$dbh->selectall_arrayref("SELECT TOP 1001 userid FROM test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?, realname) > 0) AND disabledtext = ''",undef,$findme, $findme);

The Two parameters are being checked by the driver by issuing 

set fmtonly on select 0,0 from test_me_i_am_broken where 1=2 set fmtonly off

The test for 0 seems to indicate that the driver is returning a int datatype instead of the actual datatype for parameter 1 of CHARINDEX.

I have worked with the maintainer for DBD::ODBC and from what we can tell this is a bug in the SQL Server Native Client 10.0 Driver.
Sign in to post a comment.
Posted by ficuscr on 7/30/2012 at 1:01 PM
Has there been any movement on this? I am specifically trying to understand what my options are for PDO (PHP Data Object) support with MSSQL. Had hoped this would allow me to move away from DBLIB / freeTDS solution.
Posted by Larry Kuang on 9/22/2011 at 4:28 PM

Is there an alternative to this? I am encountering a similar issue. I am running PHP 5.3.5 on SuseEnterprise using FreeTDS unixODBC Driver. I am using SQL Server 2008 R2.

I can perform pre-composed query (e.g. select user_id, first_name, last_name from user where user_id=1), and that is fine.

However, when I use the bindValue or bindParam, it throws me an error. See code below:

$user_id = 1;
$stmt = $dbh->prepare("select user_id, first_name from user where user_id=:user_id");
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$result = $stmt->fetchAll(PDO::FETCH_OBJ);

Error from error log:
[Thu Sep 22 15:10:27 2011] [error] [client xxxxxx] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22018]: Invalid character value for cast specification: 206 [FreeTDS][SQL Server]Operand type clash: text is incompatible with int (SQLExecute[206] at /usr/src/packages/BUILD/php-5.3.5/ext/pdo_odbc/odbc_stmt.c:254)'

This is a very basic, common way to access data from the database, and I would be very surprise if Microsoft SQL Server 2008 R2 cannot support this with ODBC.

We have been using PDO_DBLIB with FreeTDS connecting to SQL Server 2008 R2 using PHP 5.2.14 on OpenSuse, and there are no problems. However, there is no support for PDO_DBLIB on PHP 5.3.X. We really want to upgrade to the newer version of PHP and to SuseEnterprise for security reasons, and this is prevent me to upgrade.

I really appreciate your inputs.

Posted by Michael Thomas -Mockodin- on 10/28/2010 at 11:50 AM
>This scenario is fixed in a future release of SQL Server.

So then from a end user perspective, it ... is ... not ... fixed, is it. Please return this issue to an Open status. When the issue has a released fixed, even it part of the fix is that an upgrade is required, then it becomes appropriate to mark an issue as Closed Fixed.
Posted by Microsoft on 6/16/2010 at 4:39 PM
Hi Mockodin,

You are correct that this issue is not fixed in SQL Server 2008 R2 release. This scenario is fixed in a future release of SQL Server. At this time we are not able to comment on the exact timing of the availability of the fix.

Jimmy Wu
Microsoft SQL Server
Posted by Michael Thomas -Mockodin- on 6/16/2010 at 1:24 PM
Not Fixed in R2

This issue is hardly closed.
Posted by Michael Thomas -Mockodin- on 4/14/2010 at 8:31 PM
Will R2 contain any changes to address this issue?
Posted by Michael Thomas -Mockodin- on 2/4/2010 at 5:39 PM
"Closed as Fixed"

Hmm Perhaps some information on this? In what version is it fixed? If SQL 2008 SP1 what hotfix or when will the fix be released? Or will we need to wait for R2?

What is the extent of the fix?; As this seems to effect the use of function in general... not just charindex.
Posted by Microsoft on 1/26/2010 at 5:28 PM
Thank-you for reporting this issue regarding using set fmtonly. We are aware of various scenarios in which set fmtonly does not return the proper results. We will add this scenario to the list as we investigate how we can improve and fix these scenarios.

Jimmy Wu
Microsoft SQL Server