PHP ODBC: String Data, Right Truncation from LIKE clause in prepared statement

Question

Consider the following table:

CREATE TABLE foo (a VARCHAR(8), b VARCHAR(20))

Then consider the following prepared statement:

SELECT count(*) FROM foo WHERE a LIKE ?

Finally, consider the following PHP lines:

$stmt = odbc_prepare($dsn, $query); // Where $query is the above query
$filter = "%12345678%"; // Note: two wildcards and 8 real characters
odbc_execute($stmt, array($filter));

With PHP 5.3.13 on Windows x86 (as part of the WAMP package), and both the Microsoft SQL Server ODBC driver version 06.01.7601 and Microsoft SQL Server Native Client version 10.50.4000, the odbc_execute fails, generating a "String data, right truncation" error.

If, instead, I substitute the filter string directly into the query, like so:

SELECT count(*) FROM foo WHERE a LIKE '%12345678%'

of course it works just fine, since LIKE isn't ever supposed to lead to that error.

Is this something I'm getting wrong about prepared statements, or a bug in either PHP or the SQL Server driver?

I think you'll need single quotes around the %123456..% so make the filter like $filter = "'%12345678%'"; — Professor Abronsius
Seems reasonable but doesn't work. odbc_execute still returns false, but now both odbc_error (sqlstate) and odbc_errormsg are both empty. :-( — Brian A. Henning

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-10-10T15:12:52

The double quotes may be causing the issue. Try this code:

$stmt = odbc_prepare($dsn, $query);
$filter = array();
$filter[] = "'%12345678%'";          // single quotes around the entire LIKE 
odbc_execute($stmt, $filter);

PHP ODBC: String Data, Right Truncation from LIKE clause in prepared statement

1 Answers