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

[Benoît Minisini]

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,

-- 
Benoît Minisini