in reply to DBD::ODBC FoxPro double type numbers rounded

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 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' } };

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.

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.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re^2: DBD::ODBC FoxPro double type numbers rounded
by hanspr (Sexton) on Oct 01, 2019 at 05:14 UTC

    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.