store jpg in mssql

[mrjerrye]

Hello,

I am trying to store a jpg into a mssql table.  I cannot figure out how mssql wants the file encoded...  Im using PDO sqlsrv.

 

//  SQL file_data column is VARBINARY(MAX)
//  mssql doesnt like this
$dbStatement = $db->prepare('INSERT INTO uploads (file_application_id, file_type, file_name, file_data) VALUES (?, ?, ?, ?);');
$dbStatement->execute(array($appid['appid'],$attachment['type'],$fname,file_get_contents('c:\\uploads\\'.$attachment['name'])));

//  also doesnt like this (found from google searching)
$dbStatement = $db->prepare('INSERT INTO uploads (file_application_id, file_type, file_name, file_data) VALUES (?, ?, ?, ?);');
$data = unpack("H*hex",file_get_contents('c:\\uploads\\'.$attachment['name']));
$dbStatement->execute(array($appid['appid'],$attachment['type'],$fname, '0x'.$data['hex'] ));

does anyone know how i can get the file data encoded or formatted correctly for mssql insertion (and the retrieval later)?

[kicken]

You need to use bindParam().  Set the type as PDO::PARAM_LOB so you can bind a file resource and stream the data (rather than as a big string), and use the $driverOptions parameter to set the data type to binary.

 

$dbStatement = $db->prepare('INSERT INTO uploads (file_application_id, file_type, file_name, file_data) VALUES (?, ?, ?, ?);');
$dbStatement->bindValue(1, $appid['appid']);
$dbStatement->bindValue(2, $attachment['type']);
$dbStatement->bindValue(3, $fname);
$fp = fopen('c:\\uploads\\'.$attachment['name'], 'r');
$dbStatement->bindParam(4, $fp, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
$dbStatement->execute();

 

[mrjerrye]

Fantastic, I will give this a shot.  Thanks!

[mrjerrye]

So I gave this a try...  and here is the error returned:

 $dbStatement->errorInfo():
    [0] => IMSSP
    [1] => -7
    [2] => An error occurred translating string for input param 4 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

I had seen this error before with another method of trying to insert the string... 

 

Any suggestions?

[kicken]

Did you add the PDO::SQLSRV_ENCODING_BINARY parameter?  It prevents that error.

[mrjerrye]

I did, i may have ran an version of the script... im investigating now.  sorry for the confusion.

[mrjerrye]

kicken, your code was exactly what i needed.  Thank you very much!