got some binary data that changes when i insert and retrieve it later
from bytea column:
http://nate.quandra.org/data.bin.0.702601051229191
running 8.3 on debian 5.0.
example:
root=# create database testdb;
CREATE DATABASE
root=# \c testdb
You are now connected to database "testdb".
testdb=# create table testtable (id serial, data bytea);
NOTICE: CREATE TABLE will create implicit sequence "testtable_id_seq"
for serial column "testtable.id"
CREATE TABLE
testdb=#
--
#!/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 $encodeddata = $data;
$encodeddata =~ s!(\\|[^ -~])!sprintf("\\%03o",ord($1))!ge; #prepare
data for bytea column storage
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->execute($encodeddata);
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};
$newdata =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : "\\"!ge; #decode
bytea column storage format
print md5_hex($data).' ';
print '!' if md5_hex($data) ne md5_hex($newdata);
print '= '.md5_hex($newdata);
print "\n";
__END__
hash of data changes ... if you uncomment the $data = '123abc' line
you can see that it works with those six bytes fine, and it also works
with most other binary data, just not this binary data. any insight
would be appreciated. thanks.
nathan