title: Help Reading Binary Image Data from SQL Server into PHP
body: <p>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.</p>
  
  <p>Currently, I have been using a query like this one:</p>
  
  <blockquote>
    <p>INSERT INTO myTable(Document) 
    SELECT * FROM 
    OPENROWSET(BULK N'C:\image.jpg', SINGLE_BLOB) as BLAH</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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 (<code>varbinary(MAX)</code>) and generate an image on the fly.</p>
  
  <p>I expected it to be simple to use a <code>SELECT</code> 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.</p>
  
  <p>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.</p>
  
  <pre>
  $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&lt;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; 
  ?>
  </pre>
  
  <p>This is how I had to display the image. Thanks for mentioning the hex tip, that allowed me to figure out what was wrong.</p>