Using typed bound parameters with PHP PDO-ODBC, unixODBC and FreeTDS

Question

Hi,

I'm using 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

Unparametrized queries work fine. 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 columns 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 ? If yes, do they work using PHP PDO and hwo can I configure it ?

Answer 1

On the FreeTDS mailing list, I got the confirmation that SQLDescribeParam is not supported in FreeTDS. But when SQLDescribeParam is not supported, PDO_ODBC is to blame for using LONGVARCHAR (ie. text).

The same code worked on a Windows workstation with PDO ODBC (PHP Version 5.2.9, ODBC library Win32)

A workaround for this issue to treat every parameter as LONGVARCHAR and use explicit type conversion in queries. MS SQL Server only supports LONGVARCHAR => *CHAR conversions. To convert, I had to use thing like CAST(CAST(:number AS varchar) AS INTEGER) or CAST(CAST(:birthdate AS varchar) AS datetime). It is bad, ugly and probably a performance hog but it works.