[SOLVED] DBGrid for SQLite shows negative numbers instead of big
positive number
lazarus / 16-Databases

----------------------------------------------------------------------
Hartmut · 2016-12-26 12:37

I have a sqlite3 database which contains numbers like 3,344,740,815 or
5,744,856,294 but they are shown by DBGrid as -950,226,481 or
+1,449,888,998. Looks like that 32 bit integers are used instead of 64
bit integers. How can I change this?

When I query my DB via command-line-tool sqlite3.exe, I get the
correct big numbers, so for me my DB seems ok. But both DBGrid and
"DBGrid1.Columns[5].Field.AsLargeInt" (see line 20 and 21) return the
same negative or to small numbers. What am I doing wrong?

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);   var i: longint;   begin   try 
    SQLQuery1.Close;       DBConnection.DatabaseName:='h:\Progs\Lazaru
s\work\projekt1_SQL4\data.db';       SQLQuery1.SQL.Text:= 'select *
from traffic '         + 'where y=2016 and m=9 and recv > 1500000000
'         + 'order by 6 desc';       DBConnection.Connected:= True;  
  SQLTransaction1.Active:= True;      SQLQuery1.Open;       for i:=1
to DBGrid1.DataSource.dataset.RecordCount do         begin       
 DBGrid1.DataSource.dataset.RecNo:=i;       
 writeln(DBGrid1.Columns[5].Field.AsLargeInt);       
 writeln(DBGrid1.DataSource.DataSet.FieldByName('recv').AsLargeInt); 
      end;    except      on E: EDatabaseError do         begin      
 MessageDlg('Error','A DB error occurred: ' +
E.Message,mtError,[mbOK],0);         end;   end; {try}   end; 
I use Lazarus 1.6.2 with FPC 3.0.0 on Win7 32 bit. My sqlite3.dll is
ver 3.15.2.0 (the newest).
I attached a screenshot and the DB with a small project. Thanks in
advance.

----------------------------------------------------------------------
sky_khan · 2016-12-26 12:45

Your sent and recv columns need to be declared as "LARGEINT" or
"BIGINT" at table creation. If they are declared as "INTEGER" lazarus
recognizes them as normal 32-bit integer fields.

----------------------------------------------------------------------
Hartmut · 2016-12-26 13:03

Looks that you are right, the columns seem to be declared as
"integers". But I have no influence on that, because the DB is created
by a foreign program which is not from me, so I can't change this. I
only want to read the DB. Any other ideas?

----------------------------------------------------------------------
sky_khan · 2016-12-26 13:21

Then you have to use CAST on them at every query.
e.g
select "if","y","m","d", CAST(sent as BIGINT) as "sent", CAST(recv as
BIGINT) as "recv" from traffic

----------------------------------------------------------------------
Hartmut · 2016-12-26 13:43

That's a very good solution which works perfect for me. I didn't know
this CAST before. Thanks a lot for your valuable help!