I am using PHP 5.3.3 on a CentOS 6.2 box, connecting to an instance of Microsoft SQL Server 2008R2. The connection works, and I am able to retrieve data, so long as my queries contain no parameters. When I add parameters, I get the error, "String data, right truncation".
Here's some example code:
<?php
$dbh = new PDO("odbc:myDSN", 'myUsername', 'myPassword');
$testCase = 1;
switch ($testCase) {
case 1:
// This case fails with this error:
// Error 22001: [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation (SQLExecute[0] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254)
$query = "select * from [myDatabase].[sys].[objects] WHERE (([name]=?))";
$stmt = $dbh->prepare($query);
$param1 = 'testtable1';
$stmt->bindParam(1, $param1, PDO::PARAM_STR); // Note: '1' is correct; it should not be '0'
break;
case 2:
// This case works properly
$query = "select * from [myDatabase].[sys].[objects] WHERE (([name]='testtable1'))";
$stmt = $dbh->prepare($query);
break;
}
$execResult = $stmt->execute();
if ($execResult) {
print "Success!\n";
} else {
$errorInfo = $stmt->errorInfo();
print "Error " . $stmt->errorCode() . ": " . $errorInfo[2] . "\n";
}
$rowCount = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Row " . $rowCount . ":\n";
foreach ($row as $key => $value) {
printf(" %-20s %s\n", $key, $value);
}
$rowCount++;
}
Note that both test cases in the code above should do the same thing. Test case 1 uses parameters (as all code should), and test case 2 explicitly puts the relevant value in the SQL query. Test case 2 works. Test case 1 does not. I have tried replacing 'bindParam()' with 'bindValue()', but this has no effect. I've also tried using named parameters (e.g., :name) instead of positional parameters, but this also has no effect. I've tried adding an explicit length argument to bindParam() (using strlen($param1) as a value), but that gives a really bizarre error message (Incorrect syntax near 'OUTPUT'), and I can only assume that I'm doing it wrong. Integer parameters work properly. Only string parameters fail.
Any ideas why this isn't working?
Of course it's possible that there's a bug in the ODBC driver, or that it's not compatible with my version of PHP, or any number of similar problems, but I hope that I'm simply using the API improperly.
Edit:
Per Anda Iancu's suggestion, I delved into SQL Server Profiler. When traced, case 1 gives two nearly-identical records, one of class SQL:BatchStarting, and one of class SQL:BatchCompleted, both containing the text:
set fmtonly on select [name] from [myDatabase].[sys].[objects] where 1=2 set fmtonly off
Case 2 gives two records, both of class "RPC:Completed". The first contains the text:
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'select * from [myDatabase].[sys].[objects] WHERE (([name]=''testtable1''))'
select @p1
and the second contains the text:
exec sp_unprepare 1
Update:
In a desperate move, hoping there might be some kind of problem with dropping a new version of unixODBC into an existing version of PHP, I recompiled PHP from source. This turns out to be harder than you might think, on CentOS. Unfortunately, this had no effect. Same errors all around.
