B4J Question SQLite Drop Tabale ERROR!

[Guenter Becker]

Hello,
I'm using and SQLite Database and I like to delete a Table by code. The Project is for single user and the database is opened only by one. No other prior action is done.
I do not klnow where the Lock is comming from and all my ways tested to avoid the error do'nt lead to success.
I need help to make ist possible.

 #AdditionalJar: sqlite-jdbc-3.46.0.0
    #AdditionalJar: slf4j-api-2.0.13
    'The reference to slf4j-nop is not mandatory. However SLF4J will issue a warning, that can be ignored.
    'So, for a cleaner log add this
    #AdditionalJar: slf4j-nop-2.0.13.jar

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) = True Then
                    ' set database timeout
                    msql.ExecNonQuery("PRAGMA busy_timeout = 5000")
                    Dim su As StringBuilder: su.initialize
                    su.append("DROP TABLE IF EXISTS ").append(Name)
                    '# do sql command
                    'msql.TransactionSuccessful
                    msql.BeginTransaction
                    msql.ExecNonQuery(su.tostring)
                    msql.TransactionSuccessful
                    '# update DBInformation
                    DBInfo
                    '# final message
                    Toast("success","Datatable " & Name & " deleted.",3000)
                    Return True
                Else
                    Message("01","error","Detele Datatable","Table does not exists!",Null,"","","close")
                    Return Null
                End If
                Return False
            End If

Waiting for debugger to connect...
Program started.
Error occurred on line: 496 (GBEDBU)
java.sql.SQLException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.execute(DB.java:339)
    at org.sqlite.Stmt.exec(Stmt.java:65)
    at org.sqlite.Stmt.execute(Stmt.java:114)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:161)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:673)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:240)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
    at b4j.example.gbedbu._tbdelete(gbedbu.java:787)
    at b4j.example.main._button1_click(main.java:110)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    at java.base/java.lang.reflect.Method.invoke(Method.java:578)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA$1.run(BA.java:236)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:457)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:456)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:184)
    at java.base/java.lang.Thread.run(Thread.java:1589)

 

[zed]

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 :

msql.BeginTransaction
msql.ExecNonQuery(su.ToString)
msql.TransactionSuccessful

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:

Dim su As StringBuilder
su.Initialize
su.Append("DROP TABLE IF EXISTS ").Append(Name)
msql.ExecNonQuery(su.ToString)

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.

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

 

[Guenter Becker]

Thank you,
will test it. Assume it will not work either because I tested it without the transaction
And sorry my mistake 1st code line should be msql instead os sql.
Comming back ASAP if I have a result.