PDO corrupting MSSQL Text field data used to store file

Question

I use PDO MSSQL drivers (with great difficulty) to connect to a MSSQL DB. I have just moved from mssql_ . I have a field in a table which is a text file which I use to store a file. When needed the field is retrieved and the data written to disk.

It works fine using mssql_ drivers however retrieving the data with PDO (plain connection no additional parameters) somehow corrupts the files. I know the data is ok as I have other ways of outputing and checking, but I need a work around for this PDO issue.

I have tried binding the column as a LOB to no avail

$stmt->bindColumn(1, $lob, PDO::PARAM_LOB);

Any suggestions to fix this issue?

Answer 1

I found the solution. You need to bind the column and specify that it is binary PDO::SQLSRV_ENCODING_BINARY

$stmt = $PDORecipeCONN->prepare("Select FileDataField from Documents where VersionID= :Ver");
$stmt->execute(array(':Ver' => $fileid));
$stmt->bindColumn(1, $Data , PDO::PARAM_LOB,0,PDO::SQLSRV_ENCODING_BINARY);
$stmt->fetch(PDO::FETCH_BOUND);
$fp = fopen($fileid . '_file.doc', 'w');
fwrite($fp, $Data);

fclose($fp);