B4J Question [SOLVED] B4J 4.20, problems with sqllite, drop table causes ‘Table SQLLOOCKED’.

[johnerikson]

I can create and use the table in runtime, but not drop the table.

            Dim m As Map
            m.Initialize
            m.Put("ID", "INTEGER")
            m.Put("ImageID", "TEXT")
            jDBUtils.CreateTable(Main.dbImage, "tmpSel", M, "ID")
            sSQL = "INSERT INTO tmpSel VALUES (1, 'Img2032')"
            jDBUtils.sqlR(sSQL) ' work
            jDBUtils.DropTable(Main.dbImage, "tmpSel") 'doesn't work
            'java.sql.SQLException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)

More...............
If I dont stop the application and manually trying to create a table or modify data in a table with SQLLiteStudio it causes the following error:
[23:02:54] Could not commit table structure. Error message: Could not commit a database transaction. Details: database is locked

It looks like that the entire database is locked for some transactions.
What can cause this??

 

[billzhan]

Any open DB connections from other tools (like SQLLiteStudio ) may cause this issue. Try to close them first.

 

[johnerikson]

Thank you!

I already suspected it, but unfortunately, I get the same problem. This occurs even if I restart the computer, and do nothing else but run the application!

 

[billzhan]

FYI : https://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

Here are other reasons for getting an SQLITE_LOCKED error:

1. Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
   
   • Sometimes people think they have finished with a SELECT statement because sqlite3_step() has returned SQLITE_DONE. But the SELECT is not really complete until sqlite3_reset() or sqlite3_finalize() have been called.
     
     
     
   • As of check-in [3902] (2007-05-02 after version 3.3.17) this is now allowed for CREATE statement.
   
   
   
2. Trying to write to a table while a SELECT is active on that same table.
   
   • As of check-in [3355] (2006-08-16 after version 3.3.7) this is now allowed.
   
   
   
3. Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
   
   
   
4. fcntl(3, F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to mv the DB away, and copy it back so that it has a new Inode value.

jDBUtils is a customized module from DBUtils ? I guess some sql is left pending, post the jDBUtils module if possible.

What's the java version(java -version) and OS?

jDBUtils.sqlR(sSQL)

'DBUtils
Public Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List)
...
Try
    SQL.BeginTransaction
...

        SQL.TransactionSuccessful   'is this line missing?
    Catch
        Log(LastException)
        SQL.Rollback
    End Try

End Sub

 

[johnerikson]

Thanks!
Yes, I have read many comments about SQL. BeginTransaction and SQL. TransactionSuccessful and use it!
I can run the exact same code in B4A and works perfectly but .... If I use SQL. BeginTransaction and SQL. TransactionSuccessful and run the code in Galaxy S6 the database crashing!

With B4J I can’t get it to work whith this flow of code. I have tried all combinations with BeginTransaction, TransactionSuccessful, I have among other things read someware that if they run it once it works automatically. But nothing helps!

Installations:
I have C:\Program Files\Java\jdk1.8.0_65\bin\javac.exe installed and referenced.
SQLLite #AdditionalJar: sqlite jdbc 3.7.2
Lib jSQL 1.30 (B4J) and SQL 1.30 (B4A)

So what?

 

[billzhan]

I guess this issues relates to the jDBUtils (B4J) module. If you created (or edited) this module , please post the code.

You can try the official B4J DButils modlue: https://www.b4x.com/android/forum/threads/dbutils-example.34611/#content

We shall try to fix the issue on B4J first.

 

[johnerikson]

[Solved]problems with sqllite, drop table causes ‘Table SQLLOOCKED’.

Of 22 SQLLite-Select statements in the project I had forgotten one without closing the Resultset. It's enough to have serious problems to solve.
It is good that through the superb support varying thoughts on new solutions.
When I discovered that the code worked after copying my code into the attached example, remained me to debug more in my own code.
Now I understand how important it is to close all reultset after all SELECT statements when using SQLLite. Good to know!

Many thanks!