Re: Best way to store and retrieve photo from PostGreSQL

This is what i did (to stored pictures in DB)...

but i use the following process :

1.store picture on my localhost db
2. export as SQL statement all pictures from my table :-( ===> it was 7.4Mb
3. import to the remote db hosted by a company.

is there an easy way to store image into a hosted DB ? because i can not use
any tool to directly insert into DB the pictures :-(
they only accept pgadmin interface...

thanks a lot,

Al.

On 2/25/07, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>
>
> > Store the pictures in the filesystem and only the path, description and
> > other metadata in the database. My suggestion ;-)
> >
> >
> > Andreas
> >
> Don't do that - the filesystems are not transactional (at least not the
> usual ones), so you'll lose the ability to use transactions. Imagine
> what happens when you do an unlink() and then the transaction fails for
> some reason - there's no way to 'rollback' the filesystem operation.
> I've seen this solution (storing images in filesystem) mostly in MySQL
> applications, but that's because of (a) lack of transactions in MySQL
> and (b) somehow sub-optimal handling of binary data as MySQL loads all
> the data even if it's not needed (this was true for MySQL 3.23 - I'm not
> sure about the current releases).
>
> Anyway, I do recommend storing images in the database, using a 'bytea'
> column for the binary data (and load them only if reallly needed, using
> proper projection). You can do some benchmarks, but I've never head
> performance problems with it on PostgreSQL and the ability to use
> transactions was invaluable (it saved us hundreds of hours when the
> machine went down for some reason).
>
> Tomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5