[rt-users] Migration from MySQL to PostgreSQL - Pg datatypes for blobs?

[Václav Ovsík]

On Mon, May 17, 2010 at 02:10:43PM +0200, Emmanuel Lacour wrote:
> On Mon, May 17, 2010 at 01:53:43PM +0200, Václav Ovsík wrote:
> > 
> > Oh yes, I was blind that RT uses DBIx::SearchBuilder. There is
> > a subroutine BinarySafeBLOBs defined in
> > DBIx::SearchBuilder::Handle::Pg.pm and returns undef. That is - Pg is
> > considered not capable handling binary in safe manner.
> > 
> > Every value must be converted using base64 :(.
> > 
> 
> yes :(
> 
> Maybe there is a better way as of recent postgres?

PostgreSQL can handle blob, but special handling is needed during
bind_param. Alternatively the quoting of value may be used.
This is the output from attached test script:


zito at bobek:~/pokusy/devel/perl/dbi$ ./pg-blob 

synthetized binary value:0504030201000102030405

>>> WITHOUT bind_param type spec...
data from database:0504030201

>>> WITH bind_param type spec...
data from database:0504030201000102030405

>>> WITH quoted value...
quoted value: E'\\005\\004\\003\\002\\001\\000\\001\\002\\003\\004\\005'
data from database:0504030201000102030405


> AW, I did a migration from mysql to postgres 8.3 with base64 conversion
> and everything is ok.

Fine. I'm now testing added base64 encoding.

Regards
-- 
Zito
-------------- next part --------------
#!/usr/bin/env perl
use strict;
use DBI;
use DBD::Pg qw(PG_BYTEA);

my $dbh = DBI->connect('dbi:Pg:dbname=zito', undef, undef,
                    {   
                        'RaiseError' => 1,
                        'AutoCommit' => 1,
                    }
                );
$dbh->do('CREATE TEMP TABLE b ( b bytea )');
my $v = pack('H*', '0504030201000102030405');

print "\nsynthetized binary value:", unpack('H*', $v), "\n\n";

print ">>> WITHOUT bind_param type spec...\n";
$dbh->do('INSERT INTO b VALUES (?)', undef, $v);
my ($a) = $dbh->selectrow_array('SELECT * FROM b');
print "data from database:", unpack('H*', $a), "\n";
print "\n";

$dbh->do('DELETE FROM b');

print ">>> WITH bind_param type spec...\n";
my $sth = $dbh->prepare('INSERT INTO b VALUES (?)');
$sth->bind_param(1, $v, { pg_type => PG_BYTEA });
$sth->execute();
my ($a) = $dbh->selectrow_array('SELECT * FROM b');
print "data from database:", unpack('H*', $a), "\n";
print "\n";

$dbh->do('DELETE FROM b');

print ">>> WITH quoted value...\n";
my $qv = $dbh->quote($v, { pg_type => PG_BYTEA });
print "quoted value: $qv\n";
$dbh->do("INSERT INTO b VALUES ($qv)");
my ($a) = $dbh->selectrow_array('SELECT * FROM b');
print "data from database:", unpack('H*', $a), "\n";
print "\n\n";

$dbh->disconnect();