Re: Picture with Postgres and Delphi - Mailing list pgsql-general

> > 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

> >
> > 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.
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>