Hi All,
Here's my case: in the background I download a json string from a website, which I use to update records in two tables. Obviously all this is done in the context of a transaction, my program does a great job, but after the transaction is closed, the program freezes and "android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): retrycount exceeded" error occurs after a few minutes of freezing. When I manage to see where the error occured, well on the first sql query, it's already in the main thread. Since the code snippet in question is running in a service, I have no way of knowing if others are not performing any database operations, such as queries, so, I open a separate sql object, yet the same freezing happens.
What is the correct approach to this logic?
Attached a code snippet to see what happens:
Thanks in advance
Steve
Here's my case: in the background I download a json string from a website, which I use to update records in two tables. Obviously all this is done in the context of a transaction, my program does a great job, but after the transaction is closed, the program freezes and "android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): retrycount exceeded" error occurs after a few minutes of freezing. When I manage to see where the error occured, well on the first sql query, it's already in the main thread. Since the code snippet in question is running in a service, I have no way of knowing if others are not performing any database operations, such as queries, so, I open a separate sql object, yet the same freezing happens.
What is the correct approach to this logic?
Attached a code snippet to see what happens:
B4X:
'ACTIVITY
...
CallSub(svcRESTnewWEB,"reg_last_sync_dates")
...
' SERVICE svcRESTnewWEB
reg_last_sync_dates download the json string, in the
Sub JobDoneX (J As HttpJob) I call the frissTorzsek() with the data I got from web.
'---------------------------------------------------------------------------------
'update in the background the master data changed on the server
'm has the resulting map with the updated data, e.g:
'{"success":1,"products":[
'{"id":1,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1011","megnevezes":"Piros alma","vtszkodtip":"VTSZ","vtsz":"808","me":"Kilogramm","bruttoar":"550","afakod":"27%","adomentesseg":null,"megjegyzes":"Érett piros alma","vonalkod":"9993456789185","azon":1100546657,"termekkep":null,"status":"A","umdate":"2024-07-09T11:55:51.000Z","fokatjson":"EGYEB","alkatjson":"KEDVEZMENY","ntakszorzo":"1","ntakme":null,"afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false},
'{"id":2,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1012","megnevezes":"Kókusz","vtszkodtip":"VTSZ","vtsz":"801","me":"db","bruttoar":"300","afakod":"27%","adomentesseg":null,"megjegyzes":"Friss kókuszdió","vonalkod":"9993456789186","azon":640092854,"termekkep":"upload/termek/1720526269980.jpg","status":"A","umdate":"2024-07-09T11:57:53.000Z","fokatjson":"ALKMENTESITAL_HELYBEN","alkatjson":"ITALCSOMAG","ntakszorzo":"1","ntakme":null,"afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false},
'{"id":3,"ceg_id":61,"bolt_id":61,"cikkcsop_id":1,"cikkszam":"1013","megnevezes":"Mogyoróvaj","vtszkodtip":"VTSZ","vtsz":"2008","me":"db","bruttoar":"800","afakod":"27%","adomentesseg":null,"megjegyzes":"Krémes mogyoróvaj","vonalkod":"9993456789187","azon":2907739029,"termekkep":"upload/termek/1720526304462.jpg","status":"A","umdate":"2024-07-09T11:58:26.000Z","fokatjson":"N/A","alkatjson":"N/A","ntakszorzo":"1","ntakme":"DARAB","afakodelv":"27%","repohar":false,"autszervizdij":"N","autszervizdijsz":"5","gongyolegazon":null,"keszlet":"0","keszlet_min":"0","keszletfigyelo":false}],
'"categories":[
'{"id":1,"bolt_id":61,"megnevezes":"ÉLELMISZER","megjegyzes":"finomságok tárháza","csoportkep":null,"ruhatarjegy":false,"status":"A","sorszamtip":0,"ksorszam":"","vsorszam":"","utsorszam":"","fokatjson":"ETEL","user":null,"umdate":"2024-07-09T11:55:18.000Z","azon":733624890,"merlegelendo":"N"}],
'"last_sync_date":"2024-07-07T12:06:22.00Z"}
private Sub frissTorzsek( m As Map)
Try
If sqll.IsInitialized Then
sqll.BeginTransaction
Try
Dim sb As StringBuilder
sb.Initialize
'az egyes kulcsok alatt táblák vannak
'később lehetnek mások
For Each tbl As String In Array As String ("categories","products")
If m.ContainsKey(tbl) Then
Private realtabla As String
If tbl = "products" Then
realtabla = "cikkt"
else If tbl = "categories" Then
realtabla = "cikkcsop"
End If
'csak olyan mezőket mentek el, amik a nálam lévő táblában is vannak
Private tblfields As Map
tblfields.Initialize
Dim rs As ResultSet = sqll.ExecQuery($"
pragma table_info(${realtabla})"$)
Do While rs.NextRow
tblfields.Put(rs.Getstring("name"),rs.Getstring("type").ToLowerCase)
Loop
rs.Close
'most elkezdem feldolgozni egy tábla szerveren változott rekordjait
Private l As List = m.Get(tbl)
Private wheremap As Map
wheremap.Initialize
For Each item As Map In l
If realtabla = "cikkt" Then
'fordítani kell bizonyos mezőket
If item.ContainsKey("cikkcsop_id") Then
item.Put("csopazon",item.Get("cikkcsop_id"))
wheremap.Put("azon",item.Get("azon"))
End If
else If realtabla = "cikkcsop" Then
'fordítani kell bizonyos mezőket
If item.ContainsKey("id") Then
item.Put("azon",item.Get("id"))
wheremap.Put("azon",item.Get("id"))
End If
End If
'kiszedem azokat a kapott mezőket, amik nálam nincsenek
Private datamap As Map
datamap.Initialize
For di = 0 To item.Size-1
If tblfields.ContainsKey( item.GetKeyAt(di) ) Then
If tblfields.Get(item.GetKeyAt(di)).As(String).ToUpperCase = "DATETIME" Then
datamap.Put(item.GetKeyAt(di),Codebase.UTCtoString(item.GetValueAt(di)))
Else
datamap.Put(item.GetKeyAt(di),item.GetValueAt(di))
End If
End If
Next
'megnézem a where alapján van-e már létező tétel
Private sa(wheremap.Size) As String
di = 0
sb.Append("SELECT count(*) FROM ").Append(realtabla).Append(" WHERE ")
For Each col As String In wheremap.Keys
If di > 0 Then sb.Append(" AND ")
sb.Append(col).Append(" = ?")
sa(di) = wheremap.Get(col)
di = di + 1
Next
Private vane As Int = sqll.ExecQuerySingleResult2(sb.ToString, sa)
If vane > 0 Then
DBUtils.UpdateRecord2(sqll,realtabla,datamap,wheremap)
Else
DBUtils.InsertMaps(sqll,realtabla,Array As Object(datamap))
End If
sb.Remove(0,sb.Length)
Log(item)
Next
End If
Next
sqll.TransactionSuccessful
Codebase.savTxtError("FRISSTORZS","Sikeres törzsfrissítés")
Catch
Log(LastException)
Codebase.savTxtError("FRISSTORZS",LastException.Message)
sqll.EndTransaction
End Try
End If
Catch
Codebase.savTxtError("FRISSTORZS 2",LastException.Message)
End Try
End SubThanks in advance
Steve