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