thank you very much, all. i was able to insert my data and get it back
out with a matching hash. my problems were caused by confusion going
between plperlu - which has the bytea storage explicit custom encoding
requirement - and regular perl using dbd::pg - which does not as long
as the data type is specified.
so, for reference:
plperlu:
explicitly encode bytea before insert via spi: yes
explicitly decode bytea after select via spi: yes
perl (dbd::pg):
explicitly encode bytea before insert via sth: no if using { pg_type
=> DBD::Pg::PG_BYTEA } as third arg in bind_param() (thanks daniel
verite)
explicitly decode bytea after select via sth: no
nathan
On Sun, Aug 23, 2009 at 9:20 AM, Colin
Streicher<colin@obviouslymalicious.com> wrote:
> I'm probably a little late to this discussion, but I have had issues before
> with BYTEA in postgres before as well, this is what I found worked.
>
> use Digest::MD5;
> use DBI qw(:sql_types);
> use DBD::Pg qw(:pg_types);
>
> ....
> ....
>
> sub InsertBin($$$)
> {
> my ( $dbh, $md5sum, $filename ) = @_;
> open BIN,"<$filename" || die "Unable to open";
> my $bin;
> while (<BIN>){
> $bin .= $_;
> }
> my $insertsql = qq( INSERT INTO enc_virus VALUES( ?,?,? )); # filename -
> md5sum - binary
> my $sth = $dbh->prepare($insertsql);
> $sth->bind_param(1,$filename);
> $sth->bind_param(2,$md5sum);
> $sth->bind_param(3,$bin, { pg_type => PG_BYTEA });
> $sth->execute();
> }
>
>
> I hope this helps if you haven't figured it out yet
>
>
> On Saturday 22 August 2009 03:48:25 pm Daniel Verite wrote:
>> Nathan Jahnke wrote:
>> > thanks for your help. unfortunately i'm still getting corruption on
>> > this particular data (available at
>> > http://nate.quandra.org/data.bin.0.702601051229191 ) even with these
>> > changes:
>> >
>> > # ./bytea.pl
>> > Argument "DBD::Pg::PG_BYTEA" isn't numeric in subroutine entry at
>> > ./bytea.pl line 18.
>> > 37652cf91fb8d5e41d3a90ea3a22ea61 != ce3fc63b88993af73fb360c70b7ec965
>>
>> Ah, you also need to add
>> use DBD::Pg;
>> at the beginning of the script for DBD::Pg::PG_BYTEA to be properly
>> evaluated.
>>
>> Best regards,
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>