Can't display database image (PHP/SQL Server)

[jheitz]

Hi all,

I am trying to store an image on a SQL Server table and then retrieve it and display it on a web page. I have used in the past the URL approach (saving the image in a folder and linking to it) but now want to store these images in the database (as a means to expand my PHP knowledge really).

I have searched the web and the forum and I found explanations as to how to do this using MySQL. I adapted this thinking it would be straight forward but it doesn't seem to work (not sure why), hence this thread.

Here is what I do:

1) I have a table in my database with a column to store the Image. I have tried different data types in case this was the problem, but I haven't been able to make it work with either IMAGE, NTEXT or TEXT.

2) I use a form to upload the image file and store it in the database (I've basically adapted the code on this page: http://codewalkers.com/tutorials/35/3.html ), only thing to mention is that I needed to use strreplace when inserting the image to replace ' with '' (double single), as otherwise single quote characters confuse SQL Server.

3) To display the image I have a separate PHP file to which I pass the image ID as a GET argument like this:

echo "<IMG SRC='Render_DBImage.php?ImageID=". $ImageID ."'>";

4) This is the file Render_DBImage.php:

<?php
require($_SERVER['DOCUMENT_ROOT'].'/sserver/includes/database.inc');

$ImageID = $_GET["ImageID"];

$sql = "SELECT ImageType, [Image], ImageSize, ImageName FROM [image] WHERE ImageID=". $ImageID;
$process=odbc_exec($sqlconnect, $sql);
odbc_fetch_row($process);
$ImageType = odbc_result($process,"ImageType");
$Image = odbc_result($process,"Image");
Header ("Content-type: $ImageType");
print $Image;
odbc_close($sqlconnect);

?>

But the image is not displayed. In fact, if I try the following on my Firefox web browser:

[url]http://localhostRender_DBImage.php?ImageID=1[/url]

I can't see the image and choosing view page source shows a bunch of binary text (what is stored in the database).

Any ideas why the image is not being rendered? I just don't seem to be able to figure it out!. If you need anymore details, the PHP source code, etc... let me know please. Thanks,

Javier

[MarkR]

You are probably storing garbage in your database.

The correct column type to use is Image.

But don't expect to be able to insert it with a non-parameterised INSERT statement - in fact, forget it.

You need to be using a parameter placeholder containing the binary data, as ODBC is almost certainly not binary-safe in its SQL code.

I don't know about odbc, but I use PDO (which can be used with PDO_odbc). I bind either a named or positional parameter, with the type PDO:😛ARAM_LOB.

This works fine on Sqlite and MySQL - I haven't tried it on MSSQL, but it should.

If you genuinely have to use the "ODBC" module, try using its prepare/execute system.

Mark

[Houdini]

an image is a BLOB (Binary large object) that is the problem.

[jheitz]

Thanks for the suggestions Mark.

I tried using odbc_prepare and odbc_execute, as I didn't want to change the database connection (it will affect my whole project). The new way to insert the image is:

$stmt = odbc_prepare($sqlconnect, 'INSERT INTO image (ImageType, [Image], ImageSize, ImageName) VALUES (?,?,?,?)');

$res = odbc_execute($stmt, array($file_type, $userfile, $file_size, $file_name));		

I also ensured the data type for the column is IMAGE as suggested. I could save the image in the database with no problems and it seems to be saved in HEX (the data looks like this: "0xFFD8FFE05C30104A4649465").

However, when I try to display the image I still don't see anything. A call to my PHP page with the GET argument to display my image, displays a web page with the text "http://localhost/sserver/Render_DBImage.php?ImageID=1" as the only text. When I try to check the page's source code I get again all the gibberish (looks like this: "ÿØÿà\0JFIF\0\0H\0H\0\0ÿÛ\0C\0ÿÛ"). This gibberish looks in fact very similar to the JPEG file when I edit this with a text editor.

It might be something else that is not working as, even if the data entered in the field is somehow screwed up I would expect to receive a corrupted image instead of no image at all (am I wrong on this assumption?).

Thanks,

Javier

[jheitz]

Houdini, why is that a problem? What data type should be used instead then?

[MarkR]

You should try to eliminate the factors one at a time.

Make a PHP script which you can run from the command line, and reads a file in (i.e. file_get_contents()), puts it in the database, then takes it back out again and writes it into a second file.

Then do a binary comparison between the two files.

If it's different, you have a problem.

Mark