On Thu, 9 Sep 2004 17:58:48 -0400, Rutherdale, Will
<[EMAIL PROTECTED]> wrote:
> I'm reasonably experienced with Perl and with databases but new to Perl-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 <[EMAIL PROTECTED]> #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."
