[Gambas-user] sqlite3 component can't seem to handle very large numbers

[Herman Borsje]

Thanks Benoît and Tobias!

Problem solved by changing the data-type of the fields in Sqlite from 
INTEGER to BIGINT like so:

- select * from {table} into {temptable}
- create {newtable} (with BIGINT fields)
- insert into {newtable} select * from {temptable}
- drop {temptable}

Regards,
Herman

Op 17-06-17 om 01:49 schreef Benoît Minisini via Gambas-user:
> Le 17/06/2017 à 01:31, Tobias Boege a écrit :
>> On Sat, 17 Jun 2017, Herman Borsje wrote:
>>> When I retrieve a result from a sqlite3 database which holds very large
>>> numbers in some fields, I get weird results. Up to 10 digits works 
>>> okay, but
>>> larger numbers are incorrect. Any ideas as to what's going wrong?
>>>
>>> I am using Gambas 3.9.2 on Linux Mint 18.1
>>>
>>> Tabledef: id INTEGER, name TEXT;
>>>
>>> Database records:
>>>
>>> id                         name
>>>
>>> 1234567890        test1
>>>
>>> 12345678901      test2
>>>
>>> 123456789010    test3
>>>
>>>
>>> Public Sub Button1_Click()
>>>
>>>    Dim rs As Result
>>>    Dim con As New Connection
>>>    con.Name = "test.db"
>>>    con.Type = "sqlite3"
>>>    con.Open
>>>
>>>    rs = con.Exec("select * from test")
>>>
>>>    For Each rs
>>>      Debug Cstr(rs!id) & ": " & rs!name
>>>    Next
>>>
>>>    con.Close
>>>
>>> End
>>>
>>> Debug results:
>>>
>>> FMain.Button1_Click.14: 1234567890: test1
>>> FMain.Button1_Click.14: 0: test2
>>> FMain.Button1_Click.14: 6714656: test3
>>>
>>
>> The SQLite documentation tells me that SQLite3's INTEGER datatype can
>> consist of 1, 2, 3, 4, 6 or 8 bytes, depending on the magnitude of the
>> value to be stored, whereas Gambas' normal Integer type is always four
>> bytes, or 32 bits.
>>
>> What you call "larger numbers" are most likely just numbers that cross
>> the boundaries of 32 bits. At least the two numbers you listed above,
>> where the retrieval appears to fail, have 34 and 37 bits respectively.
>>
>> In the attached script, I tried CLong() (Long is always 8 bytes in
>> Gambas), but to no avail. It seems that the faulty conversion is already
>> done in the database driver and has to be fixed there. From glancing
>> at the source code, the mapping between SQLite and Gambas datatypes is:
>>
>>        Gambas ->  SQLite3         SQLite3        -> Gambas
>>    ------------+------------ ------------------+--------------
>>       Integer  |    INT4             INTEGER,     | \
>>         Long   |   BIGINT        INT, INT4, INT2, |  |
>>                                      SMALLINT,    |  |- Integer
>>                                      MEDIUMINT    | /
>>                                    BIGINT, INT8   |     Long
>>
>> I would suggest to map INTEGER to Long instead of Integer, but Benoit,
>> being the driver author, has to confirm.
>>
>> Regards,
>> Tobi
>>
>
> SQLite fields internally do not have datatypes. You can store any 
> value in any field.
>
> So I chose INTEGER to represent 4 bytes integer, and BIGINT to 
> represent 8 bytes integer, like in MySQL.
>
> It's just a convention, but a convention was needed.
>
> Regards,
>