Closing and reopening the database at runtime lazarus / 16-Databases ---------------------------------------------------------------------- Jiří Huňáček · 2020-12-28 19:16 Hi, would there be anyone to help me with advice (or even code) on how to close and reopen the connection to the database at runtime? My point is that during design time I have SQLite3Connection1.Connected and SQLQuery1.Active both set to true. Then I have a DBGrid connected with the displayed data. Unfortunately, when I run the application and execute the following code 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);begin  Form1.SQLQuery1.Close;  Form1.SQLQuery1.SQL.Text:='INSERT INTO data (text, number) VALUES ("Text",10)';  Form1.SQLQuery1.ExecSQL;  Form1.SQLTransaction1.Commit;   Form1.SQLQuery1.SQL.Text:='SELECT * FROM data';  Form1.SQLQuery1.ExecSQL;  Form1.SQLQuery1.Open;   Form1.DBGrid1.DataSource.DataSet.Refresh;end; procedure TForm1.FormCreate(Sender: TObject);begin  Form1.SQLite3Connection1.Connected := false;  Form1.SQLite3Connection1.DatabaseName := SysUtils.ExtractFilePath(ParamStr(0)) + 'datax.db';  Form1.SQLite3Connection1.Connected := true;   if Form1.SQLite3Connection1.Transaction.Active = false  then Form1.SQLite3Connection1.Transaction.StartTransaction;   Form1.SQLQuery1.Close;  Form1.SQLQuery1.SQL.Text := 'SELECT * FROM data';  Form1.SQLQuery1.ExecSQL;  Form1.SQLQuery1.Open;   Form1.DBGrid1.DataSource.DataSet.Refresh;end;  the program stops on line 6 "SQLTransaction1.Commit" with the error "project raised exception: Database is locked". Is it possible to somehow close and reopen the connection at runtime even though the database will be open during design time? Thanks for any advice George ---------------------------------------------------------------------- speter · 2020-12-28 22:55 2 things to try:   1: make sure you have write-access to the database file;   2: add 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 SQLite3Connection1.Connected := true; SQLQuery1.Active := true; before line 4. But, I can't see any problem with your code. When I tested it (without .connected=true & .active=true) it worked fine. So maybe it really is a file-access-privileges problem!? cheers S. ---------------------------------------------------------------------- CLA · 2020-12-29 00:00 I think CommitRetaining will keep your Transaction Open. ---------------------------------------------------------------------- egsuh · 2020-12-29 02:55 With SQLite, check whether it is open by another application, like SQLiteStudio. I'm not quite familiar with SQLite and not confident, but I experienced the same problem. Multi-user databases, like Firebird, etc. have no problem but SQLite seems single-user database. When it is open by another application, you may not change the content of it from other application. ---------------------------------------------------------------------- GAN · 2020-12-29 03:16 QuoteSQLite supports three different threading modes:     Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.     Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.     Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction. The threading mode can be selected at compile-time (when the SQLite library is being compiled from source code) or at start-time (when the application that intends to use SQLite is initializing) or at run-time (when a new SQLite database connection is being created). Generally speaking, run-time overrides start-time and start-time overrides compile-time. Except, single-thread mode cannot be overridden once selected. The default mode is serialized. Source and more info: https://sqlite.org/threadsafe.html ---------------------------------------------------------------------- cdbc · 2020-12-29 07:31 Hi It sounds like you have a "dangling" connection, somewhere..... Make ONE connection and reuse that.... Regards Benny ---------------------------------------------------------------------- avra · 2020-12-29 10:28 Quote from: cdbc on December 29, 2020, 08:31:07 amIt sounds like you have a "dangling" connection, somewhere.....Yes he does and he is aware of that: Quote from: Jiří Huňáček on December 28, 2020, 08:16:32 pmduring design time I have SQLite3Connection1.Connected and SQLQuery1.Active both set to true.He just asks if he can get away with that, or he needs to close design time connection each time before running his application from IDE? With SQLite he can not get away with that if more then 1 connection is needed which can write to database. But if max 1 connection is writable, then he might find an answer here: https://stackoverflow.com/questions/10325683/can-i-read-and-write-to- a-sqlite-database-concurrently-from-multiple-connections ---------------------------------------------------------------------- Jiří Huňáček · 2020-12-30 10:10 Hi, thank you all for the advice. Quote from: avra on December 29, 2020, 11:28:10 amQuote from: cdbc on December 29, 2020, 08:31:07 amIt sounds like you have a "dangling" connection, somewhere.....Yes he does and he is aware of that: Quote from: Jiří Huňáček on December 28, 2020, 08:16:32 pmduring design time I have SQLite3Connection1.Connected and SQLQuery1.Active both set to true.He just asks if he can get away with that, or he needs to close design time connection each time before running his application from IDE? With SQLite he can not get away with that if more then 1 connection is needed which can write to database. But if max 1 connection is writable, then he might find an answer here: https://stackoverflow.com/questions/10325683/can-i-read-and-write-to- a-sqlite-database-concurrently-from-multiple-connections So far, I have tried according to your link and I managed, after changing Journal-Mode to WAL, that the error "Database is locked" is no longer displayed and commits pass without problems. However, there is another problem that I cannot understand. After inserting new rows into the table, I successfully commit changes and then restore the DBGrid to see the changes in the table. Even though I have the DataSet activated at design time, Refresh will show me an error in the runtime that I do not have an active DataSet. I solved this by adding an activation 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 Form1.DBGrid1.DataSource.DataSet.Active:=true; before each Refresh. But somehow I can't understand it. Even if I put the activation line in Form.Create, I still have to add it again in other procedures, where I refresh DGBrid. ---------------------------------------------------------------------- avra · 2020-12-30 14:14 Quote from: Jiří Huňáček on December 30, 2020, 11:10:50 amSo far, I have tried according to your link and I managed, after changing Journal-Mode to WAL, that the error "Database is locked" is no longer displayed and commits pass without problems.I'm glad it worked. Quote from: Jiří Huňáček on December 30, 2020, 11:10:50 amAfter inserting new rows into the table, I successfully commit changes and then restore the DBGrid to see the changes in the table. Even though I have the DataSet activated at design time, Refresh will show me an error in the runtime that I do not have an active DataSet. I solved this by adding an activation 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 Form1.DBGrid1.DataSource.DataSet.Active:=true; before each Refresh.You are already using SQLite in a way it was not originally designed, so expect glitches and workarounds. If it were me I would keep no connection in design mode, and only temporary activate connection in IDE when some design changes are needed. Less headaches. And you already found that out yourself. ---------------------------------------------------------------------- egsuh · 2020-12-31 07:02 When you commit a transaction, all related datasets are closed. Try CommitRetaining in order not to close datasets. ---------------------------------------------------------------------- Jiří Huňáček · 2020-12-31 10:43 Hi, Quote from: avra on December 30, 2020, 03:14:39 pmQuote from: Jiří Huňáček on December 30, 2020, 11:10:50 amSo far, I have tried according to your link and I managed, after changing Journal-Mode to WAL, that the error "Database is locked" is no longer displayed and commits pass without problems.I'm glad it worked. Quote from: Jiří Huňáček on December 30, 2020, 11:10:50 amAfter inserting new rows into the table, I successfully commit changes and then restore the DBGrid to see the changes in the table. Even though I have the DataSet activated at design time, Refresh will show me an error in the runtime that I do not have an active DataSet. I solved this by adding an activation 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 Form1.DBGrid1.DataSource.DataSet.Active:=true; before each Refresh.You are already using SQLite in a way it was not originally designed, so expect glitches and workarounds. If it were me I would keep no connection in design mode, and only temporary activate connection in IDE when some design changes are needed. Less headaches. And you already found that out yourself. if I understand this correctly (English is not my native language), then in the IDE after setting the necessary things (eg field lengths in DBGrid), I should turn off the connection to the database and turn it on only after starting the application programmatically. Unfortunately, I returned to programming after a long time, and I still have some habits from the time I programmed in FoxPro 2.6 and Visual Basic 6. So I expect some problems to arise.  Quote from: egsuh on December 31, 2020, 08:02:04 amWhen you commit a transaction, all related datasets are closed. Try CommitRetaining in order not to close datasets. Thank you egsuh, it helped. ---------------------------------------------------------------------- JanRoza · 2020-12-31 11:19 In my database applications I always use a DataModule and in its OnCreate procedure I open the SQLite database and necessary tables as follows: 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   // Database located standard in program folder  strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'SilentBase.db';   // Check if database is in application folder, if not use C:\ProgramData\SilentBase   if not FileExists(strDbName)  then strDbName := GetAppConfigDir(True) + 'SilentBase.db';   DbConnection.DatabaseName := strDbName;   // Open database  DbConnection.Connected := True;  DbTransaction.Active := True;   // Open Databases tables  qryDatabases.Active := True;  ........  ........  This ensures your database opens at application start and you can keep it closed in design time. Hope this helps a bit to get you going. ---------------------------------------------------------------------- Jiří Huňáček · 2020-12-31 13:02 One more question about disabling connections in the IDE. I assume that I have both options checked in the IDE (TSQLconnection.Connected and TSQLQuery.Active). When I want to deactivate the connection (uncheck the Connected option) it reports an Access violation error (Figure 1), I confirm it and when I save it, it displays Streaming error (Figure 2). I click the Ignore button and everything works and both options (TSQLconnection.Connected and TSQLQuery.Active) are unchecked. I don't know if I have something wrong set up or if it's some Lazarus error. I use Lazarus IDE v2.0.10 r63526 and FPC 3.2.0.