| Subject: | Placeholders bug with bytea data type |
Hi,
the bug I'm going to describe does not appear in 1.41, but in all
1.45, 1.46 and 1.47. Don't know about other versions.
When I execute the attached test file, I get the following:
#### cut here #####
data length is 256
query: INSERT INTO test (data) VALUES (?)
data re-read from db has length 256 and is equal to the original
query: INSERT INTO test (id, data) VALUES (?, ?)
data re-read from db has length 256 and is NOT equal to the original
query: INSERT INTO test (data, id) VALUES (?, ?)
data re-read from db has length 256 and is NOT equal to the original
query: INSERT INTO test (data) VALUES (?)
data re-read from db has length 256 and is equal to the original
##### cut here #####
Basically, I'm inserting a record with binary data and getting it back.
It all works when the bytea field is alone (case 1 and 4), it breaks
when there is another non-bytea field (whatever order they are specified
in the query).
For your info, when the error shows up all bytes that are greater than
0x7F are converted into 0xFF. You can easily add a dump to see this.
Here you can find the results of the compilation and testing of 1.47 on
my machine:
####### cut here #######
t/00-signature......ok
t/00basic...........ok
t/01connect.........ok 1/8#
# Program Version
# Perl 5.8.8 (linux)
# DBD::Pg 1.47
# PostgreSQL (compiled) 70407
# PostgreSQL (target) 70407
# PostgreSQL (reported) PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled
by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
# Default port 5432
# DBI 1.50
# DBI_DSN dbi:Pg:dbname=test
t/01connect.........ok
t/01constants.......ok
t/01setup...........ok
t/02attribs.........ok
t/03dbmethod........ok
t/03smethod.........ok
2/55 skipped: Not testing pg_server_prepare on 7.4-compiled servers
t/04misc............ok
t/05arrays..........ok
15/18 skipped: Array support not implemented
t/06bytea...........ok
t/07copy............ok
t/12placeholders....ok
t/20savepoints......ok
2/4 skipped: Cannot test savepoints on pre-8.0 servers
t/99_pod............ok
t/99cleanup.........ok
All tests successful, 19 subtests skipped.
####### cut here ########
Hope this helps, but please contact me for further info.
Best regards,
Flavio.
| Subject: | prova.pl |
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::Pg;
my $data = join '', map { chr($_) } 0 .. 255;
print {*STDOUT} 'data length is ', length $data, "\n\n";
my $dbh = DBI->connect('dbi:Pg:dbname=test', '', '', {RaiseError => 1});
eval { $dbh->do('DROP TABLE test'); };
$dbh->do('CREATE TABLE test ( id integer, data bytea )');
do_insert([qw( data )], {data => $data}, 1);
do_insert([qw( id data )], {id => 14, data => $data}, 2);
do_insert([qw( data id )], {id => 35, data => $data}, 1);
do_insert([qw( data )], {data => $data}, 1);
$dbh->disconnect();
sub do_insert {
my ($fields_aref, $value_href, $binary_placeholder_id) = @_;
my @fields = @$fields_aref;
my @values = @{$value_href}{@fields};
my @pholders = ('?') x scalar @fields;
$dbh->do('TRUNCATE test');
local $" = ', ';
my $query = "INSERT INTO test (@fields) VALUES (@pholders)";
print {*STDOUT} "query: $query\n";
my $sth = $dbh->prepare($query);
$sth->bind_param($binary_placeholder_id, undef,
{pg_type => DBD::Pg::PG_BYTEA});
$sth->execute(@values);
my ($data) = $dbh->selectrow_array('SELECT data FROM test LIMIT 1');
print {*STDOUT} 'data re-read from db has length ', length $data;
print {*STDOUT} ' and is ', ($data eq $value_href->{data} ? '' : 'NOT '),
"equal to the original\n";
print {*STDOUT} "\n";
} ## end sub do_insert