B4J Question Locked SQLite Database

[Ed Brown]

Hello Everyone,

I have this little snippet of code that inserts a row into a table in a SQLite database.

    Main.db.BeginTransaction
    Try
        Main.db.ExecNonQuery(cmd)
        Main.db.TransactionSuccessful
        Result = True
    Catch
        Main.db.Rollback
        Log("error inserting")
        Log(LastException)
    End Try

This results in a locked database and subsequent updates/inserts are not possible. The intellisense help indicates that the 'TransactionSuccessful' method commits AND ends the transaction. In an android app the equivalent snippet of code includes an additional method 'EndTransaction' that I suspect may be missing from the jSQL library...just a suspicion.

If I comment out the lines with 'BeginTransaction', 'TransactionSuccessful' and 'Rollback' then the database is not locked after the insert.

Can anyone provide a work around or some other helpful bit of insight?

 

[narek adonts]

Try to put BeginTransaction after the Try statement

 

[Hilton]

Hello Everyone,

I have this little snippet of code that inserts a row into a table in a SQLite database.

    Main.db.BeginTransaction
    Try
        Main.db.ExecNonQuery(cmd)
        Main.db.TransactionSuccessful
        Result = True
    Catch
        Main.db.Rollback
        Log("error inserting")
        Log(LastException)
    End Try

This results in a locked database and subsequent updates/inserts are not possible. The intellisense help indicates that the 'TransactionSuccessful' method commits AND ends the transaction. In an android app the equivalent snippet of code includes an additional method 'EndTransaction' that I suspect may be missing from the jSQL library...just a suspicion.

If I comment out the lines with 'BeginTransaction', 'TransactionSuccessful' and 'Rollback' then the database is not locked after the insert.

Can anyone provide a work around or some other helpful bit of insight?

Hi Ed,

That snippet of code seems fine, however, the problem tends to be resultsets that have not been closed. Please go through your code and scrupulously close the resultsets as soon as you have finished with them. I believe your problem will then go. I had exactly the same problem and as soon as I did the above all was well.

Hope it works for you,
Hilton.

 

[Ed Brown]

Hello @Hilton

DOH! That was it!

Many, many thanks.

 

[Erel]

Note that if you are calling ExecNonQuery once then you don't need to create a transaction as a transaction will be created automatically.

 

[Ed Brown]

Ah I see. That's good to know. I'll update my code accordingly. I'm assuming that this applies to the SQLite databases as well?

 

[Erel]

I'm assuming that this applies to the SQLite databases as well?

Yes.