Picture with Postgres and Delphi

Hi,

Edwin Quijada wrote:

Hi !! Everybody
I am developing app using Delphi and I have a question:
I have to save pictures into my database. Each picture has 20 o 30k aprox.
What is the way more optimus?
That 's table will have 500000 records around. Somebody said the best way to
do that was encoder the picture to field bytea but I dont know about this.
Another way is save the path to the picture file but I dont like so much
because I need to write to disk by OS and have permission to write a
directory to OS.
What do u think??

Yes, generally you have two choices, but you can as well store data in
files and get it by stored procedures... Any way I did it with FTP (Indy
component). It allowed me to cache files locally.

--
Regards,
Michaďż˝ Zaborowski (TeXXaS)

Hi Edwin,

it depends on the components u use in Delphi. Normally Pictures and
ohter binary data is stored in the FieldType oid. I noticed that bytea
isn't mapped as TBlobField in Delphi in most components. I also don't
know if you are able to use the PG-Functions lo_import(), lo_export()
and so on with bytea. I use oid with Delphi and that works fine for me.
In Delphi you can use Procedures like TBlobField(MyField).LoadFromFile.
Post a Picture to the Server will look like this :

DataSet.Insert;
DataSet.BlobField.LoadFromFile('MyPictureFile');
DataSet.Post;

Now your pitcure is on the Server. You are also able to use standard
DBPicture-components...

Daniel

Am Di, 2003-09-09 um 18.54 schrieb Edwin Quijada:

----- Original Message -----
From: "Edwin Quijada" <listas_quijada@hotmail.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, September 09, 2003 6:54 PM
Subject: [GENERAL] Picture with Postgres and Delphi

Hi !! Everybody
I am developing app using Delphi and I have a question:
I have to save pictures into my database. Each picture has 20 o 30k aprox.
What is the way more optimus?
That 's table will have 500000 records around. Somebody said the best way

to

do that was encoder the picture to field bytea but I dont know about this.
Another way is save the path to the picture file but I dont like so much
because I need to write to disk by OS and have permission to write a
directory to OS.
What do u think??

You may choose between Bytea or large objects.
I had some problems with bytea on earlier version of pg (7.2 I think), but
last time I checked (7.3), all worked fine.
However, I use large objects.
The only disadvantage of large objects I see is that You can't dump them
into textual dump. You must use binary dumps.

Regards !

----- Original Message -----
From: "listy.mailowe" <listy.mailowe@wp.pl>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, September 09, 2003 8:10 PM
Subject: Re: [GENERAL] Picture with Postgres and Delphi

Hi,

Edwin Quijada wrote:

Hi !! Everybody
I am developing app using Delphi and I have a question:
I have to save pictures into my database. Each picture has 20 o 30k

aprox.

What is the way more optimus?
That 's table will have 500000 records around. Somebody said the best

way to

do that was encoder the picture to field bytea but I dont know about

this.

Another way is save the path to the picture file but I dont like so much
because I need to write to disk by OS and have permission to write a
directory to OS.
What do u think??

Yes, generally you have two choices, but you can as well store data in
files and get it by stored procedures... Any way I did it with FTP (Indy
component). It allowed me to cache files locally.

There is no need for manually storing files on filesystem, because large
objects are doing that for You. I am storing whole binary files in
blobs(synonym for large objects from some other platforms), and I do not
remember that I had a single problem with that. Do not forget that libpq has
great support for large objects, and you can store large object without
actually storing them on server filesystem, so You do not need any file
permissions on "upload directory" or something like that.
If You are using Delphi, there is great project called "Zeos objects", and
if I remember correctly it has support for large objects.

Regards !

On Tue, 9 Sep 2003, Darko Prenosil wrote:

----- Original Message -----
From: "Edwin Quijada" <listas_quijada@hotmail.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, September 09, 2003 6:54 PM
Subject: [GENERAL] Picture with Postgres and Delphi

Hi !! Everybody
I am developing app using Delphi and I have a question:
I have to save pictures into my database. Each picture has 20 o 30k aprox.
What is the way more optimus?
That 's table will have 500000 records around. Somebody said the best way

to

do that was encoder the picture to field bytea but I dont know about this.
Another way is save the path to the picture file but I dont like so much
because I need to write to disk by OS and have permission to write a
directory to OS.
What do u think??

You may choose between Bytea or large objects.
I had some problems with bytea on earlier version of pg (7.2 I think), but
last time I checked (7.3), all worked fine.
However, I use large objects.
The only disadvantage of large objects I see is that You can't dump them
into textual dump. You must use binary dumps.

for portability, I've always base64 encoded and stored them as straight
text. That works well too.

I'm a big fan of bytea. In every case where I've done the filesystem
method I wished I hadn't.

Jon

On Tue, 9 Sep 2003, Edwin Quijada wrote:

--- Jonathan Bartlett <johnnyb@eskimo.com> wrote:

I'm a big fan of bytea. In every case where I've
done the filesystem
method I wished I hadn't.

For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?

Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon

What is the size limit of bytea, I thought it was 8K?

How do you dump your database when you have bytea, do you need to do a
binary dump?

What are you using to insert the binary data?

Thank you in advance.

Guy

Jonathan Bartlett wrote:

For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?

Compared to the filesystem, bytea provides data integrity.

Bytea gives you remote access, which you can cache if needed.

Bytea gives you the same permissions as anything else in Postgres, so you
don't have to worry about that separately.

Compared to BLOBs, bytea's are just simpler. You can select them with a
single statement, you don't have to worry about leaving unreferenced
BLOBs, and, after 4 billion inserts, byteas are still meaningful while
BLOBs might not be. (due to OID problems).

Jon

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

I'd actually like to get some comments on this too because for compatibility and
throughput issues, I would think that storing the file path in the database
instead of the actually file would be "better". I've done one application like
this in the past that very worked well. I'm getting ready to do the final
e-commerce integration on a new site and if there is an advantage to storing the
files in the database (in this case about 300 jpeg images for a t-shirt site)
I'll try that out. I'll have to research that base64 encoding part because I'll
only every do text dumps.

Keith-

Quoting Jeff Eckermann <jeff_eckermann@yahoo.com>:

--- Jonathan Bartlett <johnnyb@eskimo.com> wrote:

I'm a big fan of bytea. In every case where I've
done the filesystem
method I wished I hadn't.

For the education of me and maybe others too, why was
that? i.e. what problems did you run into, that bytea avoids?

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

Is the size limit 8K for 'text' field types as well?

----- Original Message -----
From: "Guy Fraser" <guy@incentre.net>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, September 10, 2003 9:40 AM
Subject: Re: [GENERAL] Picture with Postgres and Delphi

Network Administrator wrote:

I'll have to research that base64 encoding part because I'll
only every do text dumps.

Then the base64 storage in the database is perfect.

I'll try that out. I'll have to research that base64 encoding part because I'll
only every do text dumps.

Oh yea, I forgot, bytea columns come out just fine in text dumps (BLOBs
don't, however).

Jon

What is the size limit of bytea, I thought it was 8K?

No limit that I've found. Some are several meg.

How do you dump your database when you have bytea, do you need to do a
binary dump?

Nope. pg_dump automagically escapes everything.

What are you using to insert the binary data?

Perl example:

my $COMPLETED_TEMPLATE_VARS_INSERT = <<EOF; insert into
completed_template_vars (completed_template, name, value, binvalue) VALUES
(?, ?, ?, ?)
EOF

$sth = $dbh->prepare($COMPLETED_TEMPLATE_VARS_INSERT);
$value = undef;
$binvalue = $field->{BINANS};
$value = $field->{ANS} unless $binvalue;
$sth->bind_param(1, $self->getOID);
$sth->bind_param(2, $name);
$sth->bind_param(3, $value);
$sth->bind_param(4, $binvalue, DBI::SQL_BINARY);
$sth->execute || die("DBERROR:${DBI::errstr}:");

Note that I explicityl set DBI::SQL_BINARY.

Now, for php, you do the following:

$logodata = pg_escape_bytea($tmpdata);
$tmpsql = "update advertisements set $column_name = '$logodata'::bytea where object_id = $advertisement_oid";
$tmp = $db->query($tmpsql);

I never got it to work with parameterized queries, but this works fine for
me. To select it back out, you need to do:

$q = $db->query("select teaser_logo_gif_image from advertisements where
object_id = ?::int8", array($_GET['advertisement']));
$row = $q->fetchrow();
$data = pg_unescape_bytea($row[0]);

NOTE that many versions of PHP include pg_escape_bytea but NOT
pg_unescape_bytea. Look in the docs to see which function appeared in
which version.

Jon

Well, assuming you can store a reasonable large text file in any database,
you can also look at uuencoding / base-64 encoding as a way of storing
things in the database.

It's more easily ported than either bytea or large objects.

On Wed, 10 Sep 2003, Network Administrator wrote:

text types are limited to ~1 Gig depending on things like encoding and
what not, you might get as much as 2 gig per record.

On Wed, 10 Sep 2003, Derrick Betts wrote:

Network Administrator wrote:

I'd actually like to get some comments on this too because for compatibility and
throughput issues, I would think that storing the file path in the database
instead of the actually file would be "better". I've done one application like
this in the past that very worked well. I'm getting ready to do the final
e-commerce integration on a new site and if there is an advantage to storing the
files in the database (in this case about 300 jpeg images for a t-shirt site)
I'll try that out. I'll have to research that base64 encoding part because I'll
only every do text dumps.

Storing the file path on the database can work well, but with modern
databases, there shouldn't be any issue with performance with storing
BLOB data. If you find there is an issue, then you should just set up a
table holding this information along with a key field. This key would be
a foreign key on your table holding the other information (in the case
of the t-shirt site, perhaps the other table holds information relating
to the style, size, etc).

In effect, what you are doing by storing the path is the path is your
"foreign key", to the filesystem "database". That is, you are using the
filesystem as a database.

One of the issues with storing the path on the database, instead of
storing the BLOB, is that the data on the filesystem can change or be
moved, without the database knowing about it. You then have "broken
keys", keys that refer to data that is no longer the same as it was when
the data was inserted into the database (the data is different, or it
doesn't exist, or it is corrupted in some manner). There is also the
issue of backups and restoring the state of the database. If you take a
snapshot of the database at any point in time for a backup, you need to
do the same with the pictures as well, and when you restore, remember to
restore both. If you kept the data in the database, you just need to
restore it alone.

I know there are other reasons as well - hopefully others on here will
point them out, as my memory is a bit fuzzy right now...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.

"Derrick Betts" <Derrick@grifflink.com> writes:

Is the size limit 8K for 'text' field types as well?

There is no size limit (OK, a very very large one) on either "text" or
"bytea" in modern versions of Postgres.

-Doug

How do you deal with backing up the images? Right now i can remote
backup my filesystem using rsync to an offsite location many times a
day, only taking a very small amount of I/O, bandwidth and time.
Dealing with the backup scared me away from using postgres in the first
place. The idea of doing a 200gb dump multiple times a day on an
image database scares me. So does doing a vacuum on it. The I/O,
time and bandwidth required to do this is daunting.

Are there any suggestions on how to do incremental backups of the
images and any other suggestions on performance? In the future I'd
like to move some filesystem images to postgres to have a centralized
storage. It would make some things easier, but i'm not sure it's worth
the additional problems. Hopefully i'm imagining the problems.

--brian

On Tuesday, September 9, 2003, at 08:56 PM, Jonathan Bartlett wrote: