deccmp return value differs between 2 informix ver

Hi, I am working in RHEL 5.3 with IDS 11.5 We have a piece of code to check the decimal values (greater, lesser or eqaul) We are using a function called “deccmp” to do the decimal comparison When tried to compare the 2 decimal values (3.000, 3.000), the return value is found to be 1 instead of 0 With the same piece of code, in INFORMIX ONLINE version 5.0, the return value is found to be 0. Any idea for the diffence in behavior between the 2 informix versions? Thanks in advance! Here is the fragment of code: int DecGT(dec_t *d1, dec_t *d2) /*return (1) if d1 > d2, return(0) if not*/ { int cmp = deccmp(d1,d2); int ret; DecRetDbl(d1),DecRetDbl(d2)); if( cmp == DECUNKNOWN ) { print_dbg(0, "DecGT(bad decimal number)=0\\ " ); return(0); } print_dbg (0, "*****value of cmp is %d\\ ", cmp); ret = (cmp > 0); print_dbg(0, "****DecGT(%lf,%lf)=%d\\ ",DecRetDbl(d1),DecRetDbl(d2),ret ); return ret; }

Hi Lakshmi Without your complete program. I am not able to see what this DecRetDbl() do. I used a demo program below and deccmp() worked in latest csdk. Would you please try it on your system? /* * deccmp.ec * The following program compares DECIMAL numbers and displays the results. */ #include <stdio.h> EXEC SQL include decimal; char string1[] = "3.000"; char string2[] = "3.000"; main() { mint x; dec_t num1, num2; printf("DECCOPY Sample ESQL Program running.\\ \\ "); if (x = deccvasc(string1, strlen(string1), &num1)) { printf("Error %d in converting string1 to DECIMAL\\ ", x); exit(1); } if (x = deccvasc(string2, strlen(string2), &num2)) { printf("Error %d in converting string2 to DECIMAL\\ ", x); exit(1); } printf("Number 1 = %s\\\\tNumber 2 = %s\\ ", string1, string2); printf("\\ Executing: deccmp(&num1, &num2)\\ "); printf(" Result = %d\\ ", deccmp(&num1, &num2)); printf("\\ DECCMP Sample Program over.\\ \\ "); exit(0); } ========output=========== ptang@bia 1022: a.out DECCOPY Sample ESQL Program running. Number 1 = 3.000 Number 2 = 3.000 Executing: deccmp(&num1, &num2) Result = 0 DECCMP Sample Program over. =========================== --- On Wed, 7/8/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: deccmp return value differs between 2 informix ver [16253] To: ids@iiug.org Date: Wednesday, July 8, 2009, 12:24 AM Hi, I am working in RHEL 5.3 with IDS 11.5 We have a piece of code to check the decimal values (greater, lesser or eqaul) We are using a function called deccmp to do the decimal comparison When tried to compare the 2 decimal values (3.000, 3.000), the return value is found to be 1 instead of 0 With the same piece of code, in INFORMIX ONLINE version 5.0, the return value is found to be 0. Any idea for the diffence in behavior between the 2 informix versions? Thanks in advance! Here is the fragment of code: int DecGT(dec_t *d1, dec_t *d2) /*return (1) if d1 > d2, return(0) if not*/ { int cmp = deccmp(d1,d2); int ret; DecRetDbl(d1),DecRetDbl(d2)); if( cmp == DECUNKNOWN ) { print_dbg(0, "DecGT(bad decimal number)=0\\ " ); return(0); } print_dbg (0, "*****value of cmp is %d\\ ", cmp); ret = (cmp > 0); print_dbg(0, "****DecGT(%lf,%lf)=%d\\ ",DecRetDbl(d1),DecRetDbl(d2),ret ); return ret; } ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping, Thanks for your response. I tried your sample program in my system and it is working fine. The return value is 0 only. The function DecRetDbl is as follows: Double DecRetDbl(dec_t *dp) /* return the double value equal to the decimal data type. return(HUGE_VAL) if the conversion fails */ { double dbl; if( dectodbl(dp, &dbl) ) dbl = HUGE_VAL; return(dbl); } To display the decimal number, we are converting the decimal into double I tried to print the double values with 10 precisions and I have the following de-bugging statements from the code: 090709132537 *****DecGT - 2 decimal no's are (0.5650000000,0.5650000000) 090709132537 *****value of cmp is 0 090709132537 ****The value of ret is 0 and cmp is 0 090709132537 ****DecGT(0.5650000000,0.5650000000)=0 090709132537 *****DecGT - 2 decimal no's are (0.3000000000,0.3000000000) 090709132537 *****value of cmp is 1 090709132537 ****The value of ret is 1 and cmp is 1 090709132537 ****DecGT(0.3000000000,0.3000000000)=1 For the same values, in one case the deccmp returns 0 and in other case it returns 1. Regards, Lakshmi

Converting the DECIMALs to double for printing is a problem. Double is an imprecise binary format while DECIMAL is precise. Also, double has a resolution of only 14.5 digits while DECIMAL precision is up to 32 decimal digits (depending only on the definition of the precision of the column/variable). You should use the ESQL/C library functions to convert the DECIMALs to strings for printing. Art 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 Thu, Jul 9, 2009 at 5:52 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi Ping, > > Thanks for your response. > > I tried your sample program in my system and it is working fine. The return > value is 0 only. > > The function DecRetDbl is as follows: > Double DecRetDbl(dec_t *dp) > /* return the double value equal to the decimal data type. > return(HUGE_VAL) if the conversion fails */ > { > > double dbl; > > if( dectodbl(dp, &dbl) ) dbl = HUGE_VAL; > > return(dbl); > } > > To display the decimal number, we are converting the decimal into double > > I tried to print the double values with 10 precisions and I have the > following > de-bugging statements from the code: > 090709132537 *****DecGT - 2 decimal no's are (0.5650000000,0.5650000000) > 090709132537 *****value of cmp is 0 > 090709132537 ****The value of ret is 0 and cmp is 0 > 090709132537 ****DecGT(0.5650000000,0.5650000000)=0 > > 090709132537 *****DecGT - 2 decimal no's are (0.3000000000,0.3000000000) > 090709132537 *****value of cmp is 1 > 090709132537 ****The value of ret is 1 and cmp is 1 > 090709132537 ****DecGT(0.3000000000,0.3000000000)=1 > > For the same values, in one case the deccmp returns 0 and in other case it > returns 1. > > Regards, > Lakshmi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b0d41b2144046e465fa7

Hi Lakshmi As Art pointed out, your input values (decimal datatype) may not be the same even if they looks the same after being converted to double type. You may use dectoasc() to convert your decimal value to ascii and then print them. Regards, -Ping --- On Thu, 7/9/09, Art Kagel <art.kagel@gmail.com> wrote: From: Art Kagel <art.kagel@gmail.com> Subject: Re: deccmp return value differs between 2 informix [16282] To: ids@iiug.org Date: Thursday, July 9, 2009, 9:07 AM Converting the DECIMALs to double for printing is a problem. Double is an imprecise binary format while DECIMAL is precise. Also, double has a resolution of only 14.5 digits while DECIMAL precision is up to 32 decimal digits (depending only on the definition of the precision of the column/variable). You should use the ESQL/C library functions to convert the DECIMALs to strings for printing. Art 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 Thu, Jul 9, 2009 at 5:52 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi Ping, > > Thanks for your response. > > I tried your sample program in my system and it is working fine. The return > value is 0 only. > > The function DecRetDbl is as follows: > Double DecRetDbl(dec_t *dp) > /* return the double value equal to the decimal data type. > return(HUGE_VAL) if the conversion fails */ > { > > double dbl; > > if( dectodbl(dp, &dbl) ) dbl = HUGE_VAL; > > return(dbl); > } > > To display the decimal number, we are converting the decimal into double > > I tried to print the double values with 10 precisions and I have the > following > de-bugging statements from the code: > 090709132537 *****DecGT - 2 decimal no's are (0.5650000000,0.5650000000) > 090709132537 *****value of cmp is 0 > 090709132537 ****The value of ret is 0 and cmp is 0 > 090709132537 ****DecGT(0.5650000000,0.5650000000)=0 > > 090709132537 *****DecGT - 2 decimal no's are (0.3000000000,0.3000000000) > 090709132537 *****value of cmp is 1 > 090709132537 ****The value of ret is 1 and cmp is 1 > 090709132537 ****DecGT(0.3000000000,0.3000000000)=1 > > For the same values, in one case the deccmp returns 0 and in other case it > returns 1. > > Regards, > Lakshmi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b0d41b2144046e465fa7 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping / Art, Thanks for your response! I tried printing the values in String format using the "dectoasc" function. Eventhough the decimal to ascii function reports a value of 0.300, during the decimal comparison, one value is reported as greater than the other. I have attached the debug messages in 2 scenarios: Scenario 1: ####### printing in string format - value1 is -0.0020000000 #### ####### printing in string format - value2 is 0.2400000000 #### ####### printing in string format - (value1/value2) is - 0.0083333333 #### decround(-0.0083333333,3)=-0.0080000000 *****deccmp(-0.0080000000,0.3000000000) = -1 Scenario 2: ####### printing in string format - value1 is 0.0720000000 #### ####### printing in string format - value2 is 0.2400000000 #### ####### printing in string format - (value1/value2) is -0.3000000000 #### decround(0.3000000000,3)=0.3000000000 *****deccmp(0.3000000000,0.3000000000) = 1 In both the cases, the resultant value (value1/value2) is compared with the value stored in the database. When i tried to retrieve the value from database, it is "0.300". I tried printing the decimal numbers with the function "decfcvt" and found the below: Scenario 1: value1: Output of decfcvt: -2 . decpt: -2 sign: 1 value2: Output of decfcvt: +.240 decpt: 0 sign: 0 value1/value2: Output of decfcvt: -8 . decpt: -2 sign: 1 decround(-0.0083333333,3)=-0.0080000000 *****deccmp(-0.0080000000,0.3000000000) = -1 Scenario 2: value1: Output of decfcvt: +72.072 decpt: -1 sign: 0 value2: Output of decfcvt: +.240 decpt: 0 sign: 0 value1/value2: Output of decfcvt: +.300 decpt: 0 sign: 0 DecRound(0.3000000000,3)=0.3000000000 *****deccmp(0.3000000000,0.3000000000) = 1 I still could not infer the reasons to report the value of 3.000 is greater than the same value even though i rouneded it off before doing the comparison? Regards, Lakshmi

Hi Lakshmi Would you provide a simplified program which can show us how you insert and retrieve data from your database. What is your table's schema look like? I wrote a sample as below which inserted a dec value to a database Then the value was selected back and put into a decimal host variable. Would you run it under your env? ============demo.ec=============== #include <stdio.h> EXEC SQL include decimal; char string1[] = "0.300000"; main() { mint x; dec_t num1; EXEC SQL BEGIN DECLARE SECTION; decimal(12,6) num2, num2_sel; EXEC SQL END DECLARE SECTION; printf("Sample ESQL Program running.\\ \\ "); if (x = deccvasc(string1, strlen(string1), &num1)) { printf("Error %d in converting string1 to DECIMAL\\ ", x); exit(1); } if (x = deccvasc(string1, strlen(string1), &num2)) { printf("Error %d in converting string1 to DECIMAL\\ ", x); exit(1); } printf("Number 1 = %s\\ ", string1); EXEC SQL connect to 'mydemodb'; EXEC SQL begin work; EXEC SQL drop table tab1; EXEC SQL create table tab1(col1 dec(12,6)); EXEC SQL insert into tab1 values(:num2); EXEC SQL commit work; EXEC SQL select col1 into :num2_sel from tab1; EXEC SQL disconnect current; printf("\\ Executing: deccmp(&num1, &num2)\\ "); printf(" Result = %d\\ ", deccmp(&num1, &num2)); printf("\\ Executing: deccmp(&num1, &num2_sel)\\ "); printf(" Result = %d\\ ", deccmp(&num1, &num2_sel)); printf("\\ DECCM Sample Proigram over.\\ \\ "); exit(0); } ==========end============ ========output======== ptang@bia 302: a.out Sample ESQL Program running. Number 1 = 0.300000 Executing: deccmp(&num1, &num2) Result = 0 Executing: deccmp(&num1, &num2_sel) Result = 0 DECCM Sample Proigram over. ====================================== --- On Mon, 7/13/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: Re: deccmp return value differs between 2 informix [16352] To: ids@iiug.org Date: Monday, July 13, 2009, 5:16 AM Hi Ping / Art, Thanks for your response! I tried printing the values in String format using the "dectoasc" function. Eventhough the decimal to ascii function reports a value of 0.300, during the decimal comparison, one value is reported as greater than the other. I have attached the debug messages in 2 scenarios: Scenario 1: ####### printing in string format - value1 is -0.0020000000 #### ####### printing in string format - value2 is 0.2400000000 #### ####### printing in string format - (value1/value2) is - 0.0083333333 #### decround(-0.0083333333,3)=-0.0080000000 *****deccmp(-0.0080000000,0.3000000000) = -1 Scenario 2: ####### printing in string format - value1 is 0.0720000000 #### ####### printing in string format - value2 is 0.2400000000 #### ####### printing in string format - (value1/value2) is -0.3000000000 #### decround(0.3000000000,3)=0.3000000000 *****deccmp(0.3000000000,0.3000000000) = 1 In both the cases, the resultant value (value1/value2) is compared with the value stored in the database. When i tried to retrieve the value from database, it is "0.300". I tried printing the decimal numbers with the function "decfcvt" and found the below: Scenario 1: value1: Output of decfcvt: -2 . decpt: -2 sign: 1 value2: Output of decfcvt: +.240 decpt: 0 sign: 0 value1/value2: Output of decfcvt: -8 . decpt: -2 sign: 1 decround(-0.0083333333,3)=-0.0080000000 *****deccmp(-0.0080000000,0.3000000000) = -1 Scenario 2: value1: Output of decfcvt: +72.072 decpt: -1 sign: 0 value2: Output of decfcvt: +.240 decpt: 0 sign: 0 value1/value2: Output of decfcvt: +.300 decpt: 0 sign: 0 DecRound(0.3000000000,3)=0.3000000000 *****deccmp(0.3000000000,0.3000000000) = 1 I still could not infer the reasons to report the value of 3.000 is greater than the same value even though i rouneded it off before doing the comparison? Regards, Lakshmi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping, I have tried your sample program and found the output to be same as yours. I am creating a new stub similar to our code to simulate the issue. I will post it once i am done with it. Thanks, Lakshmi

Hi Ping, I have written a stub similar to our application to reproduce the issue. Find the sample program: #include <stdio.h> #include <string.h> #include <stdlib.h> EXEC SQL include decimal; main() { int x; char result1[50]; double highod; #define END1 sizeof(result1) EXEC SQL BEGIN DECLARE SECTION; dec_t val1, high_od, dtemp; char value1[50]; EXEC SQL END DECLARE SECTION; EXEC SQL connect to 'oas'; EXEC SQL begin work; EXEC SQL drop table db_assay; EXEC SQL create table db_assay(assay_key integer, var_value char(50)); EXEC SQL insert into db_assay values ('1', "0.300"); EXEC SQL commit work; printf ("\\ ~~~~~~~~high_od value from database~~~~~~~\\ "); /******* retrieving the value from database *******/ EXEC SQL select var_value into :value1 from db_assay where assay_key=1; value1[END1-1]='\\\\0'; printf("High OD from database is %s\\ ", value1); /**** converting the value to double **********/ highod = atof(value1); printf("High OD(in double) is %lf\\ ", highod); /**** convering the value to decimal *********/ if(x = deccvdbl(highod,&high_od)) { printf("Error in converting to decimal from double\\ "); exit(1); } if (x = dectoasc(&high_od, result1, sizeof(result1), 20)) { printf("Error %d in converting result to string\\ ", x); exit(1); } result1[END1-1] = '\\\\0'; printf("high-od value(in decimal) is %s\\ ", result1); printf ("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\\ "); /**** assigning a value of 0.300000000 to a string *****/ char str1[] = "0.30000000000000000"; /**** converting the value to decimal *****/ if (x = deccvasc(str1, strlen(str1), &val1)) { printf("Error %d in converting string1 to DECIMAL\\ ", x); exit(1); } if (x = dectoasc(&val1, result1, sizeof(result1), 20)) { printf("Error %d in converting result to string\\ ", x); exit(1); } result1[END1-1] = '\\\\0'; printf("value in decimal is %s\\ ", result1); /**** difference between the value from database and 0.30000000 *****/ if (x = decsub(&high_od, &val1, &dtemp)) { printf("Error %d in subtracting decimals\\ ", x); exit(1); } if (x = dectoasc(&dtemp, result1, sizeof(result1), 20)) { printf("Error %d in converting result to string\\ ", x); exit(1); } result1[END1-1] = '\\\\0'; printf("diff. is %s\\ ", result1); printf("size of decimal is %d, double is %d\\ ", sizeof(val1), sizeof(highod)); EXEC SQL disconnect current; exit(0); } Output: ~~~~~~~~high_od value from database~~~~~~~ High OD from database is 0.300 High OD(in double) is 0.300000 high-od value(in decimal) is 0.29999999999999999000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ value in decimal is 0.30000000000000000000 diff. is -0.00000000000000001000 size of decimal is 22, double is 8 We observe from the above stub output that a value of 0.300 stored in database, upon converting to double and then to decimal has a value of 0.29999999999999999000 We are not sure as why it is so. We suspect whether the precision is affected when we convert from decimal to double. We are using IDS version 11.5 for database and RHEL version 5.3 for OS Is the difference due to informix versions? Thanks in advance. Regards, Lakshmi

Hi, I have made a small modification in the stub posted earlier, so that it displays double value after atof() with a precision 20 decimal digits. The value is displayed as: 0.29999999999999998890 Hence, we have narrowed down the problem with atof() function. We have a query that whether we can use "deccvasc" instead of using "atof" and "deccvdbl"? Will there be any issues in precision, if we use "deccvasc" function? Does this function give a precision of 6 decimal digits? Thanks Lakshmi

Hi Lakshmi System function atof() may cause rounded value. When a number is represented in some format (such as a character string) which is not a native floating-point representation supported in a computer implementation, then it will require a conversion before it can be used in that implementation. Basically, 0.3 was stored as 0.299999999999999989 in double type. Hence the deccmp(0.3, 0.299999999999999989) returned value 1. In your case, if you want to convert string to decimal, you should use informix esqlc function deccvasc() which support uo to 32 decimal digits. Regards, -Ping --- On Wed, 7/15/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: Re: deccmp return value differs between 2 informix [16405] To: ids@iiug.org Date: Wednesday, July 15, 2009, 8:01 AM Hi, I have made a small modification in the stub posted earlier, so that it displays double value after atof() with a precision 20 decimal digits. The value is displayed as: 0.29999999999999998890 Hence, we have narrowed down the problem with atof() function. We have a query that whether we can use "deccvasc" instead of using "atof" and "deccvdbl"? Will there be any issues in precision, if we use "deccvasc" function? Does this function give a precision of 6 decimal digits? Thanks Lakshmi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping, Thanks for your response. I have executed the same stub in SCO platform with Informix-Online 5.0 as database. The output is found to be: ~~~~~~~~high_od value from database~~~~~~~ High OD from database is 0.300 High OD(in double) is 0.29999999999999999000 high-od value(in decimal) is 0.30000000000000000000 ------- value in decimal is 0.30000000000000000000 diff. is 0.00000000000000000000 size of decimal is 22, double is 8 The difference lies in the conversion of double to decimal. In IDS 11.5, when converting the double value "0.29999999999999998890" to decimal, a value of "0.29999999999999999000" is returned In Informix 5.0, when converting the double value "0.29999999999999999000" to decimal, a value of "0.30000000000000000000" is returned Is this difference is due to the informix versions or Operating Systems? Thanks, Lakshmi

Hi Lakshmi Generally speaking, precision rounding for double/float certainly depends on OS/hardware. However, in the case we are discussing, the rounding was caused by IDS client version changes. Why did I refer to CSDK version? The ESQLC program is using functions from CSDK library only, such as deccvasc(), deccvdbl, etc The behavior change is due to IDS improvement in precision. Since IDS9, Decimal precision for FLOAT and SMALLFLOAT conversions to DECIMAL data type has been increased from 8 (SMALLFLOAT) and 16 (FLOAT) to 9 and 17, respectively. If you would like to revert back to client behavior, you may set the client ENV to 1 " IFX_USE_PREC_16 " Please try it and let me know the results. Regards, -Ping --- On Thu, 7/16/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: Re: deccmp return value differs between 2 informix [16416] To: ids@iiug.org Date: Thursday, July 16, 2009, 3:16 AM Hi Ping, Thanks for your response. I have executed the same stub in SCO platform with Informix-Online 5.0 as database. The output is found to be: ~~~~~~~~high_od value from database~~~~~~~ High OD from database is 0.300 High OD(in double) is 0.29999999999999999000 high-od value(in decimal) is 0.30000000000000000000 ------- value in decimal is 0.30000000000000000000 diff. is 0.00000000000000000000 size of decimal is 22, double is 8 The difference lies in the conversion of double to decimal. In IDS 11.5, when converting the double value "0.29999999999999998890" to decimal, a value of "0.29999999999999999000" is returned In Informix 5.0, when converting the double value "0.29999999999999999000" to decimal, a value of "0.30000000000000000000" is returned Is this difference is due to the informix versions or Operating Systems? Thanks, Lakshmi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On Wed, Jul 15, 2009 at 06:01, LAKSHMI DEVI PALANISSAMY<lakshmidevip@hcl.in> wrote: > I have made a small modification in the stub posted earlier, so that it > displays double value after atof() with a precision 20 decimal digits. The > value is displayed as: 0.29999999999999998890 > > Hence, we have narrowed down the problem with atof() function. We have a query > that whether we can use "deccvasc" instead of using "atof" and "deccvdbl"? > > Will there be any issues in precision, if we use "deccvasc" function? Does > this function give a precision of 6 decimal digits? Ultimately, the problem is that a binary floating point value such as C double (SQL FLOAT) cannot represent the decimal value 0.3 exactly. Normally, systems recommend using treating a double as holding up to 16 decimal digits worth of data. An Informix DECIMAL can hold the decimal value 0.3 exactly, of course. Any conversion between double and decimal is going to lead to some imprecision along the lines you've seen. Using deccvasc() to format the data is reliable and sensible. It takes the decimal representation and formats it exactly. With practice, you can see the mapping between the representation of decimal in the structure and the string notation - it takes a little practice, but is not dreadfully hard. So, if your data is in DECIMAL, then it is sensible to use deccvasc() to do the formatting because it does not run through the less precise C double type. -- 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

Hi Ping, I have set the environment variable IFX_USE_PREC_16 and checked the output of the stub. It works fine as intened. The resultant decimal value is 0.3 instead of 0.299... In the decimal.h header file, i have found the below: * precision 15 and 8 -> for Linux * precision 17 and 9 -> Default for all the other platforms. * precision 16 and 8 -> For clients when IFX_USE_PREC_16 is set. * precision 15 and 8 -> Which is supported by all platforms when Since we are using the Red Hat Enterprise Linux, isn't the double to decimal precision is 15 and 8? Instead its using the default 17 and 8. Kindly clarify my understanding. Regards, Lakshmi

Hi Lakshimi For client application, please search header files under $INFORMIXDIR/incl/esql instead of $INFORMIXDIR/incl/public. The file you are referring to is not for csdk. Regards, -Ping --- On Fri, 7/17/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: Re: deccmp return value differs between 2 informix [16431] To: ids@iiug.org Date: Friday, July 17, 2009, 7:50 AM Hi Ping, I have set the environment variable IFX_USE_PREC_16 and checked the output of the stub. It works fine as intened. The resultant decimal value is 0.3 instead of 0.299... In the decimal.h header file, i have found the below: * precision 15 and 8 -> for Linux * precision 17 and 9 -> Default for all the other platforms. * precision 16 and 8 -> For clients when IFX_USE_PREC_16 is set. * precision 15 and 8 -> Which is supported by all platforms when Since we are using the Red Hat Enterprise Linux, isn't the double to decimal precision is 15 and 8? Instead its using the default 17 and 8. Kindly clarify my understanding. Regards, Lakshmi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping, Yes. I have set in the client environment and found to be working fine. Also i have another doubt too. I found the below, where the decimal size is given 16. #define DECSIZE 16 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_dgts[DECSIZE]; /* actual digits base 100 */ }; Is it applicable to all decimal digits? Thanks, Lakshmi

That's the storage structure that's used in ESQL/C and 4GL applications to hold the results of retrieving a DECIMAL or MONEY column. It is a fixed size regardless of the resolution of the actual value or the column that held the value. Only the dec_ndgts number of base 100 digits are actually interpreted when the value in the structure is used. Art 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, Jul 21, 2009 at 8:46 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi Ping, > > Yes. I have set in the client environment and found to be working fine. > > Also i have another doubt too. I found the below, where the decimal size is > given 16. > #define DECSIZE 16 > 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_dgts[DECSIZE]; /* actual digits base 100 */ > > }; > > Is it applicable to all decimal digits? > Thanks, > Lakshmi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a758816b5f046f3705ea

Hi Lakshmi "dec_dgts[]" is a character array that holds the significant digits of the normalized decimal type number. Each byte in the array contains the next significant base-100 digit in the decimal type number. Basically, each byte can hold up to two digits and the SECSIZE=16. So decimal datatype can hold up to 2x16 = 32 digits. You could find more details about decimal structure from IBM Informix ESQL/C Programmer's Manual. Regards, -Ping --- On Tue, 7/21/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> Subject: Re: deccmp return value differs between 2 informix [16465] To: ids@iiug.org Date: Tuesday, July 21, 2009, 7:46 AM Hi Ping, Yes. I have set in the client environment and found to be working fine. Also i have another doubt too. I found the below, where the decimal size is given 16. #define DECSIZE 16 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_dgts[DECSIZE]; /* actual digits base 100 */ }; Is it applicable to all decimal digits? Thanks, Lakshmi ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Ping, I have referred the ESQL/C manual and understood the decimal strucure and the way it stores the exponent and base parts. 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. ======================================================== 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); EXEC SQL commit work; EXEC SQL select min_value into :min1 from min_max where dkey=1; printf ("Value1 is %f\\ ", min1); EXEC SQL select min_value into :min2 from min_max where dkey=2; printf ("Value2 is %f\\ ", min2); EXEC SQL select min_value into :min3 from min_max where dkey=3; printf ("Value3 is %f\\ ", min3); EXEC SQL select min_value into :min4 from min_max where dkey=4; printf ("Value4 is %f\\ ", min4); printf ("Float values assigned are %f %f %f %f\\ ", 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 precison(more than 9)? Can i ignore this difference? Thanks, Lakshmi

For DBACCESS, there is an environment variable that controls the output resolution. Keep in mind that FLOAT and SMALLFLOAT are binary IEEE floating point representations which are not exact. When you store a value like 0.120 the binary representation is likely closer to the 0.119999 that is being displayed by DBACCESS, but it is not rounding up like C's printf is. 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 Wed, Jul 22, 2009 at 6:26 AM, LAKSHMI DEVI PALANISSAMY < lakshmidevip@hcl.in> wrote: > Hi Ping, > > I have referred the ESQL/C manual and understood the decimal strucure and > the > way it stores the exponent and base parts. > > 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. > > ======================================================== > 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); > > EXEC SQL commit work; > > EXEC SQL select min_value into :min1 from min_max where dkey=1; > printf ("Value1 is %f\\ ", min1); > > EXEC SQL select min_value into :min2 from min_max where dkey=2; > printf ("Value2 is %f\\ ", min2); > > EXEC SQL select min_value into :min3 from min_max where dkey=3; > printf ("Value3 is %f\\ ", min3); > > EXEC SQL select min_value into :min4 from min_max where dkey=4; > printf ("Value4 is %f\\ ", min4); > > printf ("Float values assigned are %f %f %f %f\\ ", 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 precison(more than 9)? > Can i ignore this difference? > > Thanks, > Lakshmi > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5b352a1217f046f4b9267

Hi Lakshmi Yes, you can ignore this difference. As we discussed in previous emails, float is a binary format reprentation of a number. So dbaccess will do some rounding depending on the precision and the field width for display purpose. The default display field length in dbaccess for smallfloat is 14 hence you see the difference. You could set an environment variable DBSMFLTMASK to 6 before launch your dbaccess. This env will adjust dbaccess display field for Smallfloat to a number of significant digits you defined. setenv DBSMFLTMASK 6 ====Dbaccess output with DBSMFLTMASK set to 6================== dkey min_value 1 0.12 2 0.005 3 1.804000 4 0.3 ================================================================ Regards, -Ping --- On Wed, 7/22/09, LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> wrote: > From: LAKSHMI DEVI PALANISSAMY <lakshmidevip@hcl.in> > Subject: Re: deccmp return value differs between 2 informix [16474] > To: ids@iiug.org > Date: Wednesday, July 22, 2009, 5:26 AM > Hi Ping, > > I have referred the ESQL/C manual and understood the > decimal strucure and the > way it stores the exponent and base parts. > > 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. > > ======================================================== > 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); > > EXEC SQL commit work; > > EXEC SQL select min_value into :min1 from min_max where > dkey=1; > printf ("Value1 is %f\\ ", min1); > > EXEC SQL select min_value into :min2 from min_max where > dkey=2; > printf ("Value2 is %f\\ ", min2); > > EXEC SQL select min_value into :min3 from min_max where > dkey=3; > printf ("Value3 is %f\\ ", min3); > > EXEC SQL select min_value into :min4 from min_max where > dkey=4; > printf ("Value4 is %f\\ ", min4); > > printf ("Float values assigned are %f %f %f %f\\ ", 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 precison(more than > 9)? > Can i ignore this difference? > > Thanks, > Lakshmi > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > >

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\\ ", 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\\ ", min1); printf ("Value1 is %.4f\\ ", min1); printf ("Value1 is %.5f\\ ", min1); printf ("Value1 is %.6f\\ ", min1); printf ("Value1 is %.7f\\ ", min1); printf ("Value1 is %.8f\\ ", min1); printf ("Value1 is %.9f\\ ", 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\\ ", min2); > > EXEC SQL select min_value into :min3 from min_max where dkey=3; > printf ("Value3 is %f\\ ", min3); > > EXEC SQL select min_value into :min4 from min_max where dkey=4; > printf ("Value4 is %f\\ ", min4); > > printf ("Float values assigned are %f %f %f %f\\ ", 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

Hi Ping, Art, Jonathan, Thanks for spending time in clarifying the poblems! :-) Regards, Lakshmi