iOS Question Sqlite database stays locked after insert using DBUtils

[Vern]

I've been working on this for 2 days now and can't figure it out. I use DBUtils (version 1.0) to create a database table and insert some rows (works fine). I can read and display the data with no problem, but if I try to update, I get "Unknown error calling sqlite3_step (5: database is locked)". Closing the Resultset or even closing and opening the connection before updating does not get past the problem.

I use similar code in my B4A apps all day long with no problems. Am I missing something here?

 

[Erel]

What do you mean with "update"? Are you replacing the db file?

Can you post the code?

 

[Vern]

Ok, found the problem and it has nothing to do with DBUtils. In Application_Start I used the following code to see if the table exists and that there's data in it before allowing the user to display data.

Try
ResultSet1 = SQL1.ExecQuery("SELECT name FROM sqlite_master WHERE type='table' AND name = 'Part_inventory'")
Result = (ResultSet1.IsInitialized And ResultSet1.NextRow)
If Result = False Then ... (display message to user to get parts first)
Catch
usual code...
End Try

On Android, if the table doesn't exist, no problem; it just ignores it. On IOS if the table doesn't exist, the SELECT remains pending and once you create the table, it's locked because the SELECT does not successfully complete. Any subsequent call to DBUtils.UpdateRecord on this table will fail because it is locked.

 

[Widget]

For iOS if the table doesn't exist, why can't you simply then execute after your "End Try"

SQL1.Close 
SQL1.Initialize(...)

to lose the connection and the lock that SQL1 had on the table?

Or as an alternative, just check sqlite_master to see if the table exists before you try and access it.

public Sub TableExists(aSQL As SQL,  aTableName As String) As Boolean
  If aSQL.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name = '" & aTableName & "'") = 0 Then
    Return False
  Else
    Return True
  End If   
End Sub