Locked SQlite

[limelect 48]

My problem is not Delphi but.... I am using SQLite.

I have these lines

       ProjectsFDTable.Edit;
        ProjectsFDTable.FieldByName('ProjectName').AsString :='1111';
      ProjectsFDTable.Post;

While developing I found I get locked situation I could not get rid off.

It took me some time to understand the problem is within the DATABASE itself!!! not my program.

Somehow while developing I made a locked database.

 

So my main question is can someone  give me some link for source or guideline

to build a small program to fix such problems within a database.

Although I have a "GOOD" database I still would like to have such a program

 

P.S During my "good old days" D6 ,D7, and before we had a program to fix the database records in BDE.

 

 

 

[Guest]

build another NEW db using old structure and later re-insert old records dont works?

you can use db managers tools like:

http://www.sqliteexpert.com/

Edited February 26, 2022 by Guest

[limelect 48]

@joaodanet2018well I have a "GOOD" database; That is not the problem.

using SQLiteDatabaseBrowserPortable the program has the same problem of lock.

As I said the problem is developing a small program to fix the problem if it happens 

again.

Or finding an SQLite. DB fixer

In the BDE days, I had many programs fixing the database with sources

Edited February 26, 2022 by limelect

[Guest]

see on FDconn the option like:

OpenMode and LockingMode

 

https://hsto.org/files/6c9/4e1/16e/6c94e116ed8a4e71b72af24bae598a2b.jpg

[Arnaud Bouchez 407]

See https://www.sqlite.org/lockingv3.html

 

By default, FireDac opens SQLite3 databases in "exclusive" mode, meaning that only a single connection is allowed.
It is much better for the performance, but it "locks" the file for opening outside this main connection.

So, as @joaodanet2018 wrote, change the LockingMode in FDconn, or just close the application currently using it.

[limelect 48]

@joaodanet2018thanks i did "play" with thos flags but did not help.

However i was not looking for a development/compile solution

I was looking for a demo/program that can find the problem and fix it

[Serge_G 87]

13 hours ago, limelect said:

So my main question is can someone  give me some link for source or guideline

well if you don't mind to read French 

I wrote a sample (with SQLite functions involved) here https://github.com/Serge-Girard/GestionComptesPersonnels (google trad readme.md for description but for comments inside pas file, should be a little more ctr+c/ctrl+v manipulations )

 

13 hours ago, limelect said:

While developing I found I get locked situation I could not get rid off.

This occurs if the database is used by 2 apps simultaneously. I run too often in that situation when debugging and having another GUI for SQLite still open, or it can occur when, during design, your data is open and your program asked for opening it  (I tend to set both  FDConnection.ConnectedStoredUsage  properties to false,  to avoid this problem)

[limelect 48]

Ok what you all say is that it is ONLY a problem with 2 Applications running but 

In my case, it was only under 1 development.

It seems that somehow the computer used to have access to more than one (same)database.

(for that I have a program called "unlocker.exe.)

well now I cannot check the problem as somehow it was fixed by itself

So maybe you are all right.

[limelect 48]

@Serge_G

Thanks, serge however to use your program

I needed to move the database to debug

and change to

Database=madb.sdb
DriverID=SQLite
While running I got date encode error recovery error

D10.2.3 maybe because our date is different?

[Serge_G 87]

1 hour ago, limelect said:

@Serge_G

While running I got date encode error recovery error

D10.2.3 maybe because our date is different? 

Oh, databasename was not supposed to be changed (autocreation)
And no, it's not a Delphi version problem it was the challenge, using a "text date" ( 'AAAAMMJJ' *) and getting a date value from a "SQLite function" (in " " because using Firedac SQLite functions)

* IMHO a really bad Patrick Premartin's habit

Edited February 27, 2022 by Serge_G

[limelect 48]

@Serge_G

I do not know if what I have applied to your case but I have 2 functions in my table and sql

that work

 

1.

 ProjectsFDTable.Filtered := false;
  ProjectsFDTable.FilterOptions := [foCaseInsensitive];

  ProjectsFDTable.Filter := 'MyDateTime > {dt ' + FormatDateTime('yyyy-mm-dd ',
    AdvDateTimePicker2.Date) + ' 00.00.00 }' +
    ' and MyDateTime < {dt ' + FormatDateTime('yyyy-mm-dd ',
    AdvDateTimePicker2.Date) + ' 23.59.59 }';
  ProjectsFDTable.Filtered := True;

 

2.

  FDQuery2.Close;
  FDQuery2.SQL.Clear;
  FDQuery2.SQL.Add('Select * from Projects');

  FDQuery2.SQL.Add('WHERE {fn CONVERT(MyDateTime,DATE)}=:MyDate');
  FDQuery2.SQL.Add('ORDER BY MyDateTime'); //=''03/10/2021''');
  FDQuery2.Params.ParamByName('MyDate').AsDate := AdvDateTimePicker1.Date;
FDQuery2.Open;

 

 

[Serge_G 87]

Yes that's also a way, but I was attempting to use SQLite functions defined in  UFonctionSQlite.pas 

see chapter Extending SQLite Engine/Custom Functions  https://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC

I also go to explore collation in the same chapter but not in the github program

[SwiftExpat 65]

You should use the SQLite backup to copy that locked database to a clean file. That allows the SQLite apis to handle the recovery if there was a pending write transaction.

 

Firedac has a component, so not much code to write.

https://docwiki.embarcadero.com/Libraries/Sydney/en/FireDAC.Phys.SQLite.TFDSQLiteBackup