Database locked

1

The app is connected to some local sqlite database files.

I am copying a table from one database file to another with this code

main=DBM1
fLoc=DBM2.DatabaseFile.NativePath

sql=“ATTACH DATABASE '”+fLoc+“’ AS B_alias”
OK=DBExecute(sql,“winWelcome.pbNewEvent.Pressed”)
if OK then
sql=“INSERT INTO B_alias.HandlerDog SELECT * FROM main.HandlerDog”
OK1=DBExecute(sql,“winWelcome.pbNewEvent.Pressed”)
end if

DBExecute method is simple:

DBM1.BeginTransaction
DBM1.ExecuteSQL(sql)
DBM1.CommitTransaction
return true

At the CommitTransaction I get the database is locked error.

EDIT: Didn’t do it exactly as OP suggested but his idea solved the problem. Here is a simplified version of what works for me.

main=DBM1
fLoc=DBM2.DatabaseFile.NativePath

try
sql=“ATTACH DATABASE '”+fLoc+“’ AS B_alias”
main.ExecuteSQL(sql)

sql=“INSERT INTO B_alias.HandlerDog SELECT * FROM main.HandlerDog”
main.ExecuteSQL(sql)
sql=“INSERT INTO B_alias.Teams SELECT * FROM main.Teams”
main.ExecuteSQL(sql)

sql=“DETACH DATABASE B_alias”
main.ExecuteSQL(sql)
MessageBox “Please review the handler/dog information for any changes since last event (month).”
catch DatabaseException
if DatabaseException.Message.Contains(“unique”) then
  MessageBox “It appears that this event (month) is already started.”
else
  MessageBox “There was an error transferring the team and handler/dog  information to the new event database. Contact the author for help.”
end if
end Try
2

what are the contents of fLoc ?

I suppose there are several possibilities

  1. the DB is truly locked at the OS level
  2. the db is in use by other applications
  3. depending on the OS it could be a security setting (like on macOS) that needs to be permitted specially
3
  1. How does a database file that I created get locked at the OS level? More importantly how does one unlock it?
  2. There is only one application so that shouldn’t be a problem.
  3. I am on MacOS Sequoia, Xojo 2025r2.1. I have these database files in the Library/Application Support/myName/appName/databaseFolder. I don’t get this locked issue when I just access these files for a normal SELECT or EXECUTE command.
4

The problem is probably from enclosing the attach operation in it’s own transaction, then additional operations in separate transactions. This can cause issues, depending on the journal mode. Also, there’s no reason for a transaction or commit for an attach - it doesn’t actually modify anything.

I suggest attaching the database directly (don’t use your DBExecute method). And use the .AddDatabase method (and corresponding .RemoveDatabase) instead of doing it via a SQL script. Enclose this in a Try…Catch for IOException. Then use your DBExecute method to execute the operations, but add Try..Catch for DBException in that method (unless it’s already there but you didn’t show it). Perform Rollback in the handler, and of course return False. Otherwise perform CommitTransaction and return True.

1 Like
5

those are just possibilities

In macOS you can use Get Info to LOCK a file - but I suspect that isnt what you’ve done here then