CODEWITHSUNDEEP
phpsqlpdoodbc
CMR
CMR

Reputation: 317

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

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.

Upvotes: 5

Views: 1687

Answers (2)

Dezza
Dezza

Reputation: 1094

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.

Upvotes: 5

OscarGarcia
OscarGarcia

Reputation: 2114

Have you tried to change field type to CHAR o VARCHAR? (and not VARCHAR(MAX) that have this type of incompatibility).

Or have you tried to convert data types?

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

I'm sorry but I don't have MS SQL Server to test the code, but I get that tip from this page: https://msdn.microsoft.com/en-us//library/ms176089.aspx

Note that warning:

Code page translations are supported for char and varchar data types, but not for text data type. As with earlier versions of SQL Server, data loss during code page translations is not reported.

Hope it helps!

Upvotes: 0

Related Questions