| Subject: | DBD::DB2 trims tailing space characters of CHAR/VARCHAR |
| Date: | Wed, 21 Oct 2009 20:45:42 +0900 |
| To: | bug-DBD-DB2 [...] rt.cpan.org |
| From: | Nobuhiro Ban <ban.nobuhiro [...] gmail.com> |
Distribution name and version : DBD-DB2 1.71
Perl version : v5.10.0 built for i486-linux-gnu-thread-multi
Operating System vendor and version : Debian GNU/Linux 5.0.3 (Linux 2.6.24)
Description:
Since I updated my Perl DB2 client from Perl 5.8 + DB2 V7 + DBD::DB2 0.78
to Perl 5.10 + DB2 V9 + DBD::DB2 1.71, some strange phenomena were found.
DBD::DB2 trims tailing space characters of CHAR/VARCHAR (maybe all strings).
(I give { ChopBlanks => 0 } to DBI, of course.)
In dbdimp.c of 1.71 (since 1.61):
Show quoted text
> else if( ChopBlanks && SQL_CHAR == fbh->dbtype )
> sv_setpvn( sv,
> fbh->buffer,
> GetTrimmedSpaceLen( fbh->buffer, fbh->rlen ) );
> else
> sv_setpvn( sv, (char*)fbh->buffer, GetTrimmedSpaceLen( fbh->buffer, fbh->rlen ) );
Same if-else clause? It seems to trim the ALL strings (except for XML).
In dbdimp.c of 0.78:
Show quoted text> else if( ChopBlanks && SQL_CHAR == fbh->dbtype )
> sv_setpvn( sv,
> fbh->buffer,
> GetTrimmedSpaceLen( fbh->buffer, fbh->rlen ) );
> else
> sv_setpvn( sv, (char*)fbh->buffer, fbh->rlen );
Good, it seems to trim only the case of CHAR and { ChopBlanks => 1 }.
=== Test Data ===
Show quoted text> CREATE TABLE choptest(c CHAR(5), vc VARCHAR(5))
> INSERT INTO choptest values('12345', '12345')
> INSERT INTO choptest values(' 3 ', ' 3 ')
Show quoted text> SELECT CONCAT(c,'e'), CONCAT(vc,'e') FROM choptest
>1 2
>------ ------
>12345e 12345e
> 3 e 3 e
=== Test Code ===
use strict;
use DBI;
my $dbh = DBI->connect("DBI:DB2:XXXXX", "XXXXXX", "XXXXXX",
{RaiseError=>1, AutoCommit=>0, ChopBlanks=>0 /* or 1 */ }) or die
$DBI::errstr;
my $sth;
my @row;
$sth = $dbh->prepare("SELECT CONCAT(c,'e'), CONCAT(vc,'e') FROM choptest");
$sth->execute();
while (@row = $sth->fetchrow_array) { print "($row[0])($row[1])\n"; }
$sth->finish;
$sth = $dbh->prepare("SELECT c, vc FROM choptest");
$sth->execute();
while (@row = $sth->fetchrow_array) { print "($row[0])($row[1])\n"; }
$sth->finish;
$sth = $dbh->prepare("SELECT ' ' FROM choptest");
$sth->execute();
while (@row = $sth->fetchrow_array) { print "($row[0])\n"; }
$sth->finish;
$dbh->rollback;
$dbh->disconnect;
=== Result ===
*** DBD::DB2 0.78
* ChopBlanks => 0
(12345e)(12345e)
( 3 e)( 3 e)
(12345)(12345)
( 3 )( 3 )
( )
( )
* ChopBlanks => 1
(12345e)(12345e)
( 3 e)( 3 e)
(12345)(12345)
( 3)( 3 )
( )
( )
*** DBD::DB2 1.71
* ChopBlanks => 0
(12345e)(12345e)
( 3 e)( 3 e)
(12345)(12345)
( 3)( 3)
()
()
* ChopBlanks => 1
(12345e)(12345e)
( 3 e)( 3 e)
(12345)(12345)
( 3)( 3)
()
()
Regards,
Nobuhiro