Help Reading Binary Image Data from SQL Server in PHP
I cannot figure out how to read binary data from SQL server in PHP. I am working on a project where I need to be able to store an image directly in a SQL table and not on the filesystem.
I am currently using a query like this:
INSERT INTO myTable (document) SELECT FROM OPENROWSET (BULK N'C: \ image.jpg ', SINGLE_BLOB) as BLAH
This works great to actually insert the image into the table, but I haven't figured out a way to get it and return my image yet.
I am doing this with PHP and you will eventually have to make a stored procedure out of it, but someone can educate me on a way to get this binary data ( varbinary(MAX)
) and generate an image on the fly.
I was expecting to just use a statement SELECT
and add a content type to the headers stating that it is an image, but it just doesn't work. Instead, the page will only show the filename that I have encountered in the past and understand it as an error with the image data.
EDIT: I think I figured it out. There was some issue where SQL Server was only sending 8000 bytes when reading from a stored procedure, so it caused corrupted images that I tested.
$ 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 hexagonal tip that let me figure out what is wrong.
a source to share
No experience with SQLServer, but I've worked with BLOBs in MySQL. You have two options:
-
Flush binary data to make it work in SQL query. You can do this using addlashes () before the data is inserted and stripslashes () when it comes back.
-
Using hexadecimal syntax for SQL query.
Not sure if this is standard SQL, but in MySQL, you can read BLOBs to 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.
a source to share