Sqlite3 Database is locked error
lazarus / 16-Databases

----------------------------------------------------------------------
nugax · 2022-02-09 23:08

Im not sure why this is happening, but I am getting a Database is
locked error, on the function:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
UpdateUserField(sFieldName, sNewData: string; iUserIDGiven: integer) :
boolean;                     

I have included entire sqlite code. I suspect im not freeing the db
somewhere else but I cannot find it...

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
sqlite; {$mode ObjFPC}{$H+} interface uses  Classes, SysUtils, sqldb,
sqlite3conn, programinfo; //Forward Declared Functionsfunction
LoadUserRecord(sDetailsGiven, sTypeDetails: string): boolean;   //Load
The UserRecordfunction LoadUserStatsRecord(sDetailsGiven,
sTypeDetails: string): boolean;   //Load The User Stats Recordfunction
DetermineUserDataGiven(sDataGiven: string): string; //Determines if
handle or UserID Is Givenfunction UpdateUserField(sFieldName,
sNewData: string; iUserIDGiven: integer) : boolean;function
ReturnUserIDFromDetails(sDataGiven: string):
string;  implementation uses  usermanager;  //Load The User
Recordfunction LoadUserRecord(sDetailsGiven, sTypeDetails: string):
boolean;var  Connect: TSQLite3Connection;  Trans: TSQLTransaction; 
Query: TSQLQuery;  db_name: string[100];  sqlhandle: string[255]; 
sFirstNameFound: string[255];  boolFoundUserPwd: boolean;  //
recReturn: userhandler.CyberUserRecord; begin   //Setup inital
variables  boolFoundUserPwd := False;   //Setup SQL  db_name :=
programinfo.cbbsInfo.sUserDB;   if (sTypeDetails = 'handle') then 
begin    sqlhandle := 'SELECT * FROM cbbs_users WHERE handle = "' +
sDetailsGiven + '"';  end;   if (sTypeDetails = 'userid') then  begin 
  sqlhandle := 'SELECT * FROM cbbs_users WHERE userid = "' +
sDetailsGiven + '"';  end;      //Code to update password from handle 
Connect := TSQLite3Connection.Create(nil);  Query :=
TSQLQuery.Create(nil);  Trans := TSQLTransaction.Create(Connect); 
Connect.Transaction := Trans;  Connect.DatabaseName := db_name; 
Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not
installed  Query.SQL.Text := sqlhandle;  Query.Database := Connect;
 //Connection for SQL  Query.Open;   //Set Users Current Record 
usermanager.recUserRecord.UserID :=
query.FieldByName('userid').AsInteger; 
usermanager.recUserRecord.Handle :=
query.FieldByName('handle').AsString; 
usermanager.recUserRecord.FirstName :=
query.FieldByName('firstname').AsString; 
usermanager.recUserRecord.LastName :=
query.FieldByName('lastname').AsString; 
usermanager.recUserRecord.Password :=
query.FieldByName('password').AsString; 
usermanager.recUserRecord.eMail :=
query.FieldByName('email').AsString;  usermanager.recUserRecord.dob :=
query.FieldByName('dob').AsString;  usermanager.recUserRecord.Affils
:= query.FieldByName('affils').AsString; 
usermanager.recUserRecord.SecGroup :=
query.FieldByName('secgroup').AsString; 
usermanager.recUserRecord.MsgEditor :=
query.FieldByName('msgeditor').AsString; 
usermanager.recUserRecord.AccountEnabled :=
query.FieldByName('accountenabled').AsInteger; 
usermanager.recUserRecord.AccountStatus :=
query.FieldByName('accountstatus').AsString; 
usermanager.recUserRecord.MsgGroup :=
query.FieldByName('msggroup').AsString; 
usermanager.recUserRecord.MsgArea :=
query.FieldByName('msgarea').AsString; 
usermanager.recUserRecord.FileGroup :=
query.FieldByName('filegroup').AsString; 
usermanager.recUserRecord.FileArea :=
query.FieldByName('filearea').AsString; 
usermanager.recUserRecord.SysopNotes :=
query.FieldByName('sysopnotes').AsString; 
usermanager.recUserRecord.ForcePasswordChange :=
query.FieldByName('forcepasswordchange').AsInteger;   query.Close; 
query.Free;  Trans.Free;  Connect.Free;  exit(True);   {  if
(query.FieldByName('userid').ToString <> '') then  begin   
Query.Close;    Query.Free;    Connect.Free;    Trans.Free;    Result
:= True;  end  else  begin    Query.Close;    Query.Free;   
Connect.Free;    Trans.Free;    Result := False;  end;  }end; //Load
The User Stats Recordfunction LoadUserStatsRecord(sDetailsGiven,
sTypeDetails: string): boolean;var  Connect: TSQLite3Connection; 
Trans: TSQLTransaction;  Query: TSQLQuery;  db_name: string[100]; 
sqlhandle: string[255];  sFirstNameFound: string[255]; 
boolFoundUserPwd: boolean; begin   //Setup inital variables 
boolFoundUserPwd := False;   //Setup SQL  db_name :=
programinfo.cbbsInfo.sUserDB;   if (sTypeDetails = 'handle') then 
begin    sqlhandle := 'SELECT * FROM cbbs_users WHERE handle = "' +
sDetailsGiven + '"';  end;   if (sTypeDetails = 'userid') then  begin 
  sqlhandle := 'SELECT * FROM cbbs_users WHERE userid = "' +
sDetailsGiven + '"';  end;      //Code to update password from handle 
Connect := TSQLite3Connection.Create(nil);  Query :=
TSQLQuery.Create(nil);  Trans := TSQLTransaction.Create(Connect); 
Connect.Transaction := Trans;  Connect.DatabaseName := db_name; 
Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not
installed  Query.SQL.Text := sqlhandle;  Query.Database := Connect;
 //Connection for SQL  Query.Open;   //Set Users Current Record 
usermanager.recUserStatsRecord.StatID :=
query.FieldByName('statid').AsLongint; 
usermanager.recUserStatsRecord.UserID :=
query.FieldByName('userid').AsLongint; 
usermanager.recUserStatsRecord.DateCreated :=
query.FieldByName('datecreated').AsString; 
usermanager.recUserStatsRecord.LastOnline :=
query.FieldByName('lastonline').AsString; 
usermanager.recUserStatsRecord.NumbCalls :=
query.FieldByName('numbcalls').AsInteger; 
usermanager.recUserStatsRecord.NumbDownloads :=
query.FieldByName('numbdownloads').AsInteger; 
usermanager.recUserStatsRecord.NumbUploads :=
query.FieldByName('numbuploads').AsInteger; 
usermanager.recUserStatsRecord.NumbEchoMsgs :=
query.FieldByName('numbechomsgs').AsInteger; 
usermanager.recUserStatsRecord.NumbEmails :=
query.FieldByName('numbemails').AsInteger; 
usermanager.recUserStatsRecord.NumbLocalMsgs :=
query.FieldByName('localmsgs').AsInteger;    if
(query.FieldByName('userid').ToString <> '') then  begin   
Query.Close;    Query.Free;    Trans.Free;    Connect.Free;    Result
:= True;  end  else  begin    Query.Close;    Query.Free;   
Trans.Free;    Connect.Free;    Result := False;  end; end; //Update
User Fieldfunction UpdateUserField(sFieldName, sNewData: string;
iUserIDGiven: integer) : boolean;var  Connect: TSQLite3Connection; 
Trans: TSQLTransaction;  db_name: string[100];  sql:  string; begin  
//Setup SQL  db_name := programinfo.cbbsInfo.sUserDB;   sql := 'UPDATE
cbbs_users SET ' + sFieldName + ' =  "' + sNewData + '" WHERE userid =
"' + iUserIDGiven.tostring + '"';  Write('sql: ' + sql);     //Code to
update lastcall info from handle  Connect :=
TSQLite3Connection.Create(nil);  Trans :=
TSQLTransaction.Create(Connect);  Connect.Transaction := Trans; 
Connect.DatabaseName := db_name;  Trans.StartTransaction;  // opens
Connect, EInOutError if SQLite not installed 
Connect.ExecuteDirect(sql);  Trans.Commit;  Trans.Free; 
Connect.Free;  //Return Boolean result as sql finished. | Assume it
entered if no errors. Sqlite3 likes to error if it didnt work. 
exit(True); end;  //Determine If Handle Or UserID Is Givenfunction
DetermineUserDataGiven(sDataGiven: string): string;var  Connect:
TSQLite3Connection;  Trans: TSQLTransaction;  Query: TSQLQuery; 
db_name: string[100];  sqlhandle: string[255];  sResult :
string; begin  //Setup SQL  db_name := programinfo.cbbsInfo.sUserDB; 
sqlhandle := 'SELECT * FROM cbbs_users WHERE userid = "' + sDataGiven
+ '"';   //Code to update password from handle  Connect :=
TSQLite3Connection.Create(nil);  Query := TSQLQuery.Create(nil); 
Trans := TSQLTransaction.Create(Connect);  Connect.Transaction :=
Trans;  Connect.DatabaseName := db_name;  Trans.StartTransaction;  //
opens Connect, EInOutError if SQLite not installed  Query.SQL.Text :=
sqlhandle;  Query.Database := Connect;  //Connection for SQL 
Query.Open;   sResult := query.FieldByName('userid').AsString;   if
(sResult <> '') then  begin    Trans.Free;    connect.Free;   
query.Close;    query.Free;   {  Query.Close;    Query.Free;   
Connect.Free;    Trans.Free; }  //  Result := 'userid'; 
 exit('userid');    write('test');   end  else  begin    write
('test1');    sqlhandle := 'SELECT * FROM cbbs_users WHERE handle = "'
+ sDataGiven + '"';     //Code to update password from handle   
Connect := TSQLite3Connection.Create(nil);    Query :=
TSQLQuery.Create(nil);    Trans := TSQLTransaction.Create(Connect);  
Connect.Transaction := Trans;    Connect.DatabaseName := db_name;   
Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not
installed    Query.SQL.Text := sqlhandle;    Query.Database :=
Connect;  //Connection for SQL    Query.Open;  end;   if
(query.FieldByName('userid').AsString <> '') then  begin     //Close
And Free Resources    Query.Close;    Query.Free;    Connect.Free;   
Trans.Free;    exit('handle');  end   else  begin     //Close And Free
Resources    Query.Close;    Query.Free;    Connect.Free;   
Trans.Free;    exit('unknown');  end; end;  //Determine If Handle Or
UserID Is Givenfunction ReturnUserIDFromDetails(sDataGiven: string):
string;var  Connect: TSQLite3Connection;  Trans: TSQLTransaction; 
Query: TSQLQuery;  db_name: string[100];  sqlhandle: string[255]; 
sUserID: string; begin  //Setup SQL  db_name :=
programinfo.cbbsInfo.sUserDB;  sqlhandle := 'SELECT * FROM cbbs_users
WHERE userid = "' + sDataGiven + '"';   //Code to update password from
handle  Connect := TSQLite3Connection.Create(nil);  Query :=
TSQLQuery.Create(nil);  Trans := TSQLTransaction.Create(Connect); 
Connect.Transaction := Trans;  Connect.DatabaseName := db_name; 
Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not
installed  Query.SQL.Text := sqlhandle;  Query.Database := Connect;
 //Connection for SQL  Query.Open;   if
(query.FieldByName('userid').AsString <> '') then  begin   
exit(query.FieldByName('userid').AsString);  end  else  begin   
sqlhandle := 'SELECT * FROM cbbs_users WHERE handle = "' + sDataGiven
+ '"';     //Code to update password from handle    Connect :=
TSQLite3Connection.Create(nil);    Query := TSQLQuery.Create(nil);   
Trans := TSQLTransaction.Create(Connect);    Connect.Transaction :=
Trans;    Connect.DatabaseName := db_name;    Trans.StartTransaction;
 // opens Connect, EInOutError if SQLite not installed   
Query.SQL.Text := sqlhandle;    Query.Database := Connect;
 //Connection for SQL    Query.Open;  end;   //Get UserID  sUserID :=
query.FieldByName('userid').AsString;   if
(query.FieldByName('userid').AsString <> '') then  begin     //Close
And Free Resources    Query.Close;    Query.Free;    Trans.Free;   
Connect.Free;    exit(sUserID);  end   else  begin     //Close And
Free Resources    Query.Close;    Query.Free;    Trans.Free;   
Connect.Free;    exit('unknown');  end; end;    end.

----------------------------------------------------------------------
oghz · 2022-02-10 06:31

Sqlite database would be locked if a database lock has not been
released (happens in case of a write operation)
.

Wrap  your code in try..finally, block, here is some suggestions
 e.g


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  Try     
 // optionally check is databse is locked here        Connect :=
TSQLite3Connection.Create(nil);        Query :=
TSQLQuery.Create(nil);        Trans :=
TSQLTransaction.Create(Connect);                try        // execute
a query which might thorw exception here        // orther code here  
    except        // get the exception details and set return value
here        end        // your code which opens a connection and
execute queryfinally        // free all resources here       
Query.Close;        Query.Free;        Connect.Free;       
Trans.Free;        Result := False;End 
Here are some related threads

https://forum.lazarus.freepascal.org/index.php?topic=9504.0
https://forum.lazarus.freepascal.org/index.php?topic=33131.0
https://forum.lazarus.freepascal.org/index.php?topic=26749.0

----------------------------------------------------------------------
Zvoni · 2022-02-10 07:50

My money is on the fact, that you're not closing the connection before
freeing it.
Might be, that the "Free" for Connection/Transaction fails in some way
without throwing an error

----------------------------------------------------------------------
nugax · 2022-02-12 22:45

I figured it out. It wasnt being freed correctly.