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

Reply via email to