Jump to content

store jpg in mssql


Go to solution Solved by kicken,

Recommended Posts

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)?

Link to comment
Share on other sites

  • Solution

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();

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.