Re: float bug? perl 5.8, DBI and oracle 10.2.0

Hi again! Thanks Tim for this thorough explanation :)

For the record, DBD::Oracle binds parameters and fetches values as strings.

Great, now we are getting closer to the heart :)

As I wrote in my first post, the problem can be triggered with the
following perl test:

-----------------<snip>---------------------------
use strict;
use warnings;
use Data::Dumper;
use Test::More tests => 1;
use Carp qw(confess);
use DBI;

# database credentials: EDIT HERE
my $ORASID = $ENV{ORACLE_SID};
my $ORAUSR = 'username';
my $ORAPWD = 'password';

my $DBC;

sub sql_execute {
my ($sql,@arg) = @_;
my $sth = $DBC->prepare($sql);
if(!$sth || $sth->err) {
confess "prepare failed for [$sql]\nbecause: [".$DBC->errstr."]";
}
$sth->execute(@arg) ||
confess "exec failed: [".$sth->errstr."]\nin query [$sql]";
return $sth;
}

# connect to oracle
($DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
{
PrintError=>0,
AutoCommit=>0,
}
)) ||
confess "failure connecting to $ORASID: ".$DBI::errstr;

# create one temporary table with one numeric column filled with test data
eval { sql_execute("DROP TABLE test_oracle_bug"); };
sql_execute("CREATE TABLE test_oracle_bug (DATA NUMBER)");
sql_execute("INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)");
$DBC->commit;

# fetch numeric from table
my $ret = sql_execute("SELECT DATA FROM test_oracle_bug")->fetchrow_arrayref;
my ($val) = @$ret;

my $sum = 1.73696 - $val;
is($sum,0,"does sum $sum == 0?");

-----------------<snip>---------------------------

As you see, the numeric value is inserted in the database with:

"INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)"

I therefore assume that we are passing a string forward to
DBD::Oracle, and that it is a string that gets inserted into the
column DATA of type NUMBER in oracle. If I understood Tim well, the
number 1.73696 is then stored as a decimal string in the oracle
database.

When we later retrieve this value ("my ($val) = @$ret;") from the
table, what we get in $val should then be the same string, as returned
by DBD::Oracle. A look at $val with Devel::Peek confirms this:

"Dump($val);" says:

SV = PV(0x97bf684) at 0x988aed8
REFCNT = 1
FLAGS = (PADBUSY,PADMY,POK,pPOK)
PV = 0x9aaff18 "1.73696"\0
CUR = 7
LEN = 8

No IV (integer value) or NV (native float) in this SV (scalar value),
just a PV (string).

From there on, it would seem reasonable to believe that we can exclude
a DBI/DBD::Oracle problem: what we are dealing with is a string
representation of 1.73696.

Now, we reach the next line of code: "my $sum = 1.73696 - $val;". This
line evals to $sum = 2.22044604925031e-16. That's the problem.

However! if I try getting the same effect from pure perl code:

-----------------<snip>---------------------------

use strict;
use warnings;
use Test::More tests => 1;

my $val = "1.73696";
my $sum = 1.73696 - $val;

is($sum,0,"sum is 0");

-----------------<snip>---------------------------

Then the test passes, $sum is 0.

In other words, "1.73696 - $val" evaluates to 0 when $val is the
string "1.736960" hardcoded in the source code, but does not when $val
is the *same* string but returned by fetchrow_arrayref. Notice here
that according to Devel::Peek, the SV of the hardcoded $val and that
of the retrieved $val are identical. But obviously, something between
them must differ, otherwise the same operation on both would yield the
same result 8(

Is there any reason why the string 1.73696 returned by
fetchrow_arrayref would differ from the string "1.73696" hardcoded in
source code?

I have a feeling that Tim's example holds the answer:

$a = 42.1; $a += 0.1 for (1..30); # add 3
$b = "$a"; # cross the client/server interface as text
print "a=$a b=$b\n";
($a == $b) ? print "Equal\n" : print "Not equal!\n";

Need to think more...

/Erwan
.

Relevant Pages

  • Re: Data from web site
    ... Thanks Tim, don't think I need to try that now:) ... >> I'm not clued up on HTML, but hopefully that is the name of the frame I ... >>> You can then parse out the info from the returned string. ... >>> Dim objShell As Object, objShellWindows As Object, o As Object ...
    (microsoft.public.excel.programming)
  • Re: Catch-A-Call
    ... aka Kuay Tim ... Any higher value extends the time the modem waits ... > This string has two AT commands. ... In the Extra Settings box, ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)
  • Re: replacing text in textfile
    ... Thank you again Tim for they reply. ... >> Public Function ReplaceText(CleanThis As String, ... >> 'This adds a space before and after NewText ... > This is pretty wanton inserting and removing white space, ...
    (microsoft.public.access.modulesdaovba)
  • Re: how to change decimal symbol in VB / Access?
    ... PS> that's right TIM, some of the code wasn't real, becouse the real code is ... > string and double has a lower case letters, so I assume that this is not ... the backslash forces the use of a dot rather than whatever OLEAUT32 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Open e-mail attachment
    ... Tim, first you need to save the attchment to the file system. ... ByVal lpFile As String, ByVal lpParameters As String, _ ... Michael Bauer - MVP Outlook ...
    (microsoft.public.outlook.program_vba)