Storing Images w/PostgreSQL

[Anon]

Has anyone done this? I have been able to insert the blob using lo_import, and I can retrieve the data using the oid, but I can't figure out how to display the image, just the binary data shows up. Any suggestions would be appreciated.

[amcgrath]

Have you tried adding Header("Content-type: image/gif") before the display line? I have not yet done what you are doing, but it would seem that this "might" work.

[Anon]

Yes, I tried that, which should work as it does when I select image files from mySQL, but so far no dice.

[Anon]

Here is what I have been using:

$image_oid = 81377;

@pg_exec("BEGIN");

$fd = @pg_loopen($image_oid,"r");

$img = @pg_loreadall($fd);

@pg_loclose($fd);

@pg_exec("COMMIT");

print "<IMG SRC='viewblob1.php?image=$img&imageType=image/gif'>";

and the document viewblob1.php consists of this:

Header( "Content-type: $imageType");
echo $image;

[[deleted]]

More to the point: why would you want to store binary data in the database in the first place? It servers absolutely no purpose at all, it just slows you down.

[amcgrath]

Well, storing on disk and putting a pointer in the db is ok too, ASSUMING the database never moves or the directory locations never change. Keeping them in the db seems, to me, to make the application a more portable. JMO though.

[[deleted]]

The locations are only relative locations from the documentroot, you can move the database to africa and it will still work.

You assume the locations stay the same, just like you assume they stay the same for the images you use in your normal HTML layout, or the links you use to other internet pages.

Storing binary data in a database means you're just using the database as a complex filesystem. It is definately not more portable because every database deals with binaries in it's own way, and you are going to have quite a hard time moving the data from MySQL to Oracle for example. If you use locations, it's a minimal effort.

[amcgrath]

Vincent,

Ya, I agree, but I can tell you this: I worked for a company (www.carscene.com, now defunct, alas). We had more than 35,000 images relating to the car ads in the database, all using pointers. When the company was purchased and the site moved to its new location, the havoc caused by those images gave me an ulcer. In the end, I guess it's what you refer.

[[deleted]]

I can't really imagine a setup where moving a directory would cause problems if you are just storing a textual represenation of it's location...

But that's getting a little off topic I think.

[[deleted]]

btw: that site uses scaled images, I am not suprised you got problems with their database, not at all 🙂

[Anon]

Well, I guess the file system is way more suited to storing binary data than the database is, but it is very convenient to be able to store the image with the assoc. data, it is one less step to take. It seems to work well in MySQL with less than 10,000 images. The project I am going to use pg for will have over 500,000 in a short time. I will probably store them on the disk using image_name to access them.

Thanks for the feedback on this subject, I have not been able to find much on the subject of storing files in dbases, I haven't been paying attention to this stuff for very long though...

[Anon]

Dear friends ,

I am stuck up because i have to move images from pgsql to our pgsql server . We have worked hard with company which has pgsql database. Now we have some images which also need to be transferred.

Thanks once again

Amol S Chawathe