Re: Are typed bound parameters working with unixODBC/FreeTDS

2010/9/30 Pierre Buyle <[email protected]>

> Hi,
>
> I use the following setup to access a MS-SQL database from a PHP
> application
> - RedHat Enterprise Linux 5
> - PHP 5.2.14 with PDO and PDO_ODBC
> - unixODBC 2.2.11
> - FreeTDS 0.82.1.dev.20100810
>
> Unparametrisze queries work fine. I can execute basic queries. The
> only issue is being forced to close cursor on single result statements
> (with PDOStatment::closeCursor) to avoid "0 [FreeTDS][SQL Server]
> Invalid cursor state (SQLSTATE=24000)" errors.
>
> But I'm having a major issue with typed bound parameter. When using
> code like this:
>
> $stmt = $PDO->prepare('INSERT INTO table (column1, column2)  VALUES
> (:foo, :bar');
> $stmt->bindValue(':foo', 21, PDO::PARAM_INT);
> $stmt->bindValue(':bar', 42, PDO::PARAM_INT);
> $stmt->execute():
> if (!$stmt->execute()) {
>  var_dump($stmt->errorInfo();
> }
>
> Where both column are INT. I get the an "206 [FreeTDS][SQL
> Server]Operand type clash: text is incompatible with int
> [SQLSTATE=22018]" error.
>
> In the unixODBC log, I get something like
>
> [ODBC][26251][SQLDescribeParam.c][175]
>               Entry:
>                       Statement = 0x2b73c849fb80
>                       Parameter Number = 1
>                       SQL Type = 0x7fff9c89e15e
>                       Param Def = 0x7fff9c89e154
>                       Scale = 0x7fff9c89e15c
>                       Nullable = 0x7fff9c89e15a
> [ODBC][26251][SQLDescribeParam.c][276]Error: IM001
> [ODBC][26251][SQLBindParameter.c][193]
>               Entry:
>                       Statement = 0x2b73c849fb80
>                       Param Number = 1
>                       Param Type = 1
>                       C Type = 1 SQL_C_CHAR
>                       SQL Type = -1 SQL_LONGVARCHAR
>                       Col Def = 4000
>                       Scale = 5
>                       Rgb Value = 0x2b73c941f890
>                       Value Max = 0
>                       StrLen Or Ind = 0x2b73c93fa1b0
> [ODBC][26251][SQLBindParameter.c][339]
>               Exit:[SQL_SUCCESS]
>
> My understanding of the log is that unixODBC is trying to bind the
> parameters using the right type. But the FreeTDS doesn't support the
> function (IM001 is 'Driver does not support this function'). So
> unixODBC continue without proper typing.
>
> Can someone confirm this diagnosis or, better, a known issue with
> typed bound parameter in FreeTDS ?
>

A bit similar to
http://stackoverflow.com/questions/3828215/using-typed-bound-parameters-with-php-pdo-odbc-unixodbc-and-freetds:)

I don't know how PDO works but surely ODBC does not support ":foo" as
parameter and as ODBC specification only RPC (store procedures) support
named parameters so PHP should convert ":foo" to "?".
Our driver does not support SQLDescribeParam but there is no reason why PDO
should pass the integer value as a LONGVARCHAR as it seems to do from log.
The ODBC trace reported however is a bit too should, it would be better a
trace that include SQLPrepare and SQLExecute.

freddy77