Re: Losing Precision from FLOAT in DBD::Informix
From: Jonathan Leffler (jleffler_at_us.ibm.com)
Date: 09/10/04
- Next message: Michael Peppler: "Re: DBD::Sybase 1.04 and Fork core"
- Previous message: Kirti S. Bajwa: "RE: Perl with MySQL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: Jonathan Leffler <jonathan.leffler@gmail.com> Date: Fri, 10 Sep 2004 14:00:45 -0700
I've confirmed the problem - it appears in DBD::Informix 2003.04 on
Solaris 8
with Perl 5.8.5, DBI 1.43, CSDK 2.90.UC1B4 (a beta release) running
against
IDS 9.50.UC1N395 (a nightly build); I have no doubt that the versions of
Perl,
DBI and o/s are immaterial. The versions of CSDK and IDS are also not
dreadfully
important either.
The outline solution from last night is more or less correct - the
formatting
leaves something to be desired (as it drops even more digits than the
current
buggy solution).
I hope to release the 'official' solution soon (though CSDK 2.90 will
screw up
a separate part of the build process once released on the general public,
so I
have a bit of work to do to clean up around that).
In the mean time, these changes fix the trouble.
At the top of the file:
#include <float.h>
In dbd_ix_st_fetch():
case SQLFLOAT:
{
$ double dblval;
EXEC SQL GET DESCRIPTOR :nm_obind VALUE :index :dblval = DATA;
sprintf(coldata, "%.*g", DBL_DIG, dblval);
result = coldata;
length = strlen(result);
}
break;
case SQLSMFLOAT:
{
$ float fltval;
EXEC SQL GET DESCRIPTOR :nm_obind VALUE :index :fltval = DATA;
sprintf(coldata, "%.*g", FLT_DIG, fltval);
result = coldata;
length = strlen(result);
}
break;
I don't like the repetition of 'result=coldata;length=strlen(result);' but
I haven't spent the energy removing that yet. The '$' notation is a
shorthand
in Informix ESQL/C. The final fix will move those declarations up with
the
other variables between the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL
END
DECLARE SECTION, which makes the '{}' lines unnecessary.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
Jonathan Leffler <jonathan.leffler@gmail.com> wrote on 09/09/2004 09:46:32
PM:
> On Thu, 9 Sep 2004 17:58:48 -0400, Rutherdale, Will
> <will.rutherdale@sciatl.com> wrote:
> > I'm reasonably experienced with Perl and with databases but new
toPerl-DBI.
> >
> > I'm trying to write a conversion program from a legacy DB at work and
> > discovered that some values weren't copied accurately. The columns
involved
> > are declared FLOAT.
> >
> > It's an Informix DB running on Solaris.
>
> So, please read the README and associated files to find out how to
> report such problems. One of the steps is to include DBD::Informix in
> the subject line (as I added it). Another is to include the versions
> of Perl, DBI, DBD::Informix, ESQL/C and your database server (usually,
> but not necessarily, IDS). If your verson of DBD::Informix is not
> 2003.04, then the first step is to show that the problem still occurs
> there - please.
>
> > I've mocked up a test table with data and code to distill the problem
to
> > something analysable.
> >
> > Schema:
> > create table abc
> > (
> > id int not null,
> > val float
> > );
> >
> > Data:
> > $ echo "select * from abc" | dbaccess pncabc
> > . . .
> > id val
> >
> > 0 1.234567893700
> > 1 2.897430129800
> >
> > Code:
> > my ( $sth ) = $dbh->prepare( qq{SELECT id, val FROM abc} );
> > $sth->execute();
> > while ( my ( $id, $val ) = $sth->fetchrow_array() )
> > {
> > print STDERR "id==$id, val==$val\n";
> > }
> >
> > Result:
> > id==0, val==1.23456789
> > id==1, val==2.89743013
> >
> > As you can see, data in the 'val' column is using >=10 digits of
precision,
> > but this gets rounded off to only 9 digits when the query returns.
> >
> > My questions:
> > - where and why is the precision lost?
> > - how can I coerce DBI into giving me more precision?
> >
> > Any help would be greatly appreciated.
>
> An SQL FLOAT in Informix is equivalent to a C double. That means that
> the loss of precision is not really excusable - and is not a problem
> in the database. I'd virtually guarantee that (but virtual guarantees
> are only ever worth the paper they are written on, of course :-)
>
> So, the problem code, if it is still there, is likely to be in
> dbd_ix_st_fetch() in dbdimp.ec.
>
> For reasons that now elude me, 'case SQLFLOAT' fetches the value into
> a 'Decimal' - struct dec_t more usualy - so there is a chance that the
> problem is in ESQL/C and the way it handles FLOAT to DECIMAL
> conversions -- this is why the ESQL/C version is critical. If your
> version was old enough, I'd state that the problem is most likely
> there with very little chance of being wrong. The more recent the
> version, the less likely it is to be the problem, but it still could
> be a problem. I'll try to reproduce the issue tomorrow (other things
> being equal) - on Solaris 8.
>
> The alternative way to work it is to modify the switch so that the
> SQLFLOAT clause (and SQLSMFLOAT?) fetch into a native double variable,
> and then format that into the coldata variable:
>
> case SQLFLOAT:
> {
> $ double d;
> EXEC SQL GET DESCRIPTOR :nm_obind VALUE :index :d = DATA;
> sprintf(coldata, "%g", d);
> result = coldata;
> length = strlen(result);
> }
> break;
>
> You may need to choose a different (more complex) format for the value
> than just %g to get the full precision -- one of the reasons for not
> using printf() directly.
>
> Let me know if any of this helps - and please do include the version
> information next time!
> Thanks.
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
> "I don't suffer from insanity - I enjoy every minute of it."
- Next message: Michael Peppler: "Re: DBD::Sybase 1.04 and Fork core"
- Previous message: Kirti S. Bajwa: "RE: Perl with MySQL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
