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

Question

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 ?

Went

Until someone can give you a real (specific) solution you might want to try $PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); as a workaround so you can at least continue developing ;-) – Laughing

Sadly, when using $PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, true), I get another nice error: "SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes". – Went

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.

Went

on windows, using pdo->odbc->sql native client DSN and using casting from varchar to the appropriate datatype is still a 2x-100x performance increase over using PDO->ms sqlsrv php drivers. – Dolph

If you are using this solution, be aware that MS SQL will right truncate data at a length of 30 unless a data type length is specified in the CAST. See msdn.microsoft.com/en-ca/library/ms187928.aspx#Anchor_14 for more information. – Bales

Answer 2

I know that this is an old issue, but I wanted to post my solution for anyone having the same problem.

I was able to resolve this issue by changing my TDS Version from 7.1 to 7.2. I am not experiencing any issues anymore.

Hopefully this helps!

Priapitis

Thank you so much ... i've been already given up. Now it works perfectly. What I was doing wrong was the TDS Version. I've had 8.0 in my tds config, but 7.2 ist the latest. – Heedful

Answer 3

In my case, I had a call to a stored procedure, which returns records... but with some inserts in it... I managed to get it working by adding:

SET NOCOUNT ON

In your insert case, this could be similar, as the MSSQL outputs the result of the insert but PDO does not understand it.

Doing the cast as you said did not work for me.

An example on how to reproduce the behavior:

create procedure sptest 
    @var int
as
begin 
    create table #tmp (var int)
    insert into #tmp values (@var)
    select * from #tmp
end

And the PHP code:

$query = "exec sptest @var=:var";
$dbh = new PDO (SQLSVR_DRIVER);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$rs = $dbh->prepare($query);
$rs->bindParam('var', 1, PDO::PARAM_INT);
$rs->execute();
while ($row = $rs->fetch(PDO::FETCH_ASSOC))
    var_dump($row);

The fix is:

create procedure sptest 
    @var int
as
begin 
    set nocount on
    create table #tmp (var int)
    insert into #tmp values (@var)
    select * from #tmp
end

That's it!

Casern