Re: Picture with Postgres and Delphi

Thanks that is extremely helpfull.

Guy

Jonathan Bartlett wrote:

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