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


In reply to Wrong calculation result with data retrieved with DBD::InterBase from view by stefbv

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.