iOS Question SQLite -> different behaviour under ios than android

[TheRealMatze]

Hello!
I have just tested the sync-functionality of my app under ios. Unfortunately it failed... Under android the same code works fine.
The situation: I call a sub to insert / update a unique row in the database based on a group which is also stored in the database. The source is json and comes from a webserver. I query the groups one after each other, what means the outer resultset is open the whole time to get the next group-id.

First i check if the id is already inserted. Because of the missing rowCount under ios i use this construct

    Dim result As ResultSet
    Dim exist As Boolean = False
    Dim stlu As Int=0
    
    result=sql1.ExecQuery("Select lu from farms where id='" & id.Replace("'","''") & "'")
    
    If result.NextRow Then
        exist=True
        stlu=result.GetInt("lu")
        Else
        exist=False
    End If
    
    result.close
    
    If exist=False Then
          SQLE("Insert into ...")          '< This function is called
    Else
    ...
    end if

Sub SQLE(sqlQuery As String)
         sql1.ExecNonQuery(sqlQuery)          '< Error when inserting under ios when a resultset is already open
End Sub

It failed direct at the first entry, so exist=false. In this case i create a insert-query and send it as ExecNonQuery. The following error is exact in this line:

Unknown error calling sqlite3_step (5: database is locked) eu
DB Query: begin exclusive transaction
Unknown error finalizing or resetting statement (5: database is locked)
DB Query: begin exclusive transaction
Error occurred on line: 85 (database)
BeginTransaction error: database is locked
Stack Trace: (
  CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 1220052
  libobjc.A.dylib      objc_exception_throw + 60
  CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 126580
  B4i Example          -[B4ISQL BeginTransaction] + 156
  B4i Example          -[B4ISQL ExecNonQuery2::] + 116
  CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 1240532
  CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 9172
  B4i Example          +[B4I runDynamicMethod:method:throwErrorIfMissing:args:] + 1300
  B4i Example          -[B4IShell runVoidMethod] + 232
  B4i Example          -[B4IShell raiseEventImpl:method:args::] + 1800
 B4i Example          -[B4IShellBI raiseEvent:event:params:] + 1580
 B4i Example          +[B4IDebug delegate:::] + 80
 B4i Example          -[b4i_database _updateanimal:::::::::::::::::::::] + 1724
 B4i Example          -[ResumableSub_sync_syncNow resume::] + 12232
 CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 1240532
 CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 9172
 B4i Example          +[B4I runDynamicMethod:method:throwErrorIfMissing:args:] + 1300
 B4i Example          -[B4IShell runMethod:] + 448
 B4i Example          -[B4IShell raiseEventImpl:method:args::] + 2172
 B4i Example          -[B4IShellBI raiseEvent:event:params:] + 1580
 B4i Example          -[B4IDelegatableResumableSub resume::] + 380
 B4i Example          __21-[B4ICommon Sleep:::]_block_invoke + 48
 libdispatch.dylib    FF408738-D75B-3061-AD99-4A929C0162D2 + 10884
 libdispatch.dylib    FF408738-D75B-3061-AD99-4A929C0162D2 + 18460
 libdispatch.dylib    _dispatch_main_queue_callback_4CF + 884
 CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 670252
 CoreFoundation       A408A063-A023-3AAA-A674-0FF01607F818 + 645380
 CoreFoundation       CFRunLoopRunSpecific + 600
 GraphicsServices     GSEventRunModal + 164
 UIKitCore            F9E6CF6F-3B15-3C66-8C35-951AFD4D0CC4 + 12363612
 UIKitCore            UIApplicationMain + 168
 B4i Example          main + 120
 libdyld.dylib        1A4BE407-C230-309B-AEAB-8E9C62BD377A + 7416
)

I think the problem is because i have one open recordset - a recordset that query the local group of entrys i want to get from the server. It is possible to use lists instead of resultsets, but there are many in the project and maybe there is a easier solution. Mabye i can configure the transaction that it works like under android... Has anybody a idea?

Regards
Matthias

 

[aeric]

Please upload your project in zip.

 

[Erel]

result=sql1.ExecQuery("Select lu from farms where id='" & id.Replace("'","''") & "'")

Why??????????????

result=sql1.ExecQuery2("Select lu from farms where id= ?", Array As String(id))

 

[TheRealMatze]

result=sql1.ExecQuery("Select lu from farms where id='" & id.Replace("'","''") & "'")
Why??????????????

Because it´s easier to read (for me), especialy with muliple values. Maybe i change it later.

Please upload your project in zip.

unfortunately this is not possible... I can only describe the situation.
There is a open query while inserting. The open query has a subselect against the table i want to insert. As example:

select groupName, id, (select count(*) from groups) as gCnt, (select count(*) from entrys where groupID=groups.id) as eCnt from groups order by groupName COLLATE NOCASE asc

I want to insert into "entrys" while this select is open.

Now i have filled a list with the data from the select so i can loop the list and close the resultset before inserting. In this table i expect below than 2000 entrys, so i don´t think its so much slower than before - but it would be nice to understand why ios failed while android works.

 

[aeric]

You are only making things more complicated and doing something in unusual way. If you can't provide more info then I am not sure anyone can help but yourself.

 

[ilan]

Because it´s easier to read (for me), especialy with muliple values. Maybe i change it later.

so just a small tip from me.

If you see @Erel answering with a single '?' that means he is asking something.
2x '??" means: ehhh what?
3x '???' means: something is really bad with your code.
4x '????' means: you should really consider re-writing your code.
5 and more '?????...' means: you should really consider switching to QA

? ? ?

just kidding, don't take it personally

Anyway, i don't understand how it is more readable in your way than using a prepared statement

but even if you prefer your style you really should consider of using $""$ in your string like this:

sql1.ExecQuery($"Select lu from farms where id='${id.Replace("'","''")}'"$)

so when i use sqlite in b4i i do something like this:

        Dim rs As ResultSet = sql1.ExecQuery($"Select lu from farms where id='${id.Replace("'","''")}'"$)
        Do While rs.NextRow
'...
        Loop
        rs.Close

so the resultset is always closed like you are doing it in your code. so why are you saying that it is still open?

< Error when inserting under ios when a resultset is already open

    result=sql1.ExecQuery("Select lu from farms where id='" & id.Replace("'","''") & "'")
    
    If result.NextRow Then
        exist=True
        stlu=result.GetInt("lu")
        Else
        exist=False
    End If
    
    result.close  'HERE YOU ARE CLOSING THE RESULTSET
    
    If exist=False Then
          SQLE("Insert into ...")          '< This function is called
    Else
    ...
    end if

 

[TheRealMatze]

Anyway, i don't understand how it is more readable in your way than using a prepared statement

You are right, a insert statement is not better readable in both ways, but a update with multiple condition is easier to read, for example "update tab set a=1, b=2, c=3, d=4 where e=5 and f in(6,7)" But as i said, when the statement is complete i can change it What is the benefit of $""$ ?

so the resultset is always closed like you are doing it in your code. so why are you saying that it is still open?

Sorry for that confusion. It was late and i was focused on the insert-sub. In the second post i tried to correct it.
Indeed there is a open resultset. In this outer loop i get the id for the next fetch from the server. The resulting JSON is the base for the Update / Insert / Delete which raises the error. But the main problem is that the outer loop has a subselect on the table i want to insert to count the current entrys of this part.
Android has no problem with the same query, that´s what i´m not understand. In the end, as i said, i´ve filled a list with the outer query, close the resultset and loop through the list.

Regards
Matthias

 

[ilan]

using $””$ has lot of benefits. It is shorter and easier to read and safe you lot of headache with “”””

from the logs it looks like you are trying to make a query while your db is or closed or already performing a query.

in sqlite when u perform a query your db is locked. Not like remote db where u can perform multiple queries at the same time.

if you could create a simple project where u can recreate the error it could be very helpful for us to understand where the bug is.

 

[TheRealMatze]

I don´t think it´s a bug, but i´m wondering why sqlight on android handles this situation different to sqlight on ios. It´s not a big deal in this case (this situation is only one time in the app), but in other data-structures it might be a problem if you notice it too late... A missing method like rowCount is easy to find, but a different handling of the sql-command can easily end in massive headache

 

[aeric]

iOS -> Objective-C
Android -> Java
Sure there are differences between the platforms