Consider:
use DBI;
$dbh = DBI->connect('dbi:Informix:stores','','',{RaiseError=>1});
$dbh->do('create temp table zeroes(i integer, d decimal, d6 decimal(6),
d6_2 decimal(6,2))');
$dbh->do('insert into zeroes values(0,0,0,0)');
$sth = $dbh->prepare('select * from zeroes');
$sth->execute();
@row = $sth->fetchrow_array;
print "$row[0] $row[1] $row[2] $row[3]\n");
Even with DBD::Informix 1.04.PC1, it prints:
0 0.000000000000000E+00 0.00000E+00 0.00
(The default precision for DECIMAL is DECIMAL(16), and DECIMAL(n) in
Informix means a floating point number - rather than a fixed point number.
DECIMAL(6,2) means a total of 6 digits, 2 of them after the decimal point.)
You can convert the value to a Perl number by adding 0: that might lose
information in extreme cases, though.
If you have a good algorithm for deciding when the fixed point notation is
appropriate and when the exponential notation is appropriate, then please
tell me.
...Hmmmmm...or maybe I already know -- SQLCMD prints 0 0.0 0.0 0.00 -- so
applying the formatting from there to DBD::Informix would probably satisfy
your needs?
--
Jonathan Leffler ([EMAIL PROTECTED])
STSM, Informix Database Engineering, IBM Data Management Solutions
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 |
| | <[EMAIL PROTECTED]|
| | nk.net> |
| | |
| | 02/26/2003 07:45 |
| | PM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: jeffery sumler <[EMAIL PROTECTED]>
|
| cc: [EMAIL PROTECTED], Jonathan Leffler/Menlo Park/[EMAIL PROTECTED]
|
| Subject: Re: DBD::Informix returning 0.0 as 0.00000E+00 ?
|
|
|
|
|
>---------------------------------------------------------------------------------------------------------------------------------------------|
jeffery sumler wrote:
> I'm currently working on a project that involves replacing a number of
> scripts that are wrappers around Informix's dbaccess with
> DBI/DBD::Informix.
Consider getting SQLCMD from the IIUG software archive
(http://www.iiug.org/software). It is designed to replace DB-Access.
> An odd issue has cropped up: where dbaccess returns (or formats)
> what are I assume floating point types equal to zero as 0.0, DBI
> returns 0.00000E+00.
>
> Here's a snippet of a dbaccess dump:
>
> 0004010002|O| |0|ML4863CS|S|ML4863CS|
|EA|6111.0|AMC09|1.404||||||0.0|0.0|EDI850| | |
>
> and the corresponding line from one of our DBI/DBD::Informix scripts:
>
>
0004010002|O||0|ML4863CS|S|ML4863CS||EA|6111|AMC09|1.4040||||||0.00000E+00|0.00000E+00|EDI850|||
>
>
> All of these scripts create text files that are later fed into Oracle
> and there are situations in which sqlldr chokes on 0.00000E+00.
>
> Obviously we can munge the output formatting in our scripts with s/// so
> this is not a critical issue. We would prefer to not go down that route
> however as this Informix -> Oracle transfer is somewhat time sensitive
> and the less manipulation we have to do, the better.
>
> I've read the dbi-users archive, the DBD::Informix docs and searched a
> variety of other Informix resources with no luck. Does anyone have any
> suggestions ?
>
> DBD::Informix is version 1.00.PC2
> DBI is version 1.21
1.04.PC2 has different code in dbdimp.ec - function decgen() - to
handle formatting, and should avoid the exponential notation on
fixed-point decimals. So, one option is to try that; I'm at home and
don't have it compiled up so I can't verify it for you tonight.
Alternatively, you can hack the decgen() code to suit yourself. You
could probably use the normal ESQL/C decimal formatting functions.
DBD::Informix does not because of subtle problems in old versions of
ESQL/C.
Or you could hack the code for formatting decimals from SQLCMD
(mentioned above) into DBD::Informix. There's a lot of commonality,
but also some slight differences (dec_sci() is a newer version of
decsci(), and dec_fix() is a newer version of decfix()).
> I unfortunately have no control over the Informix db so upgrades are
> impractical if not impossible.
The database does not need to be upgraded for this to be fixed.
> We're trying to make the DBI based replacement for the dbaccess wrappers
> as generic as possible so using printf or sprintf on our query results
> to control the formatting really isn't practical. The deprecated $#
> variable has no effect.
The decimal formatting code goes nowhere near Perl at that point.
> I suspect that somewhere something like
>
> printf "%E", $foo;
>
> is happening but I have no idea where . . . .
More or less - see above.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
#include <disclaimer.h>
Guardian of DBD::Informix 1.04.PC1 -- http://dbi.perl.org/
