NOTE: I have narrowed this problem down to specifically PDO because I am able to successfully prepare and execute statements using the odbc_* functions.
Why can't I bind this parameter to the PDO prepared statement?
This works:
$mssqldriver = 'ODBC Driver 13 for SQL Server';
$pdoDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);
$pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql = "SELECT 'value' AS col where 'this' = 'this'";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);
print_r($stmt->fetch());
Array ( [col] => value [0] => value )
Does not work:
$sql = "SELECT 'value' AS col where 'this' = ?";
$stmt = $pdoDB->prepare($sql);
$params = ['this'];
$stmt->execute($params);
print_r($stmt->fetch());
Web Server is running PHP 5.5.9 on Linux Ubuntu 14.04 with ODBC Driver 13 for SQL Server and connecting to Microsoft SQL Server 2012 on Windows Server 2012
Here's the full error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0 [Microsoft][ODBC Driver 13 for SQL Server] String data, right truncation (SQLExecute[0] at /build/buildd/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' in /var/www/scratch.php:46 Stack trace: #0 /var/www/scratch.php(46): PDOStatement->execute(Array) #1 {main} thrown in /var/www/scratch.php on line 46
I have also tried setting:
$pdoDB->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
And using named parameters:
$sql = "SELECT 'value' AS col where 'this' = :myVal";
$stmt = $pdoDB->prepare($sql);
$params = ['myVal' => 'this'];
$stmt->execute($params);
print_r($stmt->fetch());
Even with an explicit colon:
$params = [':myVal' => 'this'];
I also tried just using bindParam as demonstrated in this answer:
$sql = "SELECT 'value' AS col where 'this' = ?";
$stmt = $pdoDB->prepare($sql);
$param = 'this';
$stmt->bindParam(1, $param);
$stmt->execute();
print_r($stmt->fetch());
As well as with named parameters:
$sql = "SELECT 'value' AS col where 'this' = :myVal";
$stmt = $pdoDB->prepare($sql);
$param = 'this';
$stmt->bindParam(':myVal', $param, PDO::PARAM_STR);
$stmt->execute();
print_r($stmt->fetch());
If I try to explicitly set the length:
$stmt->bindParam(':myVal', $param, PDO::PARAM_STR, 4);
I get a bonus error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Incorrect syntax near 'OUTPUT'.
And yes, all this is a trivialized example without tables so that you can easily reproduce it, but just to be sure, I have actually tried this with a real table.
CREATE TABLE myTable (
id INT IDENTITY PRIMARY KEY,
val NVARCHAR(255)
);
INSERT INTO myTable (val) VALUES ('hello world');
Works:
$sql = "SELECT * FROM myTable WHERE val = 'hello world'";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);
print_r($stmt->fetch());
Array ( [id] => 1 [0] => 1 [val] => hello world [1] => hello world )
Does not work:
$sql = "SELECT * FROM myTable WHERE val = ?";
$stmt = $pdoDB->prepare($sql);
$params = ['hello world'];
$stmt->execute($params);
print_r($stmt->fetch());
All paths lead to the same error:
String data, right truncated
