Re: ESQL/C and doubles
Peter Komanns <p.komanns@sindata.it> wrote: >HELLO All! >I've big troubles running ESQL/C programs built in LINUX when these programs >communicate with an IDN engine that is running in HP-UX environment. In this >case, values defined as SQL FLOAT (C type is double) are read from the >database cutting some bits in the HEX representation and then spoiling them. >These values are inserted with tihese bits off so that if you try to access >them using their values in a WHERE clause you can't find the related rows. > >Any hint? Don't use FLOAT or SMALLFLOAT when the precision matters? I tested your results using RedHat 5.2 (Pentium 90) and Solaris 2.6 (Sparc 20). I got essentially the same results. I modified the code a tiny bit; apart from using CONNECT instead of DATABASE since my Sparc doesn't trust my Linux box, I also added a call to my dumpsqlca() function (source obtainable from the SQLCMD code at the IIUG web site, but it requires about 6 headers as well as the function so I've not attached it). When I do that immediately after the CONNECT / DATABASE statement, I get the information: -----SQLCA----- After CONNECT sqlcode = 0 sqlerrm = '' sqlerrp = '' sqlerrd[0] = 0: (Estimated number of rows) sqlerrd[1] = 0: (ISAM error or serial number) sqlerrd[2] = 0: (Number of rows processed) sqlerrd[3] = 0: (Estimated CPU time) sqlerrd[4] = 0: (Offset of error into RDSQL statement) sqlerrd[5] = 0: (ROWID of last row) sqlwarn0 = `W': (Any warning set) sqlwarn1 = ` ': (Data item truncated (database has TX log)) sqlwarn2 = ` ': (Aggregate encountered NULL (MODE ANSI database)) sqlwarn3 = `W': (Mismatch between select-list and INTO (OnLine Engine)) sqlwarn4 = `W': (UPDATE/DELETE without where (FLOAT<->DECIMAL conversion)) sqlwarn5 = ` ': (Non-ANSI SQL) sqlwarn6 = ` ': (Data Fragment skipped (OnLine running in secondary mode)) sqlwarn7 = ` ': (Not used (DB_LOCALE does not match database locale)) ---SQLCA END--- Note that sqlwarn4 is set - so that the network traffic is being done converting FLOAT values to DECIMAL and back again. So, when the Linux end sends the values for the INSERT, the underlying ESQL/C code converts the value from a C double into the network DECIMAL notation, the Solaris end (it happens to be IDS/UDO 9.14.UC4) reads the DECIMAL value, converts it back into a double and stuffs it into the database. Then the SELECT code reverses the process; the double is pulled out of the database, converted back into a DECIMAL, sent across the wire, and then converted back from DECIMAL to double on the Linux box. Anybody who has done serious numerical analysis work knows that using doubles is prone to rounding problems. What you are showing demonstrates the trouble. The best way around the problem is probably to avoid the machine specific double notation and use DECIMAL instead. And with debug on the INSERT phase, we can see that the conversion from double to decimal on the Linux side is suspect. >Attached is a sample C program that demonstrate the error: What follows is the output of the DECIMAL equivalent of the original program, plus the source code used to obtain the output. Mild warning: don't ever post a program with 'void main(int argc, char **argv, char **envp)' anywhere near the comp.std.c news group -- they'll shred your coding style. Despite misgivings, I kept your one-space-per-level indentation, though. Yours, Jonathan Leffler (jleffler@informix.com) #include <quotes/shakespeare.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn -----SQLCA----- After CONNECT sqlcode = 0 sqlerrm = '' sqlerrp = '' sqlerrd[0] = 0: (Estimated number of rows) sqlerrd[1] = 0: (ISAM error or serial number) sqlerrd[2] = 0: (Number of rows processed) sqlerrd[3] = 0: (Estimated CPU time) sqlerrd[4] = 0: (Offset of error into RDSQL statement) sqlerrd[5] = 0: (ROWID of last row) sqlwarn0 = `W': (Any warning set) sqlwarn1 = ` ': (Data item truncated (database has TX log)) sqlwarn2 = ` ': (Aggregate encountered NULL (MODE ANSI database)) sqlwarn3 = `W': (Mismatch between select-list and INTO (OnLine Engine)) sqlwarn4 = `W': (UPDATE/DELETE without where (FLOAT<->DECIMAL conversion)) sqlwarn5 = ` ': (Non-ANSI SQL) sqlwarn6 = ` ': (Data Fragment skipped (OnLine running in secondary mode)) sqlwarn7 = ` ': (Not used (DB_LOCALE does not match database locale)) ---SQLCA END--- 1 - 100.0 9999.99999999999 99999.9999999998 2 - 100.0 9999.99999999999 99999.9999999998 3 - 200.0 19999.99999999998 199999.9999999996 4 - 600.0 59999.99999999994 599999.9999999988 5 - 2400.0 239999.99999999976 2399999.9999999952 6 - 12000.0 1199999.9999999988 11999999.999999976 7 - 72000.0 7199999.9999999928 71999999.999999856 8 - 504000.0 50399999.9999999496 503999999.999998992 9 - 4032000.0 403199999.999999597 4031999999.99999194 Line n.1 (1) 1.1 ttest1 = (100.0 ) Correct = (100.0 ) 1.2 ttest2 = (9999.99999999999 ) Correct = (9999.99999999999 ) 1.3 ttest3 = (99999.9999999998 ) Correct = (99999.9999999998 ) Line n.2 (2) 2.1 ttest1 = (100.0 ) Correct = (100.0 ) 2.2 ttest2 = (9999.99999999999 ) Correct = (9999.99999999999 ) 2.3 ttest3 = (99999.9999999998 ) Correct = (99999.9999999998 ) Line n.3 (3) 3.1 ttest1 = (200.0 ) Correct = (200.0 ) 3.2 ttest2 = (19999.99999999998 ) Correct = (19999.99999999998 ) 3.3 ttest3 = (199999.9999999996 ) Correct = (199999.9999999996 ) Line n.4 (4) 4.1 ttest1 = (600.0 ) Correct = (600.0 ) 4.2 ttest2 = (59999.99999999994 ) Correct = (59999.99999999994 ) 4.3 ttest3 = (599999.9999999988 ) Correct = (599999.9999999988 ) Line n.5 (5) 5.1 ttest1 = (2400.0 ) Correct = (2400.0 ) 5.2 ttest2 = (239999.9999999998 ) Correct = (239999.99999999976 ) 5.3 ttest3 = (2399999.999999995 ) Correct = (2399999.9999999952 ) Line n.6 (6) 6.1 ttest1 = (12000.0 ) Correct = (12000.0 ) 6.2 ttest2 = (1199999.999999999 ) Correct = (1199999.9999999988 ) 6.3 ttest3 = (11999999.99999998 ) Correct = (11999999.999999976 ) Line n.7 (7) 7.1 ttest1 = (72000.0 ) Correct = (72000.0 ) 7.2 ttest2 = (7199999.999999993 ) Correct = (7199999.9999999928 ) 7.3 ttest3 = (71999999.99999986 ) Correct = (71999999.999999856 ) Line n.8 (8) 8.1 ttest1 = (504000.0 ) Correct = (504000.0 ) 8.2 ttest2 = (50399999.99999995 ) Correct = (50399999.9999999496) 8.3 ttest3 = (503999999.999999 ) Correct = (503999999.999998992) Line n.9 (9) 9.1 ttest1 = (4032000.0 ) Correct = (4032000.0 ) 9.2 ttest2 = (403199999.9999996 ) Correct = (403199999.999999597) 9.3 ttest3 = (4