Search

odbc client/MSSQL does not work with bound parameters in subquery by aisohaikens

Closed
as Fixed Help for as Fixed

19
0
Sign in
 to vote
Type: Bug
ID: 521409
Opened: 12/20/2009 11:41:50 AM
Access Restriction: Public
0
Workaround(s)
15
User(s) can reproduce this bug
I use the SQL server 2008 native client 10.0 to connect (via odbc) my php program to a SQL Server 2008 express edititon. Everything works fine except for specifying bound parameters within subqueries, such as this one:
$q = 'select f.id from (select id from foo where id = ?) as f';
the error message I get is:
SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Native Client][SQL Server]Er is een conflict met het type operand ontstaan: text is incompatibel met int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:254)
(in english this is: 'there is a conflict with the type operand: text is incompatible with int')

If I use e.g. this query:
$q = 'select f.id from foo f where id=?';
or e.g. this one
$q = 'select f.id from (select id from foo where id = 1) as f';
everything works fine and I get id=1 as a result.

Note, that if I use the same code but with another database like mysql (using the odbc mysql driver v3.51) this works fine, so it isn't the pdo_odbc code from php.
The same error occurs when using the SQL server driver for PHP version 1.1.
The same error occurs when using the 'SQL Server' driver in the dsn instead of SQL Server Native Client 10.0.

I use this $dsn: "Driver={SQL Server Native Client 10.0};Server={localhost\SQLEXPRESS};Database={foo};Uid={[user]};Pwd={[password]};";

There is a similar report about this problem filed in the bug list of php. But this is clearly no bug in PHP but in SQL Server driver. You can find the php bug report here: http://bugs.php.net/bug.php?id=36561. They found out that using a profiler the sql that SQL Server driver produces is N'@P1 text' where it should be N'@P1 int'.
Apperantly this is a bug since beginning of 2006 and it was already in SQL Server 2005. So in SQL Server 2008 this is still not solved.
The problem with this is that ORM's cannot work (via odbc) with SQL Server, since they work frequently with these kind of sql statements, like doctrine. This is a big problem for people who are forced to work with a SQL Server
Details (expand)
Product Language
English

Version

SQL Server 2008 - Express Edition

Category

SQL Express

Operating System

Windows XP SP2 Professional
Operating System Language
Dutch
Steps to Reproduce
1. setup a database 'foo' in SQL Server with a table 'foo' which contains an id column and fill it with '1':
create database foo;
use foo;
create table foo (id INT NOT NULL);
insert into foo values (1);
2. run this php script (fill in the [user] and [pwd])
<?php
$dsn = "odbc:Driver={SQL Server Native Client 10.0};Server={localhost\SQLEXPRESS};Database={foo};Uid={[user]};Pwd={[pwd]};";
$dbh = new PDO($dsn);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$q = 'select f.id from (select id from foo where id = ?) as f';
$stmt = $dbh->prepare($q);
$stmt->bindParam(1, $a = 1);
$stmt->execute();
print_r($stmt->fetch(PDO::FETCH_ASSOC));
Actual Results
an exception occurs:

SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][SQL Native Client][SQL Server]Er is een conflict met het type operand ontstaan: text is incompatibel met int (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:254)

(in english this is: 'there is a conflict with the type operand: text is incompatible with int')
Expected Results
Array
(
    [id] => 1
)

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by Abunet on 5/4/2013 at 1:52 PM
Hi,
I'd like to know the status of this Bug. It seems not resolved yet.

I still receive the errror:
[Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification (SQLExecute[0] at /tmp/buildd/php5-5.3.3/ext/pdo_odbc/odbc_stmt.c:254)

The error is returned calling a simple odbc query in php on a debian system, when I execute a query with a named parameter

PHP SCRIPT:

<?php
    $username = "sa";
    $password = "cnr";
    $database = "atomos";
    $server = "sviluppo\\sql2008";
    try {

     $dbh = new PDO("odbc:Driver=SQL Server Native Client 11.0;Server=192.168.100.53\sql2008,1433;Trusted_Connection=no;port=1433;Database=ATOMOS;Regional=No;", $username, $password );
    
     $idrecord = 1;
    
     $sth = $dbh->prepare('SELECT * FROM TestTable WHERE id = ?');
     $sth->bindParam(1, $idrecord, PDO::PARAM_INT);
    
     $stat = $sth->execute();
    
     var_dump($sth->errorInfo());
    
     $result = $sth->fetchAll();
    
    } catch (PDOException $exception) {
        echo $exception->getMessage();
     exit;
    }

    unset($dbh);
?>

--- WEB SERVER:
- Debian 6 squeeze
- Microsoft® SQL Server® ODBC Driver 1.0 for Linux
- unixODBC-2.3.1
- PHP Version 5.3.3-7+squeeze14
- Apache/2.2.16 (Debian)

--- SQL SERVER MACHINE:
- Windows 2003 Server R2
- SQL Server 2008 R2 with Service Pack2

--- TEST TABLE SQL:

USE [ATOMOS]
GO

/****** Object: Table [dbo].[TestTable]    Script Date: 05/04/2013 22:46:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [id] [int] NOT NULL,
    [description] [nvarchar](50) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


As you can see, I can't use PDO_SqlSrv Drivers, that I've used many times without having this type of error, because the Web Server Stack is running on linux and PDO_SqlSrv is only for Windows.

Thanks in advance
Marco Roello
Posted by IanYates [Med IT] on 11/4/2010 at 8:07 PM
>>This bug is the SQL Server Native Access Client
>This is a invalid comment.
>
>This is is NOT fixed, this is also occurs in the Microsoft® SQL Server 2008 R2 Native Client
>The original poster made no reference to Access at all in fact.

The SQL Server Native Access Client, known as SNAC for short, has nothing to do with the MS Access database product. He(Jimmy from MS who answered the query) wasn't confusing things... I can see from your point of view where things got confused up as the word "access" is in its name and MS Access connectivity was raised as a separate issue...

Whilst I'm not a PHP guy, if they claim that it's been fixed then you should go get the SQL Server cumulative update for either 2008 or 2008R2. One of the cumulative update packages available for download is a standalone SNAC install. Give it a try and post back.
Posted by TechVsLife2 on 10/28/2010 at 9:32 PM
Has a date been set for the release of the fixed Sql Native Client odbc driver? There are a number of outstanding issues.

(The reference to Microsoft Access is only meant as an example of the problems arising from bugs in the sql server 2008 R2 and apparently 20008 native odbc drivers.)
Posted by Michael Thomas -Mockodin- on 10/28/2010 at 11:51 AM
For reference an issue I opened on what appears to the base issue:
https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly
Posted by Michael Thomas -Mockodin- on 10/28/2010 at 11:45 AM
>This bug is the SQL Server Native Access Client
This is a invalid comment.

This is is NOT fixed, this is also occurs in the Microsoft® SQL Server 2008 R2 Native Client
The original poster made no reference to Access at all in fact.
Posted by KRav3N on 9/14/2010 at 7:09 AM
AshayC: This has nothing to do with pdo_sqlsrv. pdo_sqlsrv works fine. The problem is with pdo_odbc. Both drivers should be viable options for connecting to MS SQL Server via php. The SQL Server Native Access Client should be fixed so that using pdo_odbc will work.
Posted by TechVsLife2 on 8/18/2010 at 12:08 AM
I think I ran across the same issue, using Microsoft Access and odbc to connect to sql server when editing time fields, see:
http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/489d3cb8-be33-4fb6-8903-1084872fb5e7

Posted by Microsoft on 8/3/2010 at 11:01 AM
We have identified the root cause and a fix will be available in an upcoming future release of the SQL Server Native Access Client.

Thanks again for reporting this issue to us.
Regards,
Jimmy Wu
Microsoft SQL Server
Posted by Microsoft on 7/28/2010 at 12:51 AM
This bug is the SQL Server Native Access Client, and has been reassigned to the correct owner to fix.
Posted by Michael Thomas -Mockodin- on 6/16/2010 at 1:29 PM
Issue also exists when accessing MSSQL from perl, is not consistent but occurs commonly with Function calls, system functions and custom CLR objects included.
Posted by Ashay Chaudhary [MSFT] on 5/19/2010 at 3:34 PM
Have you tried this with the recently released SQL Server Driver for PHP 2.0 CTP which adds support for PDO? If not, please do so and report back. Thanks.
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement