PHP PDO ODBC - The data types are incompatible in the equal to operator

Question

I don't seem to be able to get a simple WHERE clause with a parameter to work, I am constantly getting an error message saying:

[Microsoft][ODBC SQL Server Driver][SQL Server]The data types varchar(max) and text are incompatible in the equal to operator. (SQLExecute[402] at ext\pdo_odbc\odbc_stmt.c:254)

I have tried this query in multiple different ways, e.g.

Unnamed:

$query = $DBH->prepare("SELECT TOP 1 * FROM bksb_Resources WHERE ResourceType = ?");
$query->execute( array('assessment') );

Unnamed and using bindValue setting it to string

$query = $DBH->prepare("SELECT TOP 1 * FROM bksb_Resources WHERE ResourceType = ?");
$query->bindValue(1, 'assessment', PDO::PARAM_STR);
$query->execute(  );

Named and using bindParam to set it to string:

$val = 'assessment';
$query = $DBH->prepare("SELECT TOP 1 * FROM bksb_Resources WHERE ResourceType = :myp");
$query->bindParam(':myp', $val, PDO::PARAM_STR);
$query->execute(  );

But no matter how I do it, I always get this error message.

The column in question is of type: varchar(max), so presumably it is assuming the parameter being sent is of type 'text' even when I specify it to be a string value (char, varchar)

I can't change the database as it's for another piece of software.

What is the way around this? I really do not want to have to CAST every single time I do a where clause like this (or is that what everyone does?).

Thank you.

Answer 1

We had an issue similar to this, and in our case we had the following in our /etc/odbc.ini file:

[ServerAlias]
Driver = FreeTDS
Server = 1.2.3.4
PORT = 1433
TDS_Version = 8.0

We discovered that if you change the TDS_Version to 7.2 all of our bind queries started working correctly.

[ServerAlias]
Driver = FreeTDS
Server = 1.2.3.4
PORT = 1433
TDS_Version = 7.2

I'm not sure on the reason for this, but I suspect it is probably documented somewhere.