IDS Forum

Re: deccmp return value differs between 2 informix

Posted By: Jonathan Leffler
Date: Wednesday, 22 July 2009, at 7:56 p.m.

In Response To: Re: deccmp return value differs between 2 informix (LAKSHMI DEVI PALANISSAMY)

On Wed, Jul 22, 2009 at 03:26, LAKSHMI DEVI
PALANISSAMY<lakshmidevip@hcl.in> wrote:
> I have referred the ESQL/C manual and understood the decimal strucure and the
> way it stores the exponent and base parts.

Congratulations. If you've understood it properly, you have done
well. It is not trivial.

> Yet I have another query. I am trying to create a table with a variable of
> SMALLFLOAT data type. The variable is having 3 decimal digits. When I tried to
> view the value using "dbaccess" command, more decimal digits are visible.

SMALLFLOAT is a very different type from DECIMAL. SMALLFLOAT
corresponds to a C float.

You say the variable has 3 decimal digits - all well and good, but C
float cannot represent most 3-digit decimal values exactly because it
is a binary floating point system, not a decimal floating point
system.

When the value that is the closest available approximation to your
proposed 3-digit C float value is printed with 8 or 9 digits of
precision (instead of the more conventional 6 or 7 digits of
precision), then you get weird fractional bits attached to what
appeared to be a nice round number.

One way of looking at the problem is: when the string representation
of the number is manipulated, is it the case that every decimal digit
in the string corresponds to some changes in the bits of the float
(but some bit patterns of the float cannot be represented by any
decimal string), or is it the case that every bit pattern can be
represented by at least one string of decimal digits, but some sets of
strings correspond to the same bit pattern.

The conventional way of looking at things is that the decimal
representation is more important, and it doesn't matter if some bit
patterns can't be represented. This view uses 7 digits maximum in the
decimal representation.

The alternative view is that the strings should be convertible such
that every bit pattern can be represented by one or more strings.
This view sometimes needs 8 or 9 decimal digits in the string to make
the necessary distinctions.

You can't have both views active at once because binary cannot
represent every decimal value exactly (because 5 is a factor of 10,
but is not a factor of 2).

> ====================================================
> I have written a stub as follows:
> #include <stdio.h>
> #include <string.h>
> #include <stdlib.h>
> EXEC SQL include decimal;
>
> main()
> {
>
> float a=0.120, b=0.005, c=1.804, d=0.300;
>
> EXEC SQL BEGIN DECLARE SECTION;
> float min1, min2, min3, min4;
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL connect to 'oas';
> EXEC SQL begin work;
>
> EXEC SQL drop table min_max;
>
> EXEC SQL create table min_max(dkey integer, min_value smallfloat);
>
> EXEC SQL insert into min_max values ('1', 0.120);
> EXEC SQL insert into min_max values ('2', 0.005);
> EXEC SQL insert into min_max values ('3', 1.804);
> EXEC SQL insert into min_max values ('4', 0.300);

You really don't need the quotes around the integers; fortunately for
you, Informix converts strings into integers rather readily, but not
all DBMS are as tolerant.

> EXEC SQL commit work;
>
> EXEC SQL select min_value into :min1 from min_max where dkey=1;
> printf ("Value1 is %f\n", min1);

This uses the built-in printf() interpretation of how to print the
data - it uses the 7 digit maximum formatting. Unlike DB-Access,
which uses the longer 8-9 digit notation.

You should try:

printf ("Value1 is %.3f\n", min1);
printf ("Value1 is %.4f\n", min1);
printf ("Value1 is %.5f\n", min1);
printf ("Value1 is %.6f\n", min1);
printf ("Value1 is %.7f\n", min1);
printf ("Value1 is %.8f\n", min1);
printf ("Value1 is %.9f\n", min1);

Eventually, you should see the values that DB-Access produces (with 8 or 9).

> EXEC SQL select min_value into :min2 from min_max where dkey=2;
> printf ("Value2 is %f\n", min2);
>
> EXEC SQL select min_value into :min3 from min_max where dkey=3;
> printf ("Value3 is %f\n", min3);
>
> EXEC SQL select min_value into :min4 from min_max where dkey=4;
> printf ("Value4 is %f\n", min4);
>
> printf ("Float values assigned are %f %f %f %f\n", a, b, c, d);
>
> EXEC SQL disconnect current;
> exit(0);
> }
> ============================================================
> Output of the query "select * from min_max":
>
> dkey min_value
>
> 1 0.119999997
>
> 2 0.00499999989
>
> 3 1.804000020000
>
> 4 0.300000012
> ============================================================
> Stub Output:
> Value1 is 0.120000
> Value2 is 0.005000
> Value3 is 1.804000
> Value4 is 0.300000
> Float values assigned are 0.120000 0.005000 1.804000 0.300000
> ============================================================
> When I retrieved the data from the database and tried to print the value, the
> correct values are displayed.
> But the table show the data with more precision (more than 9)?
> Can I ignore this difference?

For your sanity's sake, you should ignore the difference - or not use DB-Access.

Blatant plug:

SQLCMD does not try to print C float or double values with extraneous
decimal digits and it leads to saner outputs.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Marie von Ebner-Eschenbach - "Even a stopped clock is right twice a
day." - http://www.brainyquote.com/quotes/authors/m/marie_von_ebnereschenbac.html

Messages In This Thread

• deccmp return value differs between 2 informix ver
  LAKSHMI DEVI PALANISSAMY -- Wednesday, 8 July 2009, at 1:24 a.m.
  • Re: deccmp return value differs between 2 informix
    PING TANG -- Wednesday, 8 July 2009, at 6:46 p.m.
    • Re: deccmp return value differs between 2 informix
      LAKSHMI DEVI PALANISSAMY -- Thursday, 9 July 2009, at 5:52 a.m.
      • Re: deccmp return value differs between 2 informix
        Art Kagel -- Thursday, 9 July 2009, at 10:07 a.m.
        • Re: deccmp return value differs between 2 informix
          PING TANG -- Thursday, 9 July 2009, at 6:05 p.m.
          • Re: deccmp return value differs between 2 informix
            LAKSHMI DEVI PALANISSAMY -- Monday, 13 July 2009, at 6:16 a.m.
            • Re: deccmp return value differs between 2 informix
              PING TANG -- Monday, 13 July 2009, at 3:10 p.m.
              • Re: deccmp return value differs between 2 informix
                LAKSHMI DEVI PALANISSAMY -- Tuesday, 14 July 2009, at 10:17 a.m.
              • Re: deccmp return value differs between 2 informix
                LAKSHMI DEVI PALANISSAMY -- Wednesday, 15 July 2009, at 7:48 a.m.
                • Re: deccmp return value differs between 2 informix
                  LAKSHMI DEVI PALANISSAMY -- Wednesday, 15 July 2009, at 9:01 a.m.
                  • Re: deccmp return value differs between 2 informix
                    PING TANG -- Wednesday, 15 July 2009, at 1:13 p.m.
                    • Re: deccmp return value differs between 2 informix
                      LAKSHMI DEVI PALANISSAMY -- Thursday, 16 July 2009, at 4:16 a.m.
                      • Re: deccmp return value differs between 2 informix
                        PING TANG -- Thursday, 16 July 2009, at 1:37 p.m.
                        • Re: deccmp return value differs between 2 informix
                          LAKSHMI DEVI PALANISSAMY -- Friday, 17 July 2009, at 8:50 a.m.
                          • Re: deccmp return value differs between 2 informix
                            PING TANG -- Friday, 17 July 2009, at 6:36 p.m.
                            • Re: deccmp return value differs between 2 informix
                              LAKSHMI DEVI PALANISSAMY -- Tuesday, 21 July 2009, at 8:46 a.m.
                              • Re: deccmp return value differs between 2 informix
                                Art Kagel -- Tuesday, 21 July 2009, at 9:13 a.m.
                              • Re: deccmp return value differs between 2 informix
                                PING TANG -- Tuesday, 21 July 2009, at 12:01 p.m.
                                • Re: deccmp return value differs between 2 informix
                                  LAKSHMI DEVI PALANISSAMY -- Wednesday, 22 July 2009, at 6:26 a.m.
                                  • Re: deccmp return value differs between 2 informix
                                    Art Kagel -- Wednesday, 22 July 2009, at 9:44 a.m.
                                  • Re: deccmp return value differs between 2 informix
                                    PING TANG -- Wednesday, 22 July 2009, at 12:08 p.m.
                                    • Re: deccmp return value differs between 2 informix
                                      LAKSHMI DEVI PALANISSAMY -- Friday, 24 July 2009, at 5:46 a.m.
                                  • Re: deccmp return value differs between 2 informix
                                    Jonathan Leffler -- Wednesday, 22 July 2009, at 7:56 p.m.
                  • Re: deccmp return value differs between 2 informix
                    Jonathan Leffler -- Thursday, 16 July 2009, at 8:59 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.