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
#!/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
------------------------------------------------------------------------------
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
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
