[SOLVED] SQLite raised exception class 'External: SIGSEGV' lazarus / 16-Databases ---------------------------------------------------------------------- dodgebros · 2023-10-18 18:49 I get this error (see attached jpg) everytime I try to run this code from a button on a form.  Any idea what I am doing wrong? Funny thing is I use the exact same code in another project and it works ok. Line 73 in the code is this line: 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 DataModule1.SQLQuery1.SQL.Text := strSQL; 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          strSQL := 'INSERT into powerball (date_drawn,wb1,wb2,wb3,wb4,wb5,pwrball) VALUES (:date_drawn,:wb1,:wb2,:wb3,:wb4,:wb5,:pwrball)';         DataModule1.SQLQuery1.SQL.Text := strSQL;        DataModule1.SQLQuery1.Params.paramByName('date_drawn').AsString := arrList[0];        DataModule1.SQLQuery1.Params.paramByName('wb1').AsString:= arrNumbers[0];        DataModule1.SQLQuery1.Params.paramByName('wb2').AsString := arrNumbers[1];        DataModule1.SQLQuery1.Params.paramByName('wb3').AsString := arrNumbers[2];        DataModule1.SQLQuery1.Params.paramByName('wb4').AsString := arrNumbers[3];        DataModule1.SQLQuery1.Params.paramByName('wb5').AsString := arrNumbers[4];        DataModule1.SQLQuery1.Params.paramByName('pwrball').AsString := arrNumbers[5];        DataModule1.SQLQuery1.ExecSQL;        DataModule1.SQLTransaction1.Commit; ---------------------------------------------------------------------- rvk · 2023-10-18 19:00 The instance of DataModule1.SQLQuery1 isn't created yet when you execute this code. So what is DataModule1? Is it created yet? What is DataModule1.SQLQuery1? How and when is that created? ---------------------------------------------------------------------- dodgebros · 2023-10-19 02:37 Thanks rvk for helping! So what is DataModule1? Is it created yet? What is DataModule1.SQLQuery1? How and when is that created? See attached screen captures and the code below to hopefully answer these questions. 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 unit dm; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, SQLDB, DB, SQLite3Conn; type   { TDataModule1 }   TDataModule1 = class(TDataModule)    DataSource1: TDataSource;    SQLite3Connection1: TSQLite3Connection;    SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;    procedure DataModuleCreate(Sender: TObject);  private   public   end; var  DataModule1: TDataModule1; implementation {$R *.lfm} { TDataModule1 } procedure TDataModule1.DataModuleCreate(Sender: TObject);var  strDbName: string;begin  // First assemble the complete databasename, including path     strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'data.sqlite3';     DataModule1.SQLite3Connection1.DatabaseName := strDbName;   // Open database  if DataModule1.SQLite3Connection1.Connected = true then     begin        DataModule1.SQLite3Connection1.Connected := false;        DataModule1.SQLTransaction1.Active := false;        DataModule1.SQLite3Connection1.Connected := True;        DataModule1.SQLTransaction1.Active := True;     end;end; end.        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 unit main; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls; type   { TForm1 }   TForm1 = class(TForm)    Button1: TButton;    procedure Button1Click(Sender: TObject);  private   public   end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1 } uses dm; procedure TForm1.Button1Click(Sender: TObject);var  arrList: TStringArray;  arrNumbers: TStringArray;  SL: TStringList;  strSQL: string;  strDrawing: string;  strNumbers: string;  intCnt: integer;  i: integer;begin  SL := TStringList.Create;  SL.Delimiter := ',';  SL.StrictDelimiter := true;  SL.LoadFromFile(data.csv');  intCnt := SL.Count;   for i := 1 to intCnt do      begin        strDrawing := SL.Strings[i];        arrList := strDrawing.Split(',');        if (arrList[0] = '""') Or (arrList[0] = 'Date') then           begin           continue;           end;        strNumbers := arrList[1];      strNumbers := StringReplace(strNumbers,'"','',[rfReplaceAll]);        strNumbers := StringReplace(strNumbers,' - ',';',[rfReplaceAll]);      strNumbers := Trim(strNumbers);        arrNumbers := strNumbers.Split(';');         strSQL := 'INSERT into powerball (date_drawn,wb1,wb2,wb3,wb4,wb5,pwrball) VALUES (:date_drawn,:wb1,:wb2,:wb3,:wb4,:wb5,:pwrball)';         DataModule1.SQLQuery1.SQL.Text := strSQL;        DataModule1.SQLQuery1.Params.paramByName('date_drawn').AsString := arrList[0];        DataModule1.SQLQuery1.Params.paramByName('wb1').AsString:= arrNumbers[0];        DataModule1.SQLQuery1.Params.paramByName('wb2').AsString := arrNumbers[1];        DataModule1.SQLQuery1.Params.paramByName('wb3').AsString := arrNumbers[2];        DataModule1.SQLQuery1.Params.paramByName('wb4').AsString := arrNumbers[3];        DataModule1.SQLQuery1.Params.paramByName('wb5').AsString := arrNumbers[4];        DataModule1.SQLQuery1.Params.paramByName('pwrball').AsString := arrNumbers[5];        DataModule1.SQLQuery1.ExecSQL;        DataModule1.SQLTransaction1.Commit;       end;end; end. ---------------------------------------------------------------------- rvk · 2023-10-19 03:35 That source won't compile (you are missing a ' in there so this is not compilable). Maybe you can post the complete test project because I can't see if dm is really created on startup. For that the project.lpr is important. You can choose project > publish project to create a zip of all relevant files. It is important dm.pas is in the autocreate part of Forms in the project option section and before main.pas. See here https://wiki.freepascal.org/Form_Tutorial#Use_Another_Form_as _the_Main_Form ---------------------------------------------------------------------- Zvoni · 2023-10-19 06:45 What rvk said: It's usually a sign, that the class is not created On a sidenote: reading your code you're trying to parse a string according to some rules. Do you have some sample strings you're trying to parse? This looks like something for Regex ---------------------------------------------------------------------- TRon · 2023-10-19 09:33 Quote from: dodgebros on October 19, 2023, 04:37:36 amSee attached screen captures and the code below to hopefully answer these questions. All information provided does not give answers to the questions asked by rvk. The datamodule is either autocreated (see your lpr project file) or in case you haven't then it should be manually created. In your provided information there is no sign of either so there is only one conclusion possible at this point and that is that the datamodule is not created (at the point when it is required so that it can be used), hence the error. ---------------------------------------------------------------------- dodgebros · 2023-10-19 14:57 Thanks for the continued help folks, here is where I am at: Quote from: rvk on October 19, 2023, 05:35:31 amThat source won't compile (you are missing a ' in there so this is not compilable). That was a copy paste error, code had the missing single quote QuoteMaybe you can post the complete test project because I can't see if dm is really created on startup. For that the project.lpr is important. You can choose project > publish project to create a zip of all relevant files. Done, didn't know you could do that, see attached zip file QuoteIt is important dm.pas is in the autocreate part of Forms in the project option section and before main.pas. See here https://wiki.freepascal.org/Form_Tutorial#Use_Another_Form_as _the_Main_Form It was not but now it is the first item created via Project Options->Forms->Auto-create Forms Now when I run the code I get the error "database is locked" and it stops on the code line Code: [Select]        DataModule1.SQLTransaction1.Commit; ---------------------------------------------------------------------- rvk · 2023-10-19 15:24 The only thing wrong I can see is this line: 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   for i := 1 to intCnt dointCnt is SL.Count but you should do this: 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   for i := 0 to intCnt - 1 do Besides that... *) You don't free SL *) It's also best to include try/finally around the SL := TStringList.Create. *) Check for empty line because otherwise it will crash on an unexpected line. So something like this: 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  arrList: TStringArray;  arrNumbers: TStringArray;  SL: TStringList;  strSQL: string;  strDrawing: string;  strNumbers: string;  i: integer;begin  SL := TStringList.Create;  try    SL.Delimiter := ',';    SL.StrictDelimiter := True;    SL.LoadFromFile('data.csv');    for i := 0 to SL.Count - 1 do    begin      strDrawing := SL.Strings[i];      arrList := strDrawing.Split(',');      if (Length(arrList) < 2) then continue; // minimal 2 fields      if (arrList[0] = '""') or (arrList[0] = 'Date') then continue;       strNumbers := arrList[1];      strNumbers := StringReplace(strNumbers, '"', '', [rfReplaceAll]);      strNumbers := StringReplace(strNumbers, ' - ', ';', [rfReplaceAll]);      strNumbers := Trim(strNumbers);      arrNumbers := strNumbers.Split(';');       if (Length(arrNumbers) < 6) then continue; // minimal 6 fields       strSQL := 'INSERT into powerball (date_drawn,wb1,wb2,wb3,wb4,wb5,pwrball) VALUES (:date_drawn,:wb1,:wb2,:wb3,:wb4,:wb5,:pwrball)';      DataModule1.SQLQuery1.SQL.Text := strSQL;      DataModule1.SQLQuery1.Params.paramByName('date_drawn').AsString := arrList[0];      DataModule1.SQLQuery1.Params.paramByName('wb1').AsString := arrNumbers[0];      DataModule1.SQLQuery1.Params.paramByName('wb2').AsString := arrNumbers[1];      DataModule1.SQLQuery1.Params.paramByName('wb3').AsString := arrNumbers[2];      DataModule1.SQLQuery1.Params.paramByName('wb4').AsString := arrNumbers[3];      DataModule1.SQLQuery1.Params.paramByName('wb5').AsString := arrNumbers[4];      DataModule1.SQLQuery1.Params.paramByName('pwrball').AsString := arrNumbers[5];      DataModule1.SQLQuery1.ExecSQL;       // do you really want to commit every line??? Maybe do evert 100 or 1000 records?      if i mod 100 = 0 then DataModule1.SQLTransaction1.Commit;     end;  finally    DataModule1.SQLTransaction1.Commit;    SL.Free;  end;end; ---------------------------------------------------------------------- rvk · 2023-10-19 15:33 Now that you have provided the .csv I see a problem with the Winning numbers and the powerball. You split the numbers field in 5 numbers (powerball is a separate field). Then you expect the powerball to be in that number array (which it isn't). Starting with my code you can add a separate field for the powerball and change the <6 into <5. I assume you can do this yourself (otherwise give a yell). I'm also not sure why you skip the "" line with winning numbers. (But I'm not familiar with powerball) ---------------------------------------------------------------------- dodgebros · 2023-10-19 19:58 OK, here is my revised rvk's code below.  Now I get the error message "database locked".  It happens when the code line "DataModule1.SQLQuery1.ExecSQL;" is processed: Thanks, TD Code: [Select]procedure TForm1.Button1Click(Sender: TObject); var   arrList: TStringArray;   arrNumbers: TStringArray;   SL: TStringList;   strSQL: string;   strDrawing: string;   strNumbers: string;   intCnt: integer;   i: integer;   c: integer; begin      SL := TStringList.Create;       try         SL.Delimiter := ',';         SL.StrictDelimiter := True;         SL.LoadFromFile('data.csv');         for i := 0 to SL.Count - 1 do         begin           if (i = 0) Or (Odd(i) = false) then continue;           strDrawing := SL.Strings[i];           arrList := strDrawing.Split(',');           if (Length(arrList) < 2) then continue; // minimal 2 fields           strNumbers := arrList[1];           strNumbers := StringReplace(strNumbers, '"', '', [rfReplaceAll]);           strNumbers := StringReplace(strNumbers, ' - ', ';', [rfReplaceAll]);           strNumbers := Trim(strNumbers);           arrNumbers := strNumbers.Split(';');           //if (Length(arrNumbers) < 6) then continue; // minimal 6 fields           strSQL := 'INSERT into powerball (date_drawn,wb1,wb2,wb3,wb4,wb5,pwrball) VALUES (:date_drawn,:wb1,:wb2,:wb3,:wb4,:wb5,:pwrball)';           DataModule1.SQLQuery1.SQL.Text := strSQL;           DataModule1.SQLQuery1.Params.paramByName('date_drawn').AsString := arrList[0];           DataModule1.SQLQuery1.Params.paramByName('wb1').AsString := arrNumbers[0];           DataModule1.SQLQuery1.Params.paramByName('wb2').AsString := arrNumbers[1];           DataModule1.SQLQuery1.Params.paramByName('wb3').AsString := arrNumbers[2];           DataModule1.SQLQuery1.Params.paramByName('wb4').AsString := arrNumbers[3];           DataModule1.SQLQuery1.Params.paramByName('wb5').AsString := arrNumbers[4];           DataModule1.SQLQuery1.Params.paramByName('pwrball').AsString := arrList[2];           DataModule1.SQLQuery1.ExecSQL;           // do you really want to commit every line??? Maybe do evert 100 or 1000 records?           if i mod 100 = 0 then DataModule1.SQLTransaction1.Commit;         end;       finally         DataModule1.SQLTransaction1.Commit;         SL.Free;       end;     end; ---------------------------------------------------------------------- rvk · 2023-10-19 20:02 Leave the (Length(arrNumbers) < 6) line in there but change it to (Length(arrNumbers) < 5) (So change the 6 to 5) Are you absolutely sure you don't have the database open in another database manager? ---------------------------------------------------------------------- dodgebros · 2023-10-20 03:43 There were no other apps accessing the database, but I moved this code from the Datamodule OnCreate event to under the button on form Main and now it works correctly. Code: [Select] if DataModule1.SQLite3Connection1.Connected = true then         begin            DataModule1.SQLite3Connection1.Connected := false;            DataModule1.SQLTransaction1.Active := false;            DataModule1.SQLQuery1.Active := false;            DataModule1.SQLite3Connection1.Connected := True;            DataModule1.SQLTransaction1.Active := True;            DataModule1.SQLQuery1.Active := True;         end;  Thanks for all of the help with this!!! TD ---------------------------------------------------------------------- rvk · 2023-10-20 05:08 Quote from: dodgebros on October 20, 2023, 05:43:58 amThere were no other apps accessing the database, but I moved this code from the Datamodule OnCreate event to under the button on form Main and now it works correctly. Code: [Select] if DataModule1.SQLite3Connection1.Connected = true then         begin            DataModule1.SQLite3Connection1.Connected := false;            DataModule1.SQLTransaction1.Active := false;            DataModule1.SQLQuery1.Active := false;            DataModule1.SQLite3Connection1.Connected := True;            DataModule1.SQLTransaction1.Active := True;            DataModule1.SQLQuery1.Active := True;         end;  Thanks for all of the help with this!!! TD What does that code do? In oncreate it should do nothing because when creating the connected should be false. (You have connected to false in the object inspector so it's false in oncreate) So what are you trying to do there? I think you can even completely remove that bit of code. If on the other hand you have connected set to true in your IDE then THAT is the cause of your locked file. Because then the IDE/Lazarus has the file open at design time and you open it again at run time. (I never set connected to true at design time and I leave DatabaseName empty for that cause. That way, when I accidentally set connected to true I will get an error because databasename is only set via code.) ---------------------------------------------------------------------- egsuh · 2023-10-20 07:34 If Datamodule is auto-created, you don't have to create TSQLQuery within the DatamoduleCreate event if you dropped it from component palette. Transaction should be started before any action on the database is done. So,   Transaction1.StartTransaction;   or    Transaction1.Active := true; should precede   SQLQuery1.Open; or SQLQuery1.ExecSQL;    And then you call    Transaction1.Commit; I'm explaining very "common" errors of novices --- the errors that I made when I was a novice (still I am...).  Please check whether these apply to your case. ---------------------------------------------------------------------- Zvoni · 2023-10-20 09:48 As Regex based in your CSV-File: https://regex101.com/r/cP822x/1 Pattern: Code: [Select](?:"\s(\d{1,2}\/\d{1,2}\/\d{4})\s"|("")),(?:"\s(\d+)\D+( \d+)\D+(\d+)\D+(\d+)\D+(\d+)\s"),(?:"\s(\d+)\s"),(?:"\s(\d+)\s"|"\s(\D +)\s"),(?:"(\s)") Edit: Updated Pattern https://regex101.com/r/cP822x/2 Code: [Select](?:"\s(?\d{1,2}\/\d{1,2}\/\d{4})\s"|(?"")) ,(?:"\s(?\d+)\D+(?\d+)\D+(?\d+)\D+(?\d+)\D+(?< Num5>\d+)\s"),(?:"\s(?\d+)\s"),(?:"\s(?\d+)\s"|" \s(?\D+)\s"),(?:"(?\s)") EDIT: In FPC3.2.2 the second Pattern is not supported (as of yet?) 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 program Project1; Uses Classes,regexpr; Const  G:Array[1..11] Of String=('Date ','Date','Num1','Num2','Num3','Num4','Num5','Powerball','Powerplay','P owerPlay','Empty'); Var  R:TRegexpr;  L:TStringList;  b:Boolean;  i:Integer;begin  L:=TStringList.Create;  L.LoadFromFile('c:\Path\To\Your\CSVFile\data.csv');  r:=TRegExpr.Create;  r.ModifierM:=True;  r.ModifierG:=True;  r.Express ion:='(?:"\s(\d{1,2}\/\d{1,2}\/\d{4})\s"|("")),(?:"\s(\d+)\D+(\d+)\D+( \d+)\D+(\d+)\D+(\d+)\s"),(?:"\s(\d+)\s"),(?:"\s(\d+)\s"|"\s(\D+)\s"),( ?:"(\s)")';  r.InputString:=L.Text;  b:=r.Exec;  If b Then    Begin    Repeat        For i:=1 To R.SubExprMatchCount-1 Do          If r.Match[i]<>'' Then Writeln(g[i]+'=',r.Match[i]);        Writeln('==============================');      until Not r.ExecNext;    end;  R.Free;  L.Free;end. Returns: Code: [Select]Date=9/6/2023 Num1=9 Num2=14 Num3=20 Num4=23 Num5=63 Powerball=1 Powerplay=3 ============================== Date="" Num1=1 Num2=12 Num3=30 Num4=50 Num5=55 Powerball=10 PowerPlay=--- ============================== Date=9/4/2023 Num1=1 Num2=26 Num3=32 Num4=46 Num5=51 Powerball=13 Powerplay=3 ============================== Date="" Num1=1 Num2=29 Num3=45 Num4=47 Num5=51 Powerball=7 PowerPlay=--- ---------------------------------------------------------------------- dodgebros · 2023-10-20 17:07 rvk: QuoteWhat does that code do? So what are you trying to do there? I found this piece of code here in this forum several years ago when I was looking for a solution to the 'database locked' error.  It was suppose to be the answer to this problem so that is why I included it. QuoteIf on the other hand you have connected set to true in your IDE then THAT is the cause of your locked file. Because then the IDE/Lazarus has the file open at design time and you open it again at run time. I did indeed have the SQLite3Connection1 set to active.  So I now have SQLite3Connection1, SQLQuery1, and Transaction1 all set to active false.  I do have the code below: 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 TDataModule1.DataModuleCreate(Sender: TObject);var  strDbName: string;begin     strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'data.sqlite3';     DataModule1.SQLite3Connection1.DatabaseName := strDbName;end;  It is working great now, thank you rvk for your help! egsuh: QuoteIf Datamodule is auto-created, you don't have to create TSQLQuery within the DatamoduleCreate event if you dropped it from component palette. Transaction should be started before any action on the database is done. So,   Transaction1.StartTransaction;   or    Transaction1.Active := true;   should precede   SQLQuery1.Open; or SQLQuery1.ExecSQL;    And then you call    Transaction1.Commit; Now that makes since, thank you! ---------------------------------------------------------------------- dodgebros · 2023-10-20 17:10 Thank you Zvoni for your reply.  I will keep your code example for future reference TD ---------------------------------------------------------------------- rvk · 2023-10-20 17:12 Quote from: dodgebros on October 20, 2023, 07:07:41 pmI did indeed have the SQLite3Connection1 set to active.  Yep, bingo. That was the cause of the locked file then. BTW you didn't have it to active in the test project you shared  But that's why I never set it to Connected or Active in the IDE. Glad it works now. ---------------------------------------------------------------------- dodgebros · 2023-10-20 17:16 Thanks again rvk, I had lost hope in being able to use SQLite in my projects. TD