V Vern Member Licensed User Longtime User May 11, 2016 #1 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?
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 B4X founder Staff member Licensed User Longtime User May 12, 2016 #2 What do you mean with "update"? Are you replacing the db file? Can you post the code? Upvote 0
V Vern Member Licensed User Longtime User May 13, 2016 #3 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. Upvote 0
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 Well-Known Member Licensed User Longtime User Jun 16, 2016 #4 For iOS if the table doesn't exist, why can't you simply then execute after your "End Try" Code: Copy 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. Code: Copy 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 Upvote 0
For iOS if the table doesn't exist, why can't you simply then execute after your "End Try" Code: Copy 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. Code: Copy 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
