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

[Tobias Boege]

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
-------------- next part --------------
#!/usr/bin/gbs3

Use "gb.db"

Public Sub Main()
  Dim h As New Connection
  Dim t As Table, r As Result

  h.Type = "sqlite3"
  h.Host = Null
  h.Name = Null
  h.Open()

  h.Exec("CREATE TABLE test(int INTEGER PRIMARY KEY)")

  r = h.Create("test")
  r!int = &H9876543210 ' 5 byte integer
  r.Update()

  r = h.Find("test")
  ' Notice that the "98" hex digits are lost although SQLite3's
  ' INTEGER type can store 8 bytes.
  Print Hex$(r!int), Hex$(CStr(r!int)), Hex$(CLong(r!int))
End