Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: SQLite "database is locked" Error (Solved)


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


Permlink Replies: 6 - Last Post: Mar 19, 2017 8:02 AM Last Post By: Sean Hoffman Threads: [ Previous | Next ]
Earl Staley

Posts: 87
Registered: 4/9/07
SQLite "database is locked" Error (Solved)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 11, 2017 9:37 AM
I am trying to add data to a SQLite database, but I keep getting a "database is locked" error. However when I use sqlite3.exe to insert data into the database, it works fine and there is no "database is locked" error.

To troubleshoot the problem, I made another project using C++ Berlin 10.1 with minimal code. The project has the following components: TButton, TDBGrid, TFDConnection, TFDQuery, and a TDataSource. The contents of the TFDConnection Params is as follows:

Database=C:\AddressSQLite\Address.sqlite
LockingMode=Normal
DriverID=SQLite

Here is the entire code:

#include <vcl.h>
#pragma hdrstop
 
#include "MainForm.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
	: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
QueryAddress->RecNo=1;
QueryAddress->Edit();
QueryAddress->Fields->FieldByNumber(2)->Value="Hi, I'm here!!!";
QueryAddress->Post();
}


When I click the button on the test program, I get the "database is locked" error. Then I use sqlite3.exe to insert data into the database, it works fine and there is no "database is locked" error.

I have several other programs using SQLite databases and they work fine.

Why can sqlite3.exe insert data into the database but my test program cannot?

Thank you for your assistance...
Earl Staley

Edited by: Earl Staley on Mar 15, 2017 7:51 PM

Edited by: Earl Staley on Mar 16, 2017 4:37 PM
Earl Staley

Posts: 87
Registered: 4/9/07
Re: SQLite "database is locked" Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 13, 2017 1:23 PM   in response to: Earl Staley in response to: Earl Staley
A little further info. The original database was a BDE paradox database. I used FireDAC and BDE components to create and then load the data from the paradox table to the SQLite table. Every time I tried to add or edit the data in a separate C++ program, I got the "database is locked" error message. I could browse the data just fine. Somehow I could add or edit data without errors using sqlite3.exe, which I don't understand why.

I have done a lot more troubleshooting and I found that if I just used the program to create the table and not load any data into the table, then I could add and edit records just fine. So there is apparently something wrong with the code that transfers the paradox data to the SQLite table.

Here is the code that transfers the data:
void __fastcall TForm1::ButtonLoadClick(TObject *Sender) {
	//connect the table to the FireDAC connection
	DataModule1->FDTableSQLite->Close();
	DataModule1->FDTableSQLite->Connection = DataModule1->FDConnection1;
	DataModule1->FDTableSQLite->TableName =
		ListBoxSQLiteTables->Items->Strings[ListBoxSQLiteTables->ItemIndex];
	DataModule1->FDTableSQLite->Open();
 
	//get the paradox table ready to read the data from
	DataModule1->TablePdox->Open();
	DataModule1->TablePdox->First();
 
	//transfer the paradox table data to the SQLite table
	while (!DataModule1->TablePdox->Eof) {
		DataModule1->FDTableSQLite->Append();
		for (int i = 0; i < DataModule1->TablePdox->Fields->Count; i++) {
			if (!VarIsNull(DataModule1->TablePdox->Fields->FieldByNumber(i + 1)
				->Value))
				DataModule1->FDTableSQLite->Fields->FieldByNumber(i + 1)
					->Value = DataModule1->TablePdox->Fields->FieldByNumber
					(i + 1)->Value;
		}
		DataModule1->FDTableSQLite->Post();
		DataModule1->TablePdox->Next();
	}
	DataModule1->FDTableSQLite->Cancel();
 
	//set the SQLite dbgrid column widths
	int ColWidth = DBGridSQLite->ClientWidth / DBGridSQLite->Columns->Count;
	for (int i = 0; i < DBGridSQLite->Columns->Count; i++)
		DBGridSQLite->Columns->Items[i]->Width = ColWidth;
 
	//set the paradox dbgrid column widths
	for (int i = 0; i < DBGridPdox->Columns->Count; i++)
		DBGridPdox->Columns->Items[i]->Width = ColWidth;
}


Here is the SQL that created the SQLite table:

CREATE TABLE Address_Table (RecSN INTEGER PRIMARY KEY, First_Name String, Last_Name String NOT NULL, Address String, City String, State String, Zip String, Telephone String, Card Boolean)

Thank you for your help...
Earl Staley

Edited by: Earl Staley on Mar 13, 2017 1:27 PM
Earl Staley

Posts: 87
Registered: 4/9/07
Re: SQLite "database is locked" Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 14, 2017 6:07 PM   in response to: Earl Staley in response to: Earl Staley
Some more troubleshooting. Instead of loading the data with this:
				DataModule1->FDTableSQLite->Fields->FieldByNumber(i + 1)
					->Value = DataModule1->TablePdox->Fields->FieldByNumber
					(i + 1)->Value;


I manually entered junk data like this:
   DataModule1->FDTableSQLite->Fields->FieldByNumber(1)->Value = 1;
   DataModule1->FDTableSQLite->Fields->FieldByNumber(2)->Value = "Field No 2";
   DataModule1->FDTableSQLite->Fields->FieldByNumber(3)->Value = "Field No 3";
   DataModule1->FDTableSQLite->Fields->FieldByNumber(4)->Value = "Field No 4";
   DataModule1->FDTableSQLite->Fields->FieldByNumber(5)->Value = "Field No 5";
   DataModule1->FDTableSQLite->Fields->FieldByNumber(6)->Value = "Field No 6";
   DataModule1->FDTableSQLite->Fields->FieldByNumber(7)->Value = "Field No 7";


The top code results in a 'database is locked' error when I try to edit or add data to the SQLite database, whereas the bottom code does NOT result in a 'database is locked' error and I can edit or add data to the SQLite database normally.

Could the problem be that '->Value' is a variant and I am setting one variant equal to another variant?

If not, why does the bottom code result in a "database is locked" error, but the top does not?

Thank you...
Earl Staley

Edited by: Earl Staley on Mar 14, 2017 6:08 PM
Earl Staley

Posts: 87
Registered: 4/9/07
Re: SQLite "database is locked" Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 15, 2017 7:52 PM   in response to: Earl Staley in response to: Earl Staley
I think I have found the problem, but I don't know the solution.

If MaxRecords is less than 50, I can add and edit the SQLite database. If MaxRecords is >= 50, I get the 'database is locked' error when I try to add or edit the SQLite database. If I set MaxRecords to some number >=50, All of the records are transferred and I can browse the SQLite database. I just can't add records or edit the records. I belive the 50 record limitation is in FireDAC, but I don't know how to get around it when I am just using FDTables.

Here is the code:
void __fastcall TForm1::ButtonLoadClick(TObject *Sender) {
const int MaxRecords =49;
int count =0;
 	DataModule1->TablePdox->First();
	// transfer the paradox table data to the SQLite table
	while ((!DataModule1->TablePdox->Eof)&&(count<MaxRecords)) {
		DataModule1->FDTableSQLite->Append();
 
		// load fields
		for (int i = 0; i < DataModule1->TablePdox->Fields->Count; i++) {
			if (!VarIsNull(DataModule1->TablePdox->Fields->FieldByNumber(i + 1)
				->Value))
				DataModule1->FDTableSQLite->Fields->FieldByNumber(i + 1)
					->Value = DataModule1->TablePdox->Fields->FieldByNumber
					(i + 1)->Value;
		}
 
		DataModule1->FDTableSQLite->Post();
		// Application->ProcessMessages();
		DataModule1->TablePdox->Next();
        count++;
	}
	if (DataModule1->TablePdox->RecordCount ==
		DataModule1->FDTableSQLite->RecordCount)
		ButtonLoad->Enabled = false;
	// set the SQLite dbgrid column widths
	int ColWidth = DBGridSQLite->ClientWidth / DBGridSQLite->Columns->Count;
	for (int i = 0; i < DBGridSQLite->Columns->Count; i++)
		DBGridSQLite->Columns->Items[i]->Width = ColWidth;
 
	// set the paradox dbgrid column widths
	for (int i = 0; i < DBGridPdox->Columns->Count; i++)
		DBGridPdox->Columns->Items[i]->Width = ColWidth;
}


How can I get past the 50 record limitation?

Thank you for your help...
Earl Staley
Earl Staley

Posts: 87
Registered: 4/9/07
Re: SQLite "database is locked" Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 16, 2017 4:46 PM   in response to: Earl Staley in response to: Earl Staley
The problem was not in the program that I was writing to transfer the records from the Paradox tables to the SQLite database, as I had assumed. The problem was in the program I was using to browse and add/edit the data.

The program that I was using to look at the data was just a copy of the program for the Paradox tables with FireDAC components replacing the BDE components. I just had to change the FDQuery FetchOptions Mode to fmAll and no more 'database is locked' errors.

That took me about a week of intense troubleshooting to find that.

Earl Staley
John MacDonald

Posts: 179
Registered: 3/22/98
Re: SQLite "database is locked" Error  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 18, 2017 10:30 PM   in response to: Earl Staley in response to: Earl Staley
Good to see you got that sorted out.
I'm sending the reply to thank you for posting.

Sometimes it feels like we are alone. You're not...


Earl Staley wrote:
The problem was not in the program that I was writing to transfer the records from the Paradox tables to the SQLite database, as I had assumed. The problem was in the program I was using to browse and add/edit the data.

The program that I was using to look at the data was just a copy of the program for the Paradox tables with FireDAC components replacing the BDE components. I just had to change the FDQuery FetchOptions Mode to fmAll and no more 'database is locked' errors.

That took me about a week of intense troubleshooting to find that.

Earl Staley

--
JMAC out
Sean Hoffman

Posts: 125
Registered: 3/28/99
Re: SQLite "database is locked" Error (Solved)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 19, 2017 8:01 AM   in response to: Earl Staley in response to: Earl Staley
Indeed, thank you for posting the answer. This is likely going to save some poor soul (possibly me!!) down-the-line.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02