Help Reading Binary Image Data from SQL Server into PHP

Question

I cannot seem to figure out a way to read binary data from SQL server into PHP. I am working on a project where I need to be able to store the image directly in the SQL table, not on the file system.

Currently, I have been using a query like this one:

INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\image.jpg', SINGLE_BLOB) as BLAH

This works fine to actually insert the image into the table, but I haven't yet figured a way to retrieve it and get my image back.

I am doing this with PHP, and ultimately will have to make a stored procedure out of it, but can anyone enlighten me on a way to get that binary data (varbinary(MAX)) and generate an image on the fly.

I expected it to be simple to use a SELECT statement and add a content-type to the headers that indicated it was an image, but it's simply not working. Instead, the page will just display the name of the file, which I have encountered in the past and understand it to be an error with the image data.

EDIT: I think I figured this out. There was some problem where SQL Server was only sending a maximum of 8000 bytes when reading from the stored procedure so it caused the images I was testing out to break.

$q = "Get_Picture_Test_SP @pk_rms_id=1443546";
$res = mssql_query($q);

$row = mssql_fetch_assoc($res);

$image = $row['picture'];

function hex2bin($h)
  {
  if (!is_string($h)) return null;
  $r='';
  for ($a=0; $a<strlen($h); $a+=2) { $r.=chr(hexdec($h{$a}.$h{($a+1)})); }
  return $r;
  }

$image = hex2bin($image);

header("Content-type: image/gif");

print $image;

exit; 
?>

This is how I had to display the image. Thanks for mentioning the hex tip, that allowed me to figure out what was wrong.

Answer 1

No experience with SQLServer but I did work with BLOBs in MySQL. You have two options,

1. Escape binary data so it works in SQL query. You can do this by using addslashes() before data is inserted and stripslashes() when it comes back.

2. Using hex syntax of the SQL query.

Not sure if it's standard SQL but in MySQL, you can read BLOB into hex like this,

 select hex(image) from table;

You can write binary data as hex in SQL like X'1234ABCD'.

PHP provides hex2bin/bin2hex so you can convert easily.