sqlite3 component can't seem to handle very large numbers

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3 component can't seem to handle very large numbers

Herman Borsje
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


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 component can't seem to handle very large numbers

Tobias Boege-2
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

--
"There's an old saying: Don't change anything... ever!" -- Mr. Monk

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user

sqlite3-long.gbs3 (511 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3 component can't seem to handle very large numbers

gambas-user mailing list
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

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user
Reply | Threaded
Open this post in threaded view
|

Re: 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,
>


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Gambas-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gambas-user