Upload TXT file to SQLite base..
lazarus / 16-Databases

----------------------------------------------------------------------
mpknap · 2019-09-28 06:12

This is my first step with SQL databases. I want to load DATA.TXT file
into the S.DB database. I have redone the code from another post, but
I have a question why it takes so long to load 3000 records?

(The Data.TXT file is smaller because of the attachment size. It is
100x larger originally.)


The whole project is included in the ZIP attachment.

Main procedure :

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.BitBtn1Click(Sender: TObject);var  f: textfile;  time, user,
user_name, team, team_nr, lat, lon, spacja: string; begin 
assignFile(f, 'data.txt');  SQLite3Connection1.DatabaseName :=
's.db';  SQLite3Connection1.Connected := True;  SQLQuery1.SQL.Clear; 
SQLQuery1.SQL.Add(    'insert into Detections
(UnixTime,Lat,Long,User_Name,User_Index,Team_Name,Team_Index) values (
:UnixTime,:Lat,:Long,:User_Name,:User_Index,:Team_Name,:Team_Index)');
 // try  reset(f);  while not EOF(f) do  begin    readLn(f, time);   
readLn(f, lat);     readLn(f, lon);     readLn(f, user_name);   
readLn(f, user);    readLn(f, team);    readLn(f, team_nr);     
readLn(f, spacja);    
SQLQuery1.Params.paramByName('UnixTime').AsString := time;   
SQLQuery1.Params.paramByName('Lat').AsString := lat;   
SQLQuery1.Params.paramByName('Long').AsString := lon;   
SQLQuery1.Params.paramByName('User_Name').AsString := user_name;   
SQLQuery1.Params.paramByName('User_Index').AsString := user;   
SQLQuery1.Params.paramByName('Team_Name').AsString := team;   
SQLQuery1.Params.paramByName('Team_Index').AsString := team_nr;   
SQLQuery1.ExecSQL;    SQLTransAction1.Commit;   end;  closefile(f); 
SQLQuery1.Close;   begin    SQLQuery1.Close;    SQLQuery1.SQL.Text :=
'Select * FROM Detections';    SQLQuery1.ExecSQL;    SQLQuery1.Open; 
end; end;

----------------------------------------------------------------------
Zvoni · 2019-09-28 07:29

Because you‘re inserting each line individually in a loop instead of a
bulk-insert

----------------------------------------------------------------------
mpknap · 2019-09-28 19:21

And how to insert in bulk? Please piece of code

----------------------------------------------------------------------
bytebites · 2019-09-28 19:58

Move SQLTransAction1.Commit; outside loop
Use SQLQuery1.Prepare; function.

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.BitBtn1Click(Sender: TObject);var  f: textfile;  time, user,
user_name, team, team_nr, lat, lon, spacja: string; begin 
assignFile(f, 'data.txt');  SQLite3Connection1.DatabaseName :=
's.db';  SQLite3Connection1.Connected := True;  SQLQuery1.SQL.Clear; 
SQLQuery1.SQL.Add(    'insert into Detections
(UnixTime,Lat,Long,User_Name,User_Index,Team_Name,Team_Index) values (
:UnixTime,:Lat,:Long,:User_Name,:User_Index,:Team_Name,:Team_Index)');
 SQLQuery1.Prepare;     // try  reset(f);  while not EOF(f) do  begin 
  readLn(f, time);    readLn(f, lat);     readLn(f, lon);    
readLn(f, user_name);    readLn(f, user);    readLn(f, team);   
readLn(f, team_nr);      readLn(f, spacja);    
SQLQuery1.Params.paramByName('UnixTime').AsString := time;   
SQLQuery1.Params.paramByName('Lat').AsString := lat;   
SQLQuery1.Params.paramByName('Long').AsString := lon;   
SQLQuery1.Params.paramByName('User_Name').AsString := user_name;   
SQLQuery1.Params.paramByName('User_Index').AsString := user;   
SQLQuery1.Params.paramByName('Team_Name').AsString := team;   
SQLQuery1.Params.paramByName('Team_Index').AsString := team_nr;   
SQLQuery1.ExecSQL;   end;  SQLTransAction1.Commit;  closefile(f); 
SQLQuery1.Close;   begin    SQLQuery1.Close;    SQLQuery1.SQL.Text :=
'Select * FROM Detections';    SQLQuery1.ExecSQL;    SQLQuery1.Open; 
end;

----------------------------------------------------------------------
mpknap · 2019-09-29 06:39

Thank you