Poblem in accessing decimal data type in informix
An ESQL/C application fetched DECIMAL columns into dec_t fields, but a helper wrapper around dectodbl() (DecRetDbl) always returned 0.0000 even though the value looked correct inside the function. Suggestions included checking the ESQL/C demos, dumping the dec_t internals, using dectoasc instead of converting to double (losing precision), and posting a minimal reproducible example; Jonathan Leffler showed a ~40-line test program that worked correctly. The poster eventually found the cause in his own code: a missing function prototype in his header file, so the return value was mis-typed; adding it fixed the problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Our application is trying to read a decimal value from our database. Structure decimal is defined in decimal.h file in informix struct decimal { int2 dec_exp; /* exponent base 100 */ int2 dec_pos; /* sign: 1=pos, 0=neg, -1=null */ int2 dec_ndgts; /* number of significant digits */ char dec_dgtsDECSIZE; /* actual digits base 100 */ }; typedef struct decimal dec_t; Code in our appln: dec_t val1; (val1 is read from database) val = DecRetDbl(&val1) val is always found to be 0.0000 Can someone help me in reading the exact value from database. Thanks in advance.
Look at the ESQL/C demos at %INFORMIXDIR%\\\\demo\\\\esqldemo dir, this might be of some help !! Whereas INFORMIXDIR is install location of CSDK. -Shesh "LAKSHMI DEVI PALANISSAMY" <lakshmidevip@hcl.in> Sent by: ids-bounces@iiug.org 12/08/2008 15:56 Please respond to ids@iiug.org To ids@iiug.org cc Subject Poblem in accessing decimal data type in informix [13062] Our application is trying to read a decimal value from our database. Structure decimal is defined in decimal.h file in informix struct decimal { int2 dec_exp; /* exponent base 100 */ int2 dec_pos; /* sign: 1=pos, 0=neg, -1=null */ int2 dec_ndgts; /* number of significant digits */ char dec_dgtsDECSIZE; /* actual digits base 100 */ }; typedef struct decimal dec_t; Code in our appln: dec_t val1; (val1 is read from database) val = DecRetDbl(&val1) val is always found to be 0.0000 Can someone help me in reading the exact value from database. Thanks in advance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
You have to set the resolution of the dec_t variable before fetching data into it. Art On Tue, Aug 12, 2008 at 6:26 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Our application is trying to read a decimal value from our database. > > Structure decimal is defined in decimal.h file in informix > > struct decimal > { > int2 dec_exp; /* exponent base 100 */ > int2 dec_pos; /* sign: 1=pos, 0=neg, -1=null */ > int2 dec_ndgts; /* number of significant digits */ > char dec_dgtsDECSIZE; /* actual digits base 100 */ > }; > typedef struct decimal dec_t; > > Code in our appln: > > dec_t val1; (val1 is read from database) > val = DecRetDbl(&val1) > > val is always found to be 0.0000 > > Can someone help me in reading the exact value from database. > > Thanks in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
On Tue, Aug 12, 2008 at 3:26 AM, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: > Our application is trying to read a decimal value from our database. > > Structure decimal is defined in decimal.h file in informix > > struct decimal > { > int2 dec_exp; /* exponent base 100 */ > int2 dec_pos; /* sign: 1=pos, 0=neg, -1=null */ > int2 dec_ndgts; /* number of significant digits */ > char dec_dgtsDECSIZE; /* actual digits base 100 */ > }; > typedef struct decimal dec_t; > > Code in our appln: > > dec_t val1; (val1 is read from database) > val = DecRetDbl(&val1) Who wrote DecRetDbl()? TTBOMK, it is not a function provided by Informix, so it is the first place I'd look for trouble. How is val1 read from the database? Art mentioned setting the precision before fetching; I do not think it is necessary (unlike DATETIME or INTERVAL, which do generally need the qualifier set before you mess around with the conversion routines - though again, I think that FETCH sets the qualifier). [Art: how would you set a dec_t to indicate the precision to be fetched? I can't see how you could do it. The 'chkprec()' function in I4GL is non-trivial.] You can look in the source for SQLCMD (IIUG Software Archive) for dumpdec.ec and relatives to decode the data in val1. This will allow you to see whether the problem is in the FETCH or in the conversion. > val is always found to be 0.0000 > > Can someone help me in reading the exact value from database. Why bother? You're throwing the precision away by converting to double, aren't you? If you want precise results, use decimal; do not use double. You can also Google in the comp.databases.informix for emails from me (search on my name; I've used various email addresses over time) about the internals of decimals. -- 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.
We have a structure like this:
struct plate
{
int key;
int pkey;
dec_t val1;
dec_t val2;
dec_t val3;
}
These data is stored in a table called plate.
If i query the database using dbaccess, I could see the values in val1, val2
and val3 as -0.020, 0.050, -0.025 respectively.
Code in our appln:
struct plate *p;
select * into :*p from plate where key=:xx; //SQL statement
I can see these values as:
p->val1.dec_exp=0, p->val1.dec_pos=0, p->val1.dec_ndgts=1, p->val1.dec_dgts=^B
p->val2.dec_exp=0, p->val2.dec_pos=1, p->val2.dec_ndgts=1, p->val2.dec_dgts=^E
p->val3.dec_exp=0, p->val3.dec_pos=0, p->val3.dec_ndgts=2, p->val3.dec_dgts=^B2
We have a function DectRetDbl which in turn calls dectodbl
DectRetDbl(&p->val1); //always return 0.0000 instead of -0.020
double DecRetDbl(dec_t *dp)
{
double dbl;
if( dectodbl(dp, &dbl) )
dbl = HUGE_VAL;
return(dbl);
}
what is HUGE_VAL ? 0 ?
Thanks and Regards,
Gaurav
"LAKSHMI DEVI
PALANISSAMY"
<lakshmidevip@hcl To
.in> ids@iiug.org
Sent by: cc
ids-bounces@iiug.
org Subject
Re: Poblem in accessing decimal
data type in infor [13094]
13/08/2008 09:25
Please respond to
ids@iiug.org
We have a structure like this:
struct plate
{
int key;
int pkey;
dec_t val1;
dec_t val2;
dec_t val3;
}
These data is stored in a table called plate.
If i query the database using dbaccess, I could see the values in val1,
val2
and val3 as -0.020, 0.050, -0.025 respectively.
Code in our appln:
struct plate *p;
select * into :*p from plate where key=:xx; //SQL statement
I can see these values as:
p->val1.dec_exp=0, p->val1.dec_pos=0, p->val1.dec_ndgts=1,
p->val1.dec_dgts=^B
p->val2.dec_exp=0, p->val2.dec_pos=1, p->val2.dec_ndgts=1,
p->val2.dec_dgts=^E
p->val3.dec_exp=0, p->val3.dec_pos=0, p->val3.dec_ndgts=2,
p->val3.dec_dgts=^B2
We have a function DectRetDbl which in turn calls dectodbl
DectRetDbl(&p->val1); //always return 0.0000 instead of -0.020
double DecRetDbl(dec_t *dp)
{
double dbl;
if( dectodbl(dp, &dbl) )
dbl = HUGE_VAL;
return(dbl);
}
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Tue, Aug 12, 2008 at 11:41 PM, Gaurav Saxena3 <gsaxena3@in.ibm.com> wrote:
> what is HUGE_VAL ? 0 ?
It's a large number set in <math.h>
> "LAKSHMI DEVI PALANISSAMY" <lakshmidevip@hcl
>
> We have a structure like this:
> struct plate
> {
> int key;
> int pkey;
> dec_t val1;
> dec_t val2;
> dec_t val3;
> }
>
> These data is stored in a table called plate.
> If i query the database using dbaccess, I could see the values in val1,
> val2
> and val3 as -0.020, 0.050, -0.025 respectively.
>
> Code in our appln:
>
> struct plate *p;
Where does p point to?
> select * into :*p from plate where key=:xx; //SQL statement>
> I can see these values as:
> p->val1.dec_exp=0, p->val1.dec_pos=0, p->val1.dec_ndgts=1,
> p->val1.dec_dgts=^B
> p->val2.dec_exp=0, p->val2.dec_pos=1, p->val2.dec_ndgts=1,
> p->val2.dec_dgts=^E
> p->val3.dec_exp=0, p->val3.dec_pos=0, p->val3.dec_ndgts=2,
> p->val3.dec_dgts=^B2
>
> We have a function DectRetDbl which in turn calls dectodbl
>
> DectRetDbl(&p->val1); //always return 0.0000 instead of -0.020
> double DecRetDbl(dec_t *dp)
> {
> double dbl;
> if( dectodbl(dp, &dbl) )
> dbl = HUGE_VAL;
> return(dbl);
> }
On Solaris 10, using CSDK 3.50.FC1, this output is produced by this program:
Black JL: rmk plate
INFORMIXC="gcc" esql -O plate.ec -o plate
rm -f plate.[co]
Black JL: ./plate
Key = 1, PKey = 2
Val1 = -0.0200 = -0.0200
Val2 = 0.0500 = 0.0500
Val3 = -0.0250 = -0.0250
Black JL: cat plate.ec
#include <math.h>
#include <stdio.h>
$ struct plate
{
int key;
int pkey;
dec_t val1;
dec_t val2;
dec_t val3;
};
/*
These data is stored in a table called plate.
If I query the database using dbaccess, I could see the values in val1,
val2 and val3 as -0.020, 0.050, -0.025 respectively.
*/
static double DecRetDbl(dec_t *dp)
{
double dbl;
if (dectodbl(dp, &dbl))
dbl = HUGE_VAL;
return(dbl);
}
int main(int argc, char **argv)
{
$ char *dbase = "stores";
$ int xx = 1;
$ struct plate pv;
$ struct plate *p = &pv;
char v1[32], v2[32], v3[32];
double d1, d2, d3;
int rc;
if (argc > 1)
dbase = argv[1];
$ whenever error stop;
$ database :dbase;
$ create temp table plate(key integer not null, pkey integer not null,
val1 decimal(16,4), val2 decimal(16,4), val3 decimal(16,4));
$ insert into plate values(1, 2, -0.020, 0.050, -0.025);
$ select * into :*p from plate where key=:xx;
if (sqlca.sqlcode != 0)
printf("SQLCODE = %d\\
", (int)sqlca.sqlcode);
d1 = DecRetDbl(&p->val1);
d2 = DecRetDbl(&p->val2);
d3 = DecRetDbl(&p->val3);
if ((rc = dectoasc(&p->val1, v1, sizeof(v1)-1, 4)) != 0)
printf("DECTOASC val1: rc = %d\\
", rc);
v1[sizeof(v1)-1] = '\\\\0';
if ((rc = dectoasc(&p->val2, v2, sizeof(v2)-1, 4)) != 0)
printf("DECTOASC val2: rc = %d\\
", rc);
v2[sizeof(v2)-1] = '\\\\0';
if ((rc = dectoasc(&p->val3, v3, sizeof(v3)-1, 4)) != 0)
printf("DECTOASC val3: rc = %d\\
", rc);
v3[sizeof(v3)-1] = '\\\\0';
printf("Key = %d, PKey = %d\\
", p->key, p->pkey);
printf("Val1 = %10.4f = %s\\
", d1, v1);
printf("Val2 = %10.4f = %s\\
", d2, v2);
printf("Val3 = %10.4f = %s\\
", d3, v3);
return(0);
}
Black JL:
What do you get?
And please show working minimal code examples (like my program) rather
than code fragments. The trouble with code fragments is that the
important information is in the stuff that isn't shown - like where
the pointer p points to. To make a convincing bug report, you'll need
to make a reproduction like this. Please make it easy on those you
ask for help by doing your homework before asking for help.
--
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.
Hi, Thanks for your valuable comments We have decimal.h file containing DecRetDbt static double DecRetDbl(dec_t *dp) { double dbl; if (dectodbl(dp, &dbl)) dbl = HUGE_VAL; return(dbl); } The value of dbl before returning contains the double value exactly equivalent to the decimal. printf ("Val is %f\\ ", DecRetDbl(&num)); But upon coming back to the place where it is called, the value is reset to 0.0000
Lakshmi Do you mean "dbl" contains correct value "-0.02" with in function DecRecDbl() but the return value of function DecRecDbl is 0? --- On Thu, 8/14/08, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: > From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> > Subject: Re: Poblem in accessing decimal data type in infor [13103] > To: ids@iiug.org > Date: Thursday, August 14, 2008, 9:28 AM > Hi, > > Thanks for your valuable comments > > We have decimal.h file containing DecRetDbt > static double DecRetDbl(dec_t *dp) > { > double dbl; > if (dectodbl(dp, &dbl)) > dbl = HUGE_VAL; > return(dbl); > } > > The value of dbl before returning contains the double value > exactly equivalent > to the decimal. > > printf ("Val is %f\\ ", DecRetDbl(&num)); > > But upon coming back to the place where it is called, > the value is reset to 0.0000 > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in > the discussion forum.
On Thu, Aug 14, 2008 at 7:28 AM, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: > Thanks for your valuable comments > > We have decimal.h file containing DecRetDbt Given the typos here, it is crucial to see the actual code. Also, decimal.h is an ESQL/C header -- if you hide it with your own decimal.h, all hell should be breaking loose. So, you probably didn't mean quite what you said. It does depend slightly on what's in your version of decimal.h, of course, but you should not be hacking *any* header provided by software installed on your machine; it leads to unsupported and unsupportable behaviour. > static double DecRetDbl(dec_t *dp) > { > double dbl; > if (dectodbl(dp, &dbl)) > dbl = HUGE_VAL; > return(dbl); > } > > The value of dbl before returning contains the double value exactly equivalent > to the decimal. Approximately equivalent to the decimal, in general. > printf ("Val is %f\\ ", DecRetDbl(&num)); > > But upon coming back to the place where it is called, > the value is reset to 0.0000 Then the calling code is broken - and you will have to show us the minimal example code that demos the problem. It should be about 40 lines of code total and not much more. If it is significantly longer than that, then you either have a lot of explaining to do, or you will be requested to try again with smaller code. Also, it would be helpful to have the platform and version information -- I don't recall seeing it in any of the messages. It probably isn't crucial, but it is helpful. -- 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.
Hi Jonathan, There was a problem with our application where i have missed a proto in our header file. Thanks to your valuable comments also. I tried using a sample program and got it resolved. Regards, Lakshmi