Jump to content
Sign in to follow this  
limelect

Locked SQlite

Recommended Posts

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.

 


 

 

Share this post


Link to post
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 by Guest

Share this post


Link to post

@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 by limelect

Share this post


Link to post

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.

Share this post


Link to post

@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

Share this post


Link to post
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 :classic_wink:)

 

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)

Share this post


Link to post

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.

Share this post


Link to post

@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?

Share this post


Link to post
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 by Serge_G

Share this post


Link to post

@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;

 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×