SQLite-DB: reads wrong values from big INTEGER columns
Original Reporter info from Mantis: Hartmut @Hartmut2
-
Reporter name:
Original Reporter info from Mantis: Hartmut @Hartmut2
- Reporter name:
Description:
In SQLite, when you create a table via
CREATE TABLE demo (col1 INTEGER)
then it is allowed (!) to store values > int32 in that column. But FPC reads them wrong. All other SQLite-Clients I know read them correct.
I found this bug in FPC 3.3.1 revision 43796 on Windows 7 (32 bit) and in FPC 3.0.4 on Ubuntu 18.04 (64 bit).
Steps to reproduce:
Compile and run the attached demo program (you have to adapt the path to my database).There is a small demo database attached with values > int32. The 7 stored values are:
2123579006
2477462608
3304268252
4891088032
4992299380
6055641387
6597582327
But the program reads instead:
2123579006
-1817504688
-990699044
596120736
697332084
1760674091
-1992352265
program SQLite_INT32_Error;
{$mode objfpc}{$H+}
uses sqlite3conn,sqldb,db;
var DBConnectionX: TSQLite3Connection;
SQLQueryX: TSQLQuery;
SQLTransactionX: TSQLTransaction;
DataSourceX: TDataSource;
procedure sql_init(fspecDB: string);
{complete initialization of the SQLite-database in file 'fspecDB'}
begin
DataSourceX:=TDataSource.Create(nil); // create all vars:
DBConnectionX:=TSQLite3Connection.Create(nil);
SQLQueryX:=TSQLQuery.Create(nil);
SQLTransactionX:=TSQLTransaction.Create(nil);
DBConnectionX.Transaction:=SQLTransactionX; // connect all vars:
SQLQueryX.Database:=DBConnectionX;
DataSourceX.Dataset:=SQLQueryX;
DBConnectionX.DatabaseName:=fspecDB; // assign filespec
end;
procedure sql_read(sql: string);
{executes SQL-command 'sql' and reads all data into 'DataSourceX.Dataset'.
For this demo all error-handling was deleted}
begin
SQLQueryX.Close;
SQLQueryX.SQL.Text:=sql; // wanted SQL-command
DBConnectionX.Connected:= True; // establish the connection to the DB
SQLTransactionX.Active:= True; // activate the transaction
SQLQueryX.Open; // open the SQL-query
SQLQueryX.Last;
end;
procedure sql_done;
{closes the DB-Connection and all vars}
begin
SQLQueryX.Close;
SQLTransactionX.Active:=False;
DBConnectionX.Connected:=False;
DataSourceX.Free;
DBConnectionX.Free;
SQLQueryX.Free;
SQLTransactionX.Free;
end;
var DS: TDataset;
i: longint;
begin {main}
sql_init('d:\FPC\work\einmal\SQLite_INT32_Error.sqlite'); // init SQL-DB
sql_read('SELECT col1 FROM demo'); // read data into 'DataSourceX.Dataset'
DS:=DataSourceX.Dataset;
for i:=0 to DS.RecordCount-1 do // show the data in Column 'col1':
begin
if i=0 then DS.First else DS.Next;
writeln(DS.Fields[0].AsLargeInt);
end;
sql_done; // closes the DB-Connection and all vars
end.
Additional information:
I know there are 2 "workarounds" but they don't help me:
- create the table via "CREATE TABLE demo (col1 BIGINT)" or "CREATE TABLE demo (col1 LARGEINT)"
- or read the data via "SELECT CAST(col1 AS BIGINT) FROM demo"
They don't help me because:
- my program is a common SQLite-Browser which I use to show foreign databases. So I have no influence how the tables were created. Most tables I have seen use INTEGER-columns and not BIGINT or LARGEINT.
- my program reads all tables via "SELECT * from tablename". If I had to use CAST(), then I had to parse and analyze each table definition, which can be very complex, to detect:
- the number of columns
- the name of each column
- the type of each column
to create a complex SELECT statement with a CAST() for every INTEGER-column. Extremely complex and high risk to errors.
- the user can input in my program an arbitrary (complex) SELECT-statement whose result is displayed. I had to tell him always to use CAST() for big INTEGER-columns, which is a) very uncomfortable and b) if he forgets it, he will see completely wrong results without any warning.
And this bug is a trap for every FPC developer to fall in. It should be fixed to avoid, that faulty FPC programs are created.
As far as I can see, FPC should treat INTEGER-columns not other then BIGINT- or LARGEINT-columns when reading them from a SQLite-DB.
I would be very happy, if this bug could be fixed not only in trunc, but in FPC 3.2.0 too. Thanks a lot!
Mantis conversion info:
- Mantis ID: 36486
- OS: Windows
- OS Build: Windows 7 32 bit
- Build: 43796
- Platform: i386
- Version: 3.3.1
- Fixed in version: 3.3.1
- Fixed in revision: 43802 (#1c171e1a)
- Target version: 3.2.0