| Subject: | wrong type returned for DECIMAL col w/mysql 5.0.x |
See also: <http://bugs.mysql.com/bug.php?id=15556>
Description:
When connecting to mysql 5, it returns the type of a DECIMAL column as
SQL_VARCHAR, as opposed to SQL_DECIMAL returned when the mysql server is version
4.1.
The details of my setup:
Mac OS X Tiger 10.4.3
perl 5.8.6
DBI 1.47
DBD::mysql 3.0002 (and 3.0002_04) (built with mysql-5.0.16 libraries)
mysql 5.0.16
mysql 4.1.15
See also thread at <http://lists.mysql.com/perl/3696>.
How to repeat:
The script below demonstrates the bug, outputing ...
value 12 SQL_VARCHAR
... when connecting to a mysql 5.0.16 server on localhost, vs ...
value 3 SQL_DECIMAL
... when connecting to a mysql 4.1.15 server on localhost.
#!/usr/bin/perl -w
use DBI;
## get DBI type map
my %map;
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
$map{&{"DBI::$_"}} = $_;
}
my $dbh = DBI->connect('DBI:mysql:test', 'test');
my $table = 'mysql5bug';
my $drop = "DROP TABLE IF EXISTS $table";
my $create = "CREATE TABLE $table (value decimal(5,2));";
my $select = "SELECT * FROM $table WHERE 1 = 0";
## create table and get column types
$dbh->do($drop) or die $dbh->errstr;
$dbh->do($create) or die $dbh->errstr;
my $sth = $dbh->prepare( $select );
my $rv = $sth->execute;
my $fields = $sth->{NAME};
my $types = $sth->{TYPE};
## print out column types
foreach (0..$#$fields) {
printf("%8s %3d %s\n", $fields->[$_], $types->[$_], $map{$types->[$_]});
}
## cleanup
$dbh->do($drop) or die $dbh->errstr;
$sth->finish;
$dbh->disconnect;
1;