I vaguely remember that you can pass some attributes to prepare or bind to help DBI / DBD::ODBC with the correct data types. In your case, you need to mark the result column as floating point number (instead of int / string). See bind_col in DBD::ODBC.

In general, DBI / DBD::ODBC should be able to auto-detect the type of any result column, but it seems that something got messed up with FoxPro. Perhaps FoxPro wrongly reports the column as some integer type at the ODBC level.

Alexander

Hello hanspr,

I do not know if you have already resolved your problem but I spend some time trying to replicate it on my PC. Unfortunately I am running LinuxOS so it is not exactly the same but also I am running the latest Perl and DBD::ODBC version. So again it is not exactly the same. But never the less I think the problem can be found.

I am running the code bellow on my sampleTable:

#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use Data::Dumper;
use feature 'say';
use ExtUtils::Installed;

my @modules;
my $installed = ExtUtils::Installed->new();

if (scalar(@ARGV) > 0) {
    @modules = @ARGV;
} else {
    @modules = $installed->modules();
    if (grep (/^DBD::ODBC/i, @modules)) {
    say "DBD::ODBC\t" . $installed->version('DBD::ODBC');
    }
}

my $user = 'root';
my $pasw = 'root';

my @dsns = DBI->data_sources('ODBC');
foreach my $dsn (@dsns) {
    say $dsn;
    my $dbh = DBI->connect($dsn,
               $user,
               $pasw,
               {RaiseError => 1},
    )
    or die($DBI::errstr);

    my $table = 'sampleTable';
    my $sth = $dbh->prepare("SELECT * FROM `$table`")
    or die($DBI::errstr);

    $sth->execute()
    or die($DBI::errstr);

    my $column = 'floatNumber';
    while (my $row = $sth->fetchrow_hashref) {
    print Dumper $row;
    say "floatNumber = $$row{$column}";
    }

    $sth = $dbh->column_info( undef, undef, $table, $column );
    my $hash_ref = $sth->fetchall_hashref('TYPE_NAME');
    print Dumper $hash_ref;
}

__END__

$ perl test.pl 
DBD::ODBC    1.60
dbi:ODBC:my-connector
$VAR1 = {
          'floatNumber' => '1.2345',
          'Id' => 1
        };
floatNumber = 1.2345
$VAR1 = {
          'double' => {
                        'CHAR_OCTET_LENGTH' => undef,
                        'TABLE_NAME' => 'sampleTable',
                        'DECIMAL_DIGITS' => undef,
                        'BUFFER_LENGTH' => 8,
                        'SQL_DATETIME_SUB' => undef,
                        'TABLE_CAT' => '',
                        'NUM_PREC_RADIX' => undef,
                        'IS_NULLABLE' => 'NO',
                        'COLUMN_SIZE' => 15,
                        'REMARKS' => '',
                        'TABLE_SCHEM' => undef,
                        'COLUMN_DEF' => '0',
                        'TYPE_NAME' => 'double',
                        'NULLABLE' => '0',
                        'SQL_DATA_TYPE' => '8',
                        'ORDINAL_POSITION' => 1,
                        'DATA_TYPE' => '8',
                        'COLUMN_NAME' => 'floatNumber'
                      }
        };
[download]

I checked online regarding the changes of the module DBD::ODBC/Changes over time and I found:

1.49_3 2014-05-01

[CHANGE IN BEHAVIOUR]
 
As warned years ago, this release removes the odbc_old_unicode attribu
+te.
If you have a good reason to use it speak up now before the next non-d
+evelopment
release.
 
[BUG FIXES]
 
Fix rt89255: Fails to create test table for tests using PostgreSQL odb
+c driver.
Change test suite to fallback on PRECISION if COLUMN_SIZE is not found
+.
 
[ENHANCEMENTS]
 
Added support for MS SQL Server Query Notification. See the new
section in the pod.
 
Added a currently undocumented (and experimental)
odbc_describe_param method on a statement handle which takes a
parameter number as the only argument and returns an array of the
data type, parameter size, decimal digits and nullable (as per
SQLDescribeParam).
 
[DOCUMENTATION]
 
Added FAQ on truncated column names with freeTDS.
 
[MISCELLANEOUS]
 
I have removed the "experimental" tag for odbc_getdiagfield and odbc_g
+etdiagrec
methods.
[download]

It looks that on version 1.49 was added the decimal compatibility. Again I might be wrong but through the script you can print the type of your DB type and see if it is categorized as float or what ever. After that is it possible to increase the DBD::ODBC to the latest version or this is not possible?

Readmore about MySQL DECIMAL Data Type.

I hope this helps, BR.

Hi,

Sorry to answer so late, I thought that I actually posted a solution at the time, to document what I did.

I did solve it by adding the function round like this:

$sth = $dbh->prepare("select round(monto,2) from table producto where pid=1");

Not really a solution, but it has been working.