Re: DBD::Informix 1.00.PC1 problems retrieving floats

On Mon, 12 Feb 2001, Hugh Alexander wrote:

>I tried dbi-users with this question, but didn't get much response,
>perhaps someone at this address has an idea.

Mainly because I happened to be off the air (and off skiing) from Friday
through Sunday.

>I am using perl 5.005 and Informix dynamic server 9.21.UC3.  I built a
>version of DBD::Informix 1.00.PC1 using Informix clientsdk 2.20UC1 and
>everything worked great, then I built a version using Informix clientsdk
>2.50UC2 and when I retrieve values that are defined as 'float' from the
>database they come back as different numbers:

There was a change made by someone at Informix who decided that 32-bit
floats could suddenly squeeze more precision out of a 32-bit float than
most hardware will put into them.  I didn't know it affected 2.50, but
I've not used it all that extensively.

>The ouput below is from two seperate queries:
>'select name, num from data_table'
>'select name, num::smallfloat from data_table'
>
>output from a script using DBD::Informix built with clientsdk 2.20UC1:
>Select data from temp table...
>Name: joe       , Num: 15.75
>Name: john      , Num: 114.04

Obviously, 15.75 has an exact binary representation; 0.04 does not.
Maybe the answer is to use decimals instead of float, but that does
not address the issue of what changed.  Since you're using 1.00.PC1
in both cases, the problem relates to how the data is fetched into
into memory.

The good news is I can reproduce the problem on my Solaris 7 box
with the script:

    Anubis JL: perl xx.pl
    15.75
    114.040001
    Anubis JL: cat xx.pl
    #!/usr/bin/perl -w
    use strict;
    use DBI;
    my $dbh = DBI->connect('dbi:Informix:stores','','',{RaiseError=>1});
    $dbh->do("create temp table geewhiz(sf smallfloat not null)");
    $dbh->do("insert into geewhiz values(15.75)");
    $dbh->do("insert into geewhiz values(114.04)");
    my $sth=$dbh->prepare("select * from geewhiz");
    $sth->execute;
    my @row;
    while (@row = $sth->fetchrow_array)
    {
            print "$row[0]\n";
    }
    $dbh->disconnect;
    Anubis JL:

That's the bad news, too, I suppose.

    Anubis JL: InformixTechSupport -V
    Perl Version 5.006
    DBI Version 1.14
    DBD::Informix Version 1.00.PC1
    INFORMIX-ESQL Version 9.30.UC1
    DBD::Informix Licence Number ACN#J334912 (default - no Tech Support available)
    Anubis JL:

I'm using CSDK 2.40, with the same trouble.  So, what to do?
The relevant code in dbdimp.ec is at line 1862:

        case SQLFLOAT:
        case SQLSMFLOAT:
        case SQLDECIMAL:
        case SQLMONEY:
            /*
            ** Default formatting (in some versions of ESQL/C)
            ** assumes 2 decimal places -- wrong!
            */
            EXEC SQL GET DESCRIPTOR: nm_obind VALUE:index
                    :decval = DATA;
            strcpy(coldata, decgen(&decval, 0));
            result = coldata;
            length = strlen(result);
            /* warn("Decimal Data: %d <<%s>>\n", length, result); */
            break;

It treats these numeric types the same.  And decgen() is known to be not
entirely satisfactory, too.  The code in the 1.10.PC1 prototype reads:

        case SQLFLOAT:
        case SQLSMFLOAT:
        case SQLDECIMAL:
        case SQLMONEY:
            /*
            ** Default formatting (in some versions of ESQL/C)
            ** assumes 2 decimal places -- wrong!
            */
            EXEC SQL GET DESCRIPTOR: nm_obind VALUE:index
                    :decval = DATA;
            strcpy(coldata, decgen(&decval, collength));
            result = coldata;
            length = strlen(result);
            /* warn("Decimal Data: %d <<%s>>\n", length, result); */
            break;

And the code for decgen() has been rewritten as:

    /* Convert DECIMAL to convenient string */
    /* Patches problems with Informix conversion routines in pre-7.10 versions */
    /* Don't forget that decimals are stored in a base-100 notation */
    static char    *
    decgen(dec_t *val, int collen)
    {
        char *str;
        int dp = PRECDEC(collen);   /* Decimal places */
        int sf = PRECTOT(collen);   /* Significant digits */

        if (dp == 0xFF)
        {
            /* Floating point decimal */
            str = decsci(val, sf, 0);
        }
        else
        {
            str = decfix(val, dp, 0);
        }
        while (*str == ' ')
            str++;
        /* Chop trailing blanks */
        str[byleng(str, strlen(str))] = '\0';
        return str;
    }

Try this and see...


>Select data from temp table...
>Name: joe       , Num: 15.75
>Name: john      , Num: 114.04
>
>same script using DBD::Informix built with clientsdk 2.50UC2:
>Select data from temp table...
>Name: joe       , Num: 15.75
>Name: john      , Num: 114.04000000000001
>Select data from temp table...
>Name: joe       , Num: 15.75
>Name: john      , Num: 114.040001
>
>I looked through the Notes/* and README files, but I don't see anything
>that mentions this type of problem.  Has anyone else seen anything like
>this?
>
>-=hugh
>

--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"

• Re: DBD::Informix 1.00.PC1 problems retrieving floats by Jonathan Leffler