The SQLITE_LOCKED message indicates that the database or table is locked when you execute your DROP TABLE statement. In SQLite, this type of lock typically occurs when:
A transaction is already open on the database (read or write) and has not been committed/closed.
You are trying to modify the structure (DDL: DROP TABLE, ALTER TABLE, etc.) within a transaction.
Another cursor or query is still active on the table you want to delete.
You are using two different instances (SQL and MSSQL) that point to the same file and are blocking each other.
In your code, you do :
[CODE lang="b4x" title="SQL"]msql.BeginTransaction
msql.ExecNonQuery(su.ToString)
msql.TransactionSuccessful[/CODE]
However, SQLite does not allow DDL commands (DROP TABLE, CREATE TABLE, ALTER TABLE) within a transaction.
This is precisely what causes the lock.
You could simply delete the table outside of BeginTransaction:
[CODE lang="b4x" title="Example"]Dim su As StringBuilder
su.Initialize
su.Append("DROP TABLE IF EXISTS ").Append(Name)
msql.ExecNonQuery(su.ToString)[/CODE]
If you have any ResultSets or queries open on this table, close them before the DROP.
In your code, you have `sql.InitializeSQLite(...)` and you're using `msql.ExecNonQuery(...)`. Make sure you don't have two connections open to the same file. Keep only one connection active.
The PRAGMA busy_timeout doesn't resolve this case because it's not a concurrent access conflict but a structural restriction.
Perhaps this code will work better. I haven't tested it.
[CODE lang="b4x" title="Example"]sql.InitializeSQLite(File.DirApp,"prodinfo.db",False)
If fx.Msgbox2(mCallback,"Delete Datatable " & Name,"Please confirm.","Delete","","cancel",fx.MSGBOX_WARNING) _
= XUI.DialogResponse_Positive Then
If DBInformation.ContainsKey(Name) Then
Dim su As StringBuilder
su.Initialize
su.Append("DROP TABLE IF EXISTS ").Append(Name)
sql.ExecNonQuery(su.ToString) ' no transaction here
DBInfo
Toast("success","Datatable " & Name & " deleted.",3000)
Return True
Else
Message("01","error","Delete Datatable","Table does not exist!",Null,"","","close")
Return Null
End If
End If[/CODE]
