Re: [sqlite] BCD representation of floats
John, I'm not sure what you mean. What do 64-bit SQLite integers do for me? Oracle 8 uses the BCD, and I'm trying to temporarily match that. I'm not looking for sufficient precision, I'm looking for the exact same 64 bit floating point number as from Oracle. Liam On Wed, Mar 26, 2008 at 9:25 PM, John Stanton <[EMAIL PROTECTED]> wrote: > We actually added this type of capability to Sqlite (actually fixed > point display format numbers), but it may be unnecessary in your case. > Instead of representing integers as BCD how about using the 64 bit > Sqlite integers? You may have sufficient precision. The COBOL-style > COMP3 integers are pretty much obsolete these days. > > > > Liam Healy wrote: > > I am porting a numerical application from Oracle to SQLite. For the > > most part, I have been successful, but there are slight disagreements > > in the floating point number results. I have traced this back and > > found a problem. According to > > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > > by default stores floats as binary-coded decimal (BCD), and not > > IEEE754 binary. SQLite on the other hand does > > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > > point value, stored as an 8-byte IEEE floating point number." For the > > results of the application, it makes no difference how the numbers are > > stored -- the differences in the 15th significant figure are > > irrelevant. However, I would like to insure that there no > > disagreements in the way the two applications operate (other than the > > storage of floating point numbers), and for that I temporarily need > > exact agreement on input numbers. I cannot change the Oracle > > application, so I'm wondering if there's a wrapper or something I can > > put around sqlite calls (or better, a mode that I can put sqlite in) > > that will reproduce exactly the BCD format of Oracle. > > > > Thanks for any guidance. > > > > Liam > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BCD representation of floats
Ken, Thanks for your detailed response. I thought of your remedies, but I perhaps didn't explain myself well enough. The Oracle we are using is Oracle 8, yes, ancient I know, but a fact of life. It has no binary_double type; that was introduced for Oracle 10 evidently. Second, I know there is a deviation, and I even know what it is in some cases, so a deviation check won't help me much. Following the extraction from the database, there is a sequence of complex numerical calculations. The end result is a fairly substantial discrepancy in the numerical result. Since we ported this entire piece of software, not just changing the database but other things as well, we'd like to know if it's all attributable to the database, or if there is some other source of the disagreement. It's not likely that a disagreement in the 14th significant figure from Oracle/sqlite could propagate to the 6th significant figure, but having dealt with numerics, you know this is possible. Therefore my dilemma is this: are the observed differences in output due solely to the difference from the databases, or is there something else going on? The most straightforward way to ascertain this is to make sure the numbers coming out of the database are identical. I've done this in a limited way. With some painstaking digging into gdb and dbx, I found out how to read and set the integer (hex) representations of the doubles. Where I saw some important numbers differing slightly coming out of sqlite, I manually set them to what the Oracle result was, and let the computation proceed. At the end, the results were essentially the same as the unmodified sqlite application, so I know now that that discrepancy was insignificant. However, there are other numbers coming from other tables that haven't been checked in the same way. I was seeking one blanket way of temporarily having all the numbers extracted emulate the exact form of the Oracle numbers. It's not that this is the only way to proceed, but if such a method exists, it would make things much faster. Liam On Wed, Mar 26, 2008 at 1:02 PM, Ken <[EMAIL PROTECTED]> wrote: > Liam, > > I know a great deal about oracle internals, Oracle stores "numbers" as you > indicate in a bcd format. But It can be up to 22 bytes long. But usage in > oracle proc/proc++/sql/plsql is really dependent upon your native conversion > to host datatype. > > The number storage formats betweend Sqlite and Oracle are totally different. > I don't think there is much your going to be able to do since the storage is > different, most likely the precision is also going to be different and hence > the outputs. > > Maybe you could implement a deviation check in your sqlite code. If the > number produced fromsqlite matches oracle within a range +/- some number. > Then it is considered equal. > > Also as your article implies you could modify the oracle data type to be a > binary_float or binary_double which would store the value in a "native" ieee > format instead of the NUMBER (which is more accurate). > > Another thought: Role your own NUMBER data type !!! Create a function inside > sqlite that would basically implement an oracle Number representation. This > should then produce exact match to oracle (in theory). You could use a text > field or blob field to store the number datatype. Then apply your conversion > function to it! > > HTH, > Ken > > > > Liam Healy <[EMAIL PROTECTED]> wrote: I am porting a numerical application > from Oracle to SQLite. For the > > > most part, I have been successful, but there are slight disagreements > in the floating point number results. I have traced this back and > found a problem. According to > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > by default stores floats as binary-coded decimal (BCD), and not > IEEE754 binary. SQLite on the other hand does > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > point value, stored as an 8-byte IEEE floating point number." For the > results of the application, it makes no difference how the numbers are > stored -- the differences in the 15th significant figure are > irrelevant. However, I would like to insure that there no > disagreements in the way the two applications operate (other than the > storage of floating point numbers), and for that I temporarily need > exact agreement on input numbers. I cannot change the Oracle > application, so I'm wondering if there's a wrapper or something I can > put around sqlite calls (or better, a mode that I can put sqlite in) > that will reproduce exactly the BCD format of Oracle. > > Thanks for any guidance. > > Liam > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org >
Re: [sqlite] BCD representation of floats
We actually added this type of capability to Sqlite (actually fixed point display format numbers), but it may be unnecessary in your case. Instead of representing integers as BCD how about using the 64 bit Sqlite integers? You may have sufficient precision. The COBOL-style COMP3 integers are pretty much obsolete these days. Liam Healy wrote: > I am porting a numerical application from Oracle to SQLite. For the > most part, I have been successful, but there are slight disagreements > in the floating point number results. I have traced this back and > found a problem. According to > http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle > by default stores floats as binary-coded decimal (BCD), and not > IEEE754 binary. SQLite on the other hand does > http://www.sqlite.org/datatype3.html: "REAL. The value is a floating > point value, stored as an 8-byte IEEE floating point number." For the > results of the application, it makes no difference how the numbers are > stored -- the differences in the 15th significant figure are > irrelevant. However, I would like to insure that there no > disagreements in the way the two applications operate (other than the > storage of floating point numbers), and for that I temporarily need > exact agreement on input numbers. I cannot change the Oracle > application, so I'm wondering if there's a wrapper or something I can > put around sqlite calls (or better, a mode that I can put sqlite in) > that will reproduce exactly the BCD format of Oracle. > > Thanks for any guidance. > > Liam > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BCD representation of floats
Liam, I know a great deal about oracle internals, Oracle stores "numbers" as you indicate in a bcd format. But It can be up to 22 bytes long. But usage in oracle proc/proc++/sql/plsql is really dependent upon your native conversion to host datatype. The number storage formats betweend Sqlite and Oracle are totally different. I don't think there is much your going to be able to do since the storage is different, most likely the precision is also going to be different and hence the outputs. Maybe you could implement a deviation check in your sqlite code. If the number produced fromsqlite matches oracle within a range +/- some number. Then it is considered equal. Also as your article implies you could modify the oracle data type to be a binary_float or binary_double which would store the value in a "native" ieee format instead of the NUMBER (which is more accurate). Another thought: Role your own NUMBER data type !!! Create a function inside sqlite that would basically implement an oracle Number representation. This should then produce exact match to oracle (in theory). You could use a text field or blob field to store the number datatype. Then apply your conversion function to it! HTH, Ken Liam Healy <[EMAIL PROTECTED]> wrote: I am porting a numerical application from Oracle to SQLite. For the most part, I have been successful, but there are slight disagreements in the floating point number results. I have traced this back and found a problem. According to http://articles.techrepublic.com.com/5100-22_11-5224536.html, Oracle by default stores floats as binary-coded decimal (BCD), and not IEEE754 binary. SQLite on the other hand does http://www.sqlite.org/datatype3.html: "REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number." For the results of the application, it makes no difference how the numbers are stored -- the differences in the 15th significant figure are irrelevant. However, I would like to insure that there no disagreements in the way the two applications operate (other than the storage of floating point numbers), and for that I temporarily need exact agreement on input numbers. I cannot change the Oracle application, so I'm wondering if there's a wrapper or something I can put around sqlite calls (or better, a mode that I can put sqlite in) that will reproduce exactly the BCD format of Oracle. Thanks for any guidance. Liam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
