[yunqa.de] Re: How to correctly avoid 'database is locked' in multi user environment
- From: H M <docshotmail2@xxxxxxxxxxx>
- To: DISQLite3 mailing list <yunqa@xxxxxxxxxxxxx>
- Date: Fri, 11 Jan 2013 07:43:50 +0000
Thanks Ralf, excellent advice and quick response as usual.
In case anyone else is following this, below is my solution (slightly adatped
from the demo Busy_timeout.dpr)
My application could generate insert/update/delete sql with a varying number of
statements. So I replaced all my
DISQLite3Database1.execute(sql);
statements with a call to the following function.
......................................................................................................
function TDataModSP.ExecuteSQLWithLockedDBTimeOut(sql: string): integer;
var
FullSQL : string;
begin
//since we are goint to wrap the SQL inside a transaction, the supplied SQL
needs to end
//with a semicolon. Make sure it does
sql := trim(sql); //remove spaces before and after
if rightstr(sql,1) <> ';' then // no semicolon, need to add one
sql := sql + ';';
FullSQL := 'Begin transaction; ' + sql + ' Commit;' ; //generate transaction
sql
try
try
DISQLite3Database1.Open;
sqlite3_busy_timeout( DISQLite3Database1.Handle, BUSY_TIMEOUT_TIME); //set
busy timeout to const value
DISQLite3Database1.Execute(FullSQL); //try to run the sql until timeout
Result := SQLITE_OK; //if we get to here without exception then the data was
saved
except
on e: ESQLite3 do // The DISQLite3 database engine raised an error, return the
specific error code
begin
Result := e.ErrorCode;
DISQLite3Database1.Rollback; //undo all the bits of the transaction we
did get done in case...
end; //...its important we
do it all or none of it
else //for any other error simply re- raise it so it gets handled elsewhere
raise
end //try - except
finally
DISQLite3Database1.Close; //close the database again
end; //try - finally
end; //function
.......................................................................................
Then in the application the function is called with lines like
SQLiteReturnValue := DataModSP.ExecuteSQLWithLockedDBTimeOut(sql);
if SQLiteReturnValue = SQLITE_OK then
showmessage('Data saved OK');
else
case SQLiteReturnValue of
SQLITE_BUSY : showmessage ('Data not saved, please try again') ;
SQLITE_LOCKED : showmessage ('Data not saved, please please try again in a a
couple of seconds');
Else
showmessage ('Data not saved, error code: ' inttostr(SQLiteReturnValue ));
end; //case
Howard
Other related posts:
