binding parameter for bytea type?

Václav Ovsík <[email protected]> Tue, 18 May 2010 13:17:14 +0200

Newsgroups	gmane.comp.db.postgresql.dbdpg
Message-ID	<[email protected]>

Hi everybody,
excuse my naive question please...

Is it possible that binding of binary data-type (bytea) without
data-type specification would behave like in MySQL in some future
versions of DBD::Pg? Binding binary values works correctly without type
specification in DBD::mysql.


zito@bobek:~/pokusy/devel/perl/dbi$ ./mysql-blob 

synthetized binary value: 0x0201000102

data from database: 0x0201000102


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

synthetized binary value: 0x0201000102

>>> WITHOUT bind_param type spec...
data from database: 0x0201

>>> WITH bind_param type spec...
data from database: 0x0201000102

>>> WITH quoted value...
quoted value: E'\\002\\001\\000\\001\\002'
data from database: 0x0201000102


This need of special care for columns of the type bytea is probably the
reason a module DBIx::SearchBuilder method BinarySafeBLOBs definition.

The BinarySafeBLOBs method is defined returning true for
    Informix, ODBC, mysql
and returning false for
    Oracle, Pg, SQLite, Sybase.

The result is a need for base64 or quoted-printable encoding of non-UTF8
data of column type text (Attachments.Content) of Request Tracker
application..

Best Regards
-- 
Zito

mysql-blob (text/plain, 627 B)

#!/usr/bin/env perl
use strict;
use DBI;

my $dbh = DBI->connect('dbi:mysql:zito', undef, undef,
                    {   
                        'RaiseError' => 1,
                        'AutoCommit' => 1,
                    }
                );
$dbh->do('CREATE TEMPORARY TABLE b ( b longblob )');
my $v = pack('H*', '0201000102');

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

#DBI->trace(5);
$dbh->do('INSERT INTO b VALUES (?)', undef, $v);
#DBI->trace(0);
my ($a) = $dbh->selectrow_array('SELECT * FROM b');
print "data from database: 0x", unpack('H*', $a), "\n";
print "\n";

$dbh->disconnect();

pg-blob (text/plain, 1.3 KB)

#!/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*', '0201000102');

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

print ">>> WITHOUT bind_param type spec...\n";
#DBI->trace(100);
$dbh->do('INSERT INTO b VALUES (?)', undef, $v);
#DBI->trace(0);
my ($a) = $dbh->selectrow_array('SELECT * FROM b');
print "data from database: 0x", 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: 0x", 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: 0x", unpack('H*', $a), "\n";
print "\n\n";

$dbh->disconnect();