Page 1 of 1
sqlite force unlock
Posted: Thu 07 Feb 2013 15:28
by sandy771
I have a db (created by my program) that is returning a database locked error when I try to delete an index.
The db is on my PC, my machine has been rebooted and I am the only user of the .db. The database *may* have been shutdown wihile in the middle of a transaction. There are no journal files.
How can I force the .db back into a usable state? Ihave read about craeting a backup, but this is a bit too unwieldy and I would like an easier method (for my users).
P
Re: sqlite force unlock
Posted: Sat 09 Feb 2013 11:41
by CristianP
Hi,
My procedure to do this is a little complicated and I will describe only the steps.
1.
you need to copy sqlite3.exe and repair.bat into you database folder.
into repair.bat you need to have:
sqlite3.exe CurrentDB .dump|sqlite3.exe NewDB
2.
Code: Select all
UniConnection1.Connected := False;
RunAndWaitShell(sDBPath + 'repair.bat', '', sDBPath, SW_SHOWNORMAL);
3.
rename CurrentDB to OldDB
rename NewDB into CurrentDB
4.
If all is ok then delete OldBD
Here is RunAndWaitShell() procedure:
Code: Select all
procedure RunAndWaitShell(Executable, Parameter, WorkingDir: String; ShowParameter: INTEGER);
var
Info: TShellExecuteInfo;
pInfo: pShellExecuteInfo;
exitCode: DWord;
begin
pInfo := @Info;
//if swDebug then ShowParameter:=0;
with Info do begin
cbSize := SizeOf(Info);
fMask := SEE_MASK_NOCLOSEPROCESS;
Wnd := application.Handle;
lpVerb := NIL;
lpFile := PChar(Executable);
lpParameters := PChar(Parameter);
lpDirectory := PChar(WorkingDir);
nShow := ShowParameter;
hInstApp := 0;
//optional
lpIDList := nil;
lpClass := nil;
hkeyClass := 0;
dwHotKey := 0;
end;
ShellExecuteEx(pInfo);
repeat
exitCode := WaitForSingleObject(Info.hProcess, INFINITE);
until (exitCode <> WAIT_TIMEOUT);
end;
Best Regards,
Cristian Peţa
Re: sqlite force unlock
Posted: Mon 11 Feb 2013 13:53
by AlexP
Hello,
There is no engine for restoring damaged DBs in our components. You can use the engine suggested in the previous post for this.
Re: sqlite force unlock
Posted: Tue 12 Feb 2013 14:17
by sandy771
Thanks for the replies - unfortunately as I am using custom collation I get an error "no such collation sequence: SYSTEMNOCASE" when I run the command line:(
Re: sqlite force unlock
Posted: Wed 13 Feb 2013 16:52
by AlexP
Hello,
Unfortunately, for the time being, this task cannot be resolved using our components, however, we will add support for the SQLite Online Backup API engine in one of the next versions. That will allow BackUp operations using just our components in an application without other utilities.
Re: sqlite force unlock
Posted: Thu 14 Feb 2013 07:26
by CristianP
Hi,
I have created my "backup" using two TUniConnection and TUniLoader. This is mainly used if someone want to move to other server but can be a used as backup.
With my procedure I can copy data between SQLite, MySQL, PostgreSQL and SQL Server. But this is personalized for my databases.
In brief:
1. do connections
2. create table
3. use TUniLoader to move data
4. create indexes, triggers....
For SQLite to SQLite steps 2 and 3 is easy with:
Code: Select all
SELECT * FROM sqlite_master WHERE type='table' and tbl_name='your_table'
Code: Select all
SELECT * FROM sqlite_master WHERE (type='index' or type='trigger') and tbl_name='your_table'
For list or table names you can use TUniConnection.GetTableNames() or select from sqlite_master.
Best Regards,
Cristian Peţa
Re: sqlite force unlock
Posted: Thu 14 Feb 2013 14:56
by sandy771
Thanks
The client re-opened the database and the lock issue had disappeared.
However in the meantime I configured a solution using unidump that may do the job.
dump (backup) the complete database
disconnect and save the existing database
craete a new blank database
create tables
retore from backup
create indexes