iOS Question SQLite -> different behaviour under ios than android

TheRealMatze · Dec 27, 2021

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

B4X:

    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

B4X:

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:

B4X:

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 · Dec 28, 2021

Please upload your project in zip.

Erel · Dec 28, 2021

B4X:

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

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

B4X:

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

TheRealMatze · Dec 28, 2021

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:

SQL:

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 · Dec 28, 2021

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 · Dec 28, 2021

TheRealMatze said:
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:

B4X:

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

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

B4X:

        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

B4X:

    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 · Dec 29, 2021

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 · Dec 29, 2021

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 · Dec 29, 2021

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 · Dec 29, 2021

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

iOS Question SQLite -> different behaviour under ios than android

TheRealMatze

Active Member

aeric

Expert

Erel

B4X founder

TheRealMatze

Active Member

aeric

Expert

ilan

Expert

TheRealMatze

Active Member

ilan

Expert

TheRealMatze

Active Member

aeric

Expert

iOS Question SQLite -> different behaviour under ios than android

Active Member

Expert

B4X founder

Active Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Privacy & Transparency

Privacy & Transparency