| Subject: | Tie::DBI strips trailing spaces from stored values |
A string with trailing spaces stored in a hash tied via Tie::DBI to an
SQL database will, when read back, have the spaces removed.
Tested on: Perl 5.6.1, Tie::DBI 1.02, DBI 1.21, MySQL 3.23.49,
DBD::mysql 1.2216. All packages are the standard ones from the Debian
"woody" distribution, except Tie::DBI, which was installed from CPAN.
(It's not included in woody.)
This is a result of the DBI ChopBlanks attribute, so can be worked
around by extracting the database handle for the tie and performing
$dbh->{'ChopBlanks'} = 0; however, the documentation for my version of
DBI says that this value defaults to false, so either the documentation
is wrong or Tie::DBI is explicitly setting it to true.
If the latter is true, it's very unintuitive, and also violates the DBI
recommendations, as it breaks the emulation of standard untied Perl
hashes. Even if it turns out to be a problem with the DBI defaults, the
DBI docs recommend that applications wanting specific behaviour set the
attribute themselves. Thus it should still be patched in Tie::DBI.
Tested only on MySQL, but as I've confirmed that the trailing spaces are
still stored in the database and that explicitly setting ChopBlanks to 0
fixes the problem, I think it's reasonable to assume the same problem
will show up with other database backends. A small script to reproduce
the problem is attached; it requires Tie::DBI and an
appropriately-configured database.
| Subject: | tie-dbi-bug.pl |
#!/usr/bin/perl -w
# demonstrates space-stripping bug in Tie::DBI 1.02
#
# assumes that a database is already configured, and that a table named
# "hash" exists which has at least two columns: "hashkey" for keys and
# "hashval" for values. Do not use TEXT or VARCHAR as the "hashval"
# column type; MySQL at least strips trailing spaces in the actual
# database for these types, whereas BLOB or VARBINARY will store the
# trailing spaces correctly (though Tie::DBI will still strip them.)
use strict;
use Tie::DBI;
use constant TEST_STRING => ' extra spaces ';
use constant TIE_DETAILS => {
db => 'mysql:depth_test',
table => 'hash',
key => 'hashkey',
user => 'depth_test',
password => 'depth_test',
CLOBBER => 2
};
my %hash;
my ($before, $after);
my $dbh;
tie %hash, 'Tie::DBI', TIE_DETAILS or die;
# uncomment to work around the bug
#$dbh = tied(%hash)->dbh;
#$dbh->{ChopBlanks} = 0;
$before = TEST_STRING;
$hash{1} = { hashval => $before };
$after = $hash{1}->{'hashval'};
print "Before: [$before]\n";
print "After : [$after]\n";