Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Deleting SQLite tables but database is locked


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Jan 15, 2015 6:26 AM Last Post By: Dmitry Arefiev
Larry X

Posts: 15
Registered: 6/29/06
Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 12, 2015 1:53 PM
Am developing a simple SQLite DB browser/editor using XE6/FireDAC. When I attempt to delete a table, I get "ERROR: Database table is locked if any other table is open, even though the browser is the only user of the database at this point. Oddly enough, I can rename the table (just not delete it). To delete it I have to close all open tables. Here are my connection parameters:

FDConnection1.Connected := false;
FDConnection1.Params.Clear;
FDConnection1.Params.Add('DriverID=SQLite');
FDConnection1.Params.Add('DriverName=SQLite');
FDConnection1.Params.Add('Database='+D); // D is a string of the full path database name
FDConnection1.Params.Add('Protocol=TCPIP');
FDConnection1.Params.Add('Server=localhost');
FDConnection1.Params.Add('PRAGMA locking_mode = NORMAL');
FDConnection1.Params.Add('DateTimeFormat=DateTime');
FDConnection1.Params.Add('PRAGMA journal_mode=TRUNCATE');
FDConnection1.Params.Add('PRAGMA auto_vacuum=1');
FDConnection1.Params.Add('PRAGMA page_size=65536');
//FDConnection2.Params.Add('MetaDefSchema=MAIN');
FDConnection1.Connected := true;
Cristian Peța

Posts: 157
Registered: 8/7/06
Re: Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 12, 2015 11:14 PM   in response to: Larry X in response to: Larry X
//FDConnection2.Params.Add('MetaDefSchema=MAIN');
FDConnection1.Connected := true;
If you use two connections for the same database almost sure this is the reason.

Best regards,
Cristian Peța
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 13, 2015 7:12 AM   in response to: Larry X in response to: Larry X
FDConnection1.Params.Add('Protocol=TCPIP');
FDConnection1.Params.Add('Server=localhost');

Remove. SQLite is not a client/server DBMS.

FDConnection1.Params.Add('PRAGMA locking_mode = NORMAL');
FDConnection1.Params.Add('PRAGMA journal_mode=TRUNCATE');
FDConnection1.Params.Add('PRAGMA auto_vacuum=1');
FDConnection1.Params.Add('PRAGMA page_size=65536');

FireDAC SQLite driver does not have such parameters. See for details:
http://docwiki.embarcadero.com/RADStudio/XE7/en/Connect_to_SQLite_database_(FireDAC)
Instead you can do:
  FDConnection1.ExecSQL('PRAGMA locking_mode = NORMAL');
  FDConnection1.ExecSQL('PRAGMA journal_mode=TRUNCATE');
  FDConnection1.ExecSQL('PRAGMA auto_vacuum=1');
  FDConnection1.ExecSQL('PRAGMA page_size=65536');


--
With best regards,
Dmitry Arefiev / FireDAC Architect
Larry X

Posts: 15
Registered: 6/29/06
Re: Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 13, 2015 9:30 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Thank you Cristian and Dmitry. Very helpful. Issue solved.

Edited by: Larry X on Jan 13, 2015 9:31 AM
Larry X

Posts: 15
Registered: 6/29/06
Re: Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 13, 2015 1:22 PM   in response to: Larry X in response to: Larry X
I was too hasty in my assessment. The issue still persists, even though there is only one connection. Oddly enough, if I do a recordcount on the open table (call it TableA), I can then delete another table (call it TableB) even though TableA is open. If I attempt to delete TableB with TableA open without having done a recordcount on TableA, I get the database locked error message. What's going on?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Deleting SQLite tables but database is locked  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 15, 2015 6:26 AM   in response to: Larry X in response to: Larry X
Two points:
1) Set LockingMode=Normal at connection definition parameters.
2) Try to do FetchAll on the dataset, then try to delete the records.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02