Why data changed when inserting into SQLite3 database (and maybe other dbs)

Hi,

I created a table in VW7.4.1 SQLite3EXDI using SQL String:

CREATE TABLE quotesd(symbol varchar, date int, o numeric, h numeric, l numeric,
c numeric, v numeric,  a numeric);

and when  I do INSERT (note: 670636800 is seconds from 1970.1.1):

INSERT INTO quotesd (symbol, date, o, h, l, c, v, a) values ('399001.SZ'
670636800 988.05 988.05 988.05 988.05 1 1000000)

I find I get this after I SELECT:

#('399001.SZ' 670636800 988.04998779297d 988.04998779297d 988.04998779297d
988.04998779297d 1 1000000)

why have numbers changed? I've tested with several datatypes in table creating
string but not seemly solve the problem.

How to overcome this?
Thanks!

yours,

jimg

These seem to be conversion errors (going from decimal to binary and
back again).

It seems that Smalltalk and SQLite introduce slightly different errors.
I don't know what precision a 'numeric' has in SQLite but I did notice
the following:

988.04998779297d asFloat "-> 988.05"

988.05 asDouble "-> 988.05004882812d"

which (I guess) shows that these conversion errors are slightly
different in SQLite and Smalltalk....


Remember that floats and doubles are imprecise and that FP libraries are
allowed to treat them as such, each library introducing conversion and
roundoff errors in its very own way...

If you need exact numbers stay away from floats but scale your numbers
up so they stay integers (AKA fixed point arithmetic).



HTH,

Reinout
-------

jimg wrote:

Reinout Heeck <vwnclist <at> soops.nl> writes:
> ....
> If you need exact numbers stay away from floats but scale your numbers
> up so they stay integers (AKA fixed point arithmetic).
> ....

Yes, I'll try this, thanks!

Reinout Heeck <vwnclist <at> soops.nl> writes:

>
> These seem to be conversion errors (going from decimal to binary and
> back again).
> ....

Hi, Heeck! I've found the codes in SQLite3EXDI which cause the problem though
have no Idea to overcome it except for using integers instead.

It's here:
SQLite3Cursor>>bindValue: aValue at: aPosition
        | res errs pos |
        pos := aPosition.
        aPosition isString
                ifTrue:
                        [pos := self xif sqlite3_bind_parameter_index: self statement
                                                with: aPosition asString copy].
        pos > (self xif sqlite3_bind_parameter_count: self statement)
                ifTrue: [^self class connectionClass invalidDescriptorCountSignal raise].
        (res := aValue sqliteBindOn: self at: pos) = self xif SQLITE_OK
                ifFalse:
                        [errs := Array with: ((ExternalDatabaseError new)
                                                                dbmsErrorCode: res;
                                                                dbmsErrorString: self getErrorString).
                        self class connectionClass unableToBindSignal raiseWith: errs
                                errorString: errs first dbmsErrorString]

Number>>sqliteBindOn: aCursor at: position
        ^aCursor bindReal: self asDouble at: position


Here any external real type is conversed to aDouble. And these are for use in:

session prepare: SQLString;
execute;
answer

So when I insert some records to a test db using AD Hoc SQL tool the problem
dosn't show up. And I guess this tool dosn't use binding
session prepare: SQLString
to insert values.

Now I am wonder what to do to overcome this in binding.

Thanks for any idea.

Regards,

Jim G

Reinout Heeck wrote:
what I find dubious:

1.356 asFloat asDouble ==> 1.3559999465942d
Double readFrom: '1.356' readStream ==> 1.356d

On 5/21/07, Holger Kleinsorgen <[hidden email]> wrote:
The VM uses the C library's default float/double conversion (which is afaia IEEE compliant on all platforms):

$ cat >t.c <<END
int
main()
{       printf("%.10e\n", (double)1.356f); return 0; }
END
$ cc -o t t.c
t.c: In function 'main':
t.c:3: warning: incompatible implicit declaration of built-in function 'printf'
$ t
1.3559999466e+00

is what I get on Mac OS X Intel.

One issue is printing.  In C printf will round with certain precisions.  e.g.
$ cat >t.c <<END
int
main()
{       printf("%g\n", (double)1.356f); return 0; }
END
$ cc -o t t.c
t.c: In function 'main':
t.c:3: warning: incompatible implicit declaration of built-in function 'printf'
$ t
1.356