SQLite-DB: reads wrong values from big INTEGER columns
<h3><details><summary>Original Reporter info from Mantis: <small>Hartmut @Hartmut2</small></summary><small>
- **Reporter name:**
</small></details></h3>
## Description:
In SQLite, when you create a table via<br/>
``` CREATE TABLE demo (col1 INTEGER)```<br/>
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:<br/>
2123579006<br/>
2477462608<br/>
3304268252<br/>
4891088032<br/>
4992299380<br/>
6055641387<br/>
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}<br/>
sql_init('d:\FPC\work\einmal\SQLite_INT32_Error.sqlite'); // init SQL-DB<br/>
sql_read('SELECT col1 FROM demo'); // read data into 'DataSourceX.Dataset'
``` pascal
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:<br/>
1) 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.<br/>
2) 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:<br/>
- the number of columns<br/>
- the name of each column<br/>
- the type of each column<br/>
to create a complex SELECT statement with a CAST() for every INTEGER-column. Extremely complex and high risk to errors.<br/>
3) 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 (#1c171e1ad392280b886a00e49b3a558bcfa5e4c9)
- **Target version:** 3.2.0
issue