Re: bytea corruption? - Mailing list pgsql-general

wrong reply-address; please disregard the last message from me.


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

things work fine if i make the data "123abc":

# ./bytea.pl
Argument "DBD::Pg::PG_BYTEA" isn't numeric in subroutine entry at
./bytea.pl line 18.
a906449d5769fa7361d7ecc6aa3f6d28 = a906449d5769fa7361d7ecc6aa3f6d28

below is my script as it stands now:

#!/usr/bin/perl -w

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);


my $fh;
open( $fh, '/tmp/data.bin.0.702601051229191' ) or die $!;
binmode $fh;
my $data = do { local( $/ ) ; <$fh> } ;
close($fh);

#$data = '123abc';

my $connection = DBI->connect_cached("dbi:Pg:dbname=testdb;port=5432",
"root", "", {RaiseError=>1});

my $insert_sth = $connection->prepare('insert into testtable (data)
values (?) returning id');
$insert_sth->bind_param(1, $data, { pg_type => DBD::Pg::PG_BYTEA });
$insert_sth->execute();
my $ref = $insert_sth->fetchrow_hashref;
my $id = $ref->{id};

my $getall_sth = $connection->prepare('select * from testtable where id=?');
$getall_sth->execute($id);
my $newref = $getall_sth->fetchrow_hashref;
my $newdata = $newref->{data};

print md5_hex($data).' ';
print '!' if md5_hex($data) ne md5_hex($newdata);
print '= '.md5_hex($newdata);

print "\n";

--


nathan


On Sat, Aug 22, 2009 at 9:17 AM, Daniel Verite<daniel@manitou-mail.org> wrote:
>        Nathan Jahnke wrote:
>
>> good catch - it's because i'm used to working in plperlu.
>> unfortunately commenting out those lines makes no difference for this
>> particular data (that i linked in my original email); it's still
>> corrupted:
>
> Don't remove both: remove only the custom decoding.
>
> It's different for the encoding step. It can also be removed, but in this
> case you need to tell DBD::Pg that your data is binary, like this:
>
> $insert_sth->bind_param(1, $data, { pg_type => DBD::Pg::PG_BYTEA });
> $insert_sth->execute();
>
> (and have $data be raw binary, no custom encoding).
>
> --
> Daniel
> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>