stefbv has asked for the wisdom of the Perl Monks concerning the following question:

In a Firebird database I have a table, and a view, based on this table. The view is used to make a simple arithmetical calculation.

The problem is that the result of the calculation is wrong, when returned with DBD::InterBase, but correct when I use isql or Flamerobin to make a select from the view.

Script for creating the table and the view (edit dbname, server, user and password) :

use strict; use warnings; use DBI; my $dbname = 'testdb'; # The testdb database must exist my $server = 'localhost'; my $user = 'username'; my $pass = 'password'; my $dbh = DBI->connect( "DBI:InterBase:" . "dbname=" . $dbname . ";host=" . $server . ";ib_dialect=3", $user, $pass, { AutoCommit => 1, RaiseError => 1, FetchHashKeyName => 'NAME_lc' +} ); my $sql1 = qq{ CREATE TABLE testtable ( cant DECIMAL(9,3) NOT NULL, vat DECIMAL(4,2) NOT NULL, price NUMERIC(11,5) NOT NULL) }; my $rv = $dbh->do( $sql1 ); my $sql2 = qq{ CREATE VIEW v_testtable ( cant , vat , price , val , vat_val ) AS SELECT tt.cant , tt.vat , tt.price , tt.cant * tt.price , tt.cant * tt.price * tt.vat /100 FROM testtable tt; }; $rv = $dbh->do( $sql2 ); # Insert some data my $sql3 = qq{ INSERT INTO testtable (cant, vat, price) VALUES (?, ?, ?); }; my $st = $dbh->prepare($sql3); my @testval = ( [ 1, 19, 100 ], [ 1, 19, 1000 ], [ 1, 19, 10000 ], [ 1, 19, 100000 ], ); foreach my $val ( @testval ) { $st->execute( @{$val} ); } $dbh->disconnect();

Script for retrieving the result of the calculation:

use strict; use warnings; use DBI; my $dbname = 'testdb'; my $server = 'localhost'; my $user = 'username'; my $pass = 'password'; my $dbh = DBI->connect( "DBI:InterBase:" . "dbname=" . $dbname . ";host=" . $server . ";ib_dialect=3", $user, $pass, { AutoCommit => 1, RaiseError => 1, FetchHashKeyName => 'NAME_lc' +} ); my $sql4 = qq{ SELECT cant , vat , price , val , vat_val FROM v_testtable }; eval { my $st = $dbh->prepare($sql4); $st->execute; while ( my $rez = $st->fetchrow_hashref() ) { print $rez->{cant} * $rez->{price} * $rez->{vat} /100,"\t"; print "$rez->{vat_val}\n"; } }; if ($@) { warn "Transaction aborted because $@"; } $dbh->disconnect();

The result on my system (Slackware 13.0), Firebird-2.1.3 with DBD::InterBase-0.48 is:

Expected Result --------- ----------------- 19 -88.1021438976 190 -884.1624455168 1900 -8847.1212166144 19000 -88475.1384243200

With ActivePerl-5.8.7-815, Firebird-2.1.3, DBD-InterBase-0.44 is:

19 134.1051235328 190 1347.0641895424 1900 13474.0778692608 19000 134745.0736599040

Can anyone reproduce this behavior, or I'm doing something very wrong here?

Thank You for your time and efort.

Best regards, Stefan

Replies are listed 'Best First'.
Re: Wrong calculation result with data retrieved with DBD::InterBase from view
by pilcrow (Sexton) on Mar 23, 2010 at 16:24 UTC

    DBD-InterBase 0.48 appears to have a bug in converting high-scale data. You can work around it by explicitly CAST()ing your "faulty" column in your VIEW definition to something like NUMERIC(18, 5)

    I filed this as rt.cpan #55841. In my tests, any NUMERIC/DECIMAL with a scale >= 10 is corrupted:

    #! /usr/bin/perl # perl t.pl dbi:InterBase:dbname=... user pass use DBI; use strict; use warnings; my $dbh = DBI->connect(@ARGV[0..2], {RaiseError => 1}); for my $scale (1 .. 17) { my $cast = "CAST(19 AS NUMERIC(18, $scale))"; my ($r) = $dbh->selectrow_array("select $cast FROM rdb\$databa +se"); print "$cast ...... $r\n"; }
    This prints:
    CAST(19 AS NUMERIC(18, 1)) ...... 19 CAST(19 AS NUMERIC(18, 2)) ...... 19 CAST(19 AS NUMERIC(18, 3)) ...... 19 CAST(19 AS NUMERIC(18, 4)) ...... 19 CAST(19 AS NUMERIC(18, 5)) ...... 19 CAST(19 AS NUMERIC(18, 6)) ...... 19 CAST(19 AS NUMERIC(18, 7)) ...... 19 CAST(19 AS NUMERIC(18, 8)) ...... 19 CAST(19 AS NUMERIC(18, 9)) ...... 19 CAST(19 AS NUMERIC(18, 10)) ...... -88.1021438976 CAST(19 AS NUMERIC(18, 11)) ...... -884.01624455168 CAST(19 AS NUMERIC(18, 12)) ...... -8847.001212166144 CAST(19 AS NUMERIC(18, 13)) ...... -88475.0001384243200 CAST(19 AS NUMERIC(18, 14)) ...... -884756.00000957530112 CAST(19 AS NUMERIC(18, 15)) ...... -8847564.000000985366528 CAST(19 AS NUMERIC(18, 16)) ...... -88475644.0000001263730688 CAST(19 AS NUMERIC(18, 17)) ...... -884756445.00000001899888640