2

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.

Improve this question
4
  • Have you tried using chrome to view the source of the invalid image? There might be a PHP error getting dumped that is invalidating the image, or could explain why maybe no image data is even being dumped. I remember there used to be a way to do this in firefox as well, but the only browser it seems to be working in for me now is chrome. Commented Apr 13, 2010 at 15:21
  • I have tried using SQL Server Management Studio and saving the binary data directly to my file system and even that wouldn't display the image. When I view the table the data is stored in a format like "0xFFD8FFE0..." rather than a bunch of garbage characters. Commented Apr 13, 2010 at 15:25
  • I have access to two servers at work. One of them I have very low permission settings on that disallow me from using bulk inserts, and it makes it very difficult to test out the image problem I'm having. The other one I was given recently specifically to try and figure a way to display images, and I currently have SQL server installed, and Apache with PHP working. I haven't been able to configure my php.ini file to work with SQL Server yet on this testing server I was given. As soon as I get SQL server working with PHP I can do a lot more testing. Commented Apr 13, 2010 at 15:28
  • could you paste the PHP code that you've used? I have many suspicions, don't know which one to comment, might be none of them. Commented Apr 13, 2010 at 15:33

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Oh, so when I pull in the data, find a PHP function to convert from hex to binary and then try displaying the image? I'm thinking maybe it's already in hex form and that could be the reason it's not working. I'll try something like that right after I finish getting SQL Server to work through my Apache PHP setup.
See my edits. If it's already in hex form, you just need to convert to binary using hex2bin().
0 
    $qry=" **Your Query statement**";
echo"<table cellspacing='10px' cellpadding='15px'>";
$i=1;
$res=mssql_query($qry)or die(mssql_get_last_message());
//echo $qry;
while($rw2=mssql_fetch_assoc($res))
    {
            $code_base64 =base64_encode($rw2['**Binary Table Field Name**']);
$code_json =json_encode($code_base64);
//Multiple Image Display in array format
<img src="" id="Grnimage<?php echo $i;?>" alt=" " name="grnimage[]" width="200" height="180" /></br></br>
        <script type="text/javascript">
        var id="Grnimage"+<?php echo $i;?>;
            var tyype="image/png";
        var jsonval=JSON.stringify(<?php echo  $code_json; ?>);
var byteData= JSON.parse(jsonval);
                var raw = window.atob(unescape(encodeURIComponent(byteData)));
    var rawLength = raw.length;
    var array = new Uint8Array(new ArrayBuffer(rawLength));
    for(i = 0; i < rawLength; i++) {
        array[i] = raw.charCodeAt(i);
    }
        var blob = new Blob([array], {type: tyype});
                blobURL = window.URL.createObjectURL(blob);
            document.getElementById(id).src = blobURL;

</script>
Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.