Android Question DoEvents needed?

advansis

advansis

Active Member
Licensed User
Longtime User
Hi guys,
I have an Activity (called "DashBoard") and a Class (called "appClass").
In appClass there is resumable sub called "FileImport": it processes a CSV file.
During the file processing I call (by Callsub statement) the "ShowStatus" sub that displays a string in a label (lblStatus).
If don't put the DOEVENTS command inside the "ShowStatus", the label is not updated.
Here's the relevant code, the process starts clicking a button (BTN_Click):

DashBoard: 
Sub BTN_Click
    Dim AC As App_Class: AC.Initialize
    wait for (AC.FileImport(Me)) complete (success As Boolean)
End Sub


Sub ShowStatus(Msg As String,RowNum As Int)
    If RowNum>0 Then
        lblStatus.Text="Processing row: " & RowNum
    else if Msg.Length>0 Then
        lblStatus.Text=Msg
    Else
        lblStatus.Text=""
    End If
    ' lblStatus.Invalidate ' <- NOT WORKING
    ' Sleep(0) ' <- NOT WORKING
    DoEvents ' WORKS !!!
End Sub

AppClass: 
Sub Class_Globals
    Dim DummyAct As Activity
End Sub

...

Sub DataImport(Caller As Object) As ResumableSub
    ...
    Dim NRows as int=0
    Do While (...)
        ...
        NRows=NRows+1
        if NRows mod 100=0 Then ' Update the Status only each 100 rows
            If SubExists(Caller,"ShowStatus") Then CallSub3(Caller,"ShowStatus","",NRows)
        End If
    Loop
    If SubExists(Caller,"ShowStatus") Then CallSub3(Caller,"ShowStatus","",0) ' Clear Status
    Return true
End Sub

During debugging, if I put a breakpoint in ShowStatus sub, the label is updated...
I know DoEvents should not be used, but how can I achieve what I need ? Thanks in advance

PS. Tried also CallSubDelayed3 (I don't think is relevant), with no luck...
 
Sort by date Sort by votes
OliverA

OliverA

Expert
Licensed User
Longtime User
Try CallSubDelayed3 + Sleep?
B4X: 
Sub DataImport(Caller As Object) As ResumableSub
    ...
    Dim NRows as int=0
    Dim showStatus as Boolean = SubExists(Caller, "ShowStatus")
    Do While (...)
        ...
        NRows=NRows+1
        if NRows mod 100=0 Then ' Update the Status only each 100 rows
            If showStatus Then CallSubDelayed3(Caller,"ShowStatus","",NRows)
            Sleep(0) ' Give event queue a chance to process other events (including above's ShowStatus event)
                          '  and keep Android from thinking you are locked up
        End If
    Loop
    If showStatus Then
       CallSubDelayed3(Caller,"ShowStatus","",0) ' Clear Status
       Sleep(0)
    End If
    Return true
End Sub
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
OliverA said:
Try CallSubDelayed3 + Sleep?
B4X: 
Sub DataImport(Caller As Object) As ResumableSub
    ...
    Dim NRows as int=0
    Dim showStatus as Boolean = SubExists(Caller, "ShowStatus")
    Do While (...)
        ...
        NRows=NRows+1
        if NRows mod 100=0 Then ' Update the Status only each 100 rows
            If showStatus Then CallSubDelayed3(Caller,"ShowStatus","",NRows)
            Sleep(0) ' Give event queue a chance to process other events (including above's ShowStatus event)
                          '  and keep Android from thinking you are locked up
        End If
    Loop
    If showStatus Then
       CallSubDelayed3(Caller,"ShowStatus","",0) ' Clear Status
       Sleep(0)
    End If
    Return true
End Sub
Click to expand...

I'm sorry, Adding CallSubDelayed and Sleep(0) doesn't work...
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
Erel said:
DoEvents is never needed and should never be used.

How large is the CSV file? How long does it take to process it? You should post the relevant code.
Make sure to test performance in release mode.
Click to expand...

Erel, the problem is not performance, (I can improve it, I think) but providing the user a feedback...
Reading CSV and inserting in a Sqllite "staging" table is quite fast: 4-5 seconds for a 4Mb file, containing 18k rows (using LoadCSV2 of StringUtils).
The time to validate, normalize and adjust data, after reading, is much slower: 30-40 seconds (I use sqllite-transactions).
The latter process performs several INSERT/UPDATE commands in some tables.
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
@Erel, when I have large queries and several commands, I use ExecQueryAsync and ExecNonQueryBatch... I use them out of loops.
In my case I have several single commands operations inside (my loops), I think I should not use async methods.
1) I'm wrong and shall I use ExecQueryAsync for each iteration, also for retreiving values (ExecQuerySingleResult) ?
2) I perform select and then insert/updates to retrieve and update data during the loop and cannot use ExecNonQueryBatch because data in my tables change
*) The main problem is that sqlite doesn't allow to execute updates on joins... and cannot use "columns-list" updates because version should be 3.15.0 or above
:(
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
Erel said:
You are asking about DoEvents. We can learn from this question that the task hangs the main thread for too long. The solution is simple. Use async methods.
Click to expand...
Ok I will use asyncronous SQL operations (I already use them for the main queries outside the loops), but consider that the task itself (DataImport) is called asyncronously, the main thread DOESN'T HANG and works correctly. The process could be long, but doesn't matter, it runs in the background . The problem IS NOT DoEvents, but the fact that the status label isn't refreshed when it is supposed to, even using sleep(0)
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
peacemaker said:
@advansis, async methods with Waiting For a result inside your loop.
Click to expand...
You mean I shall use ExecQuerySingleResult instead of ExecQuerySingleResult, inside my resumable sub?
And shall I use ExecNonQueryBatch (I cannot queue more than one statement) for each step of the loop?
Ok, will try, but I think I will add a lot of time-waste
 
Upvote 0
advansis

advansis

Active Member
Licensed User
Longtime User
peacemaker said:
SQL.ExecQueryAsync and get your single value from the returned cursor.
Click to expand...

Erel said:
You are asking about DoEvents. We can learn from this question that the task hangs the main thread for too long. The solution is simple. Use async methods.
Click to expand...

OK, let's make it simpler (?!?!?).
I "translated" my code into something less complex... My code sounds like the following.
I don't know if it real works, because I translated table/field names... but the command's structure is the same
Of course DBase is a SQL object, GetLastIdentity is a function that returns: DBase.ExecQuerySingleResult("SELECT last_insert_rowid()")
Fields "id_category" and "id_supplier" in table "temp_import" are not filled during CSV import.
*(for those who ask) Fields order in sql is important: it is done to have only one "Pars" variable both in Inserting and in Updating.

Shall I change all my ExecquerySingleResult in ExecQueryAsync and ExecNonQuery in ExecNonQueryBatch ? Wouldn't it be useless (they are single operations and are inside a resumablesub ...) ?
Thanks a lot for your help and your time, I really appreciate it !


This is the modified version of ShowStatus procedure:

B4X: 
Sub ShowStatus(Msg As String,RowNum As Int)
    If RowNum>0 And Msg.Length>0 Then
        lblStatus.Text=Msg.Replace("{0}", RowNum)
    else if Msg.Length>0 Then
        lblStatus.Text=Msg.Replace("{0}","")
    Else
        lblStatus.Text=""
    End If
End Sub

This is the database definition:
B4X: 
Sub CreateDB
    DBase.ExecNonQuery("CREATE TABLE IF NOT EXISTS temp_import (code TEXT PRIMARY KEY" & _
    ",id_category INTEGER,id_supplier INTEGER" & _
    ",cat_code TEXT,cat_name TEXT,sup_code TEXT,sup_name TEXT,sup_address TEXT,sup_city TEXT,sup_state TEXT" & _
    ",name TEXT,flags TEXT)")

    DBase.ExecNonQuery("CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY key" & _
    ",code TEXT,name text)")

    DBase.ExecNonQuery("CREATE TABLE IF NOT EXISTS suppliers (id INTEGER PRIMARY key" & _
    ",code TEXT,name text,address TEXT,city TEXT,state TEXT)")

    DBase.ExecNonQuery("CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY key" & _
    ",id_category INTEGER REFERENCES categories(id) ON UPDATE CASCADE ON DELETE SET NULL" & _
    ",id_supplier INTEGER REFERENCES suppliers(id) ON UPDATE CASCADE ON DELETE SET NULL" & _
    "code TEXT,name TEXT,flags TEXT)")

    ' Create INDEXES...
End Sub

This is the schema of the import-process:
B4X: 
Sub DataImport(Caller As Object) As ResumableSub

    ' The following procedure loads the CSV data into "temp_import" table
    wait for (ImportIntoTempTable) Complete (success As Boolean)
    If success=False Then Return False

    ' Processing Categories
    Dim SqlFind As String="SELECT distinct cat_code,cat_name FROM temp_import"
    Dim SqlIns As String="INSERT OR IGNORE INTO categories (code,name) SELECT ?,? WHERE NOT EXISTS (SELECT * FROM categories WHERE code=?)"
    Dim SqlUpd As String="UPDATE categories SET code=?,name=? WHERE code=?"
    Dim RS As ResultSet
    Dim Pars As List,NRows As Int=0
    Dim Id_Prod As Int,Id_Cat As Int=0,Id_Sup As Int=0
    Dim CodProd As String="",CodCat As String="",CodSup As String=""
    Dim StatusText As String=""

    Dim HasShowStatus As Boolean=SubExists(Caller,"ShowStatus")

    StatusText="Importing categories: {0}"
    If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)

    Dim SenderFilter As Object = DBase.ExecQueryAsync("SQL", SqlFind, Null)
    Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
    If success=False Then Return False

    DBase.BeginTransaction
    Do While RS.NextRow
        CodCat=RS.GetString("cat_code")
        Id_Cat=DBase.ExecquerySingleResult("SELECT id FROM categories WHERE code=?",Array As String(CodCat))
        Pars.Initialize
        For i=0 To RS.ColumnCount-1
            Pars.Add(RS.GetString2(i))
        Next
        Pars.Add(CodCat)
        If Id_Cat<>0 Then
            DBase.ExecNonQuery2(SqlUpd,Pars)
        Else
            DBase.ExecNonQuery2(SqlIns,Pars)
            Id_Cat=GetLastIdentity ' --> DBase.ExecQuerySingleResult("SELECT last_insert_rowid()")
        End If
        DBase.ExecNonQuery2("UPDATE temp_import SET id_category=? WHERE cat_code=? AND id_category IS NULL",Array As String(Id_Cat,CodCat))
        NRows=NRows+1
        If NRows Mod 10=0 Then
            If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
        End If
    Loop
    RS.Close
    DBase.TransactionSuccessful
    DBase.EndTransaction


    StatusText="Importing suppliers: {0}"
    If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)

    SqlFind ="SELECT distinct sup_code,sup_name FROM temp_import"
    SqlIns ="INSERT OR IGNORE INTO suppliers (code,name,address,city,state) SELECT ?,?,?,?,? WHERE NOT EXISTS (SELECT * FROM suppliers WHERE code=?)"
    SqlUpd ="UPDATE suppliers SET code=?,name=?,address=?,city=?,state=? WHERE code=?"

    SenderFilter = DBase.ExecQueryAsync("SQL", SqlFind, Null)
    Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
    If success=False Then Return False

    DBase.BeginTransaction
    NRows=0
    Do While RS.NextRow
        CodSup=RS.GetString("sup_code")
        Id_Sup=DBase.ExecquerySingleResult("SELECT id FROM suppliers WHERE code=?",Array As String(CodSup))
        Pars.Initialize
        For i=0 To RS.ColumnCount-1
            Pars.Add(RS.GetString2(i))
        Next
        Pars.Add(CodSup)
        If Id_Sup<>0 Then
            DBase.ExecNonQuery2(SqlUpd,Pars)
        Else
            DBase.ExecNonQuery2(SqlIns,Pars)
            Id_Sup=GetLastIdentity
        End If
        DBase.ExecNonQuery2("UPDATE temp_import SET id_supplier=? WHERE sup_code=? AND id_supplier IS NULL",Array As String(Id_Sup,CodSup))
        NRows=NRows+1
        If NRows Mod 10=0 Then
            If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
        End If
    Loop
    RS.close
    DBase.TransactionSuccessful
    DBase.EndTransaction

    StatusText="Processing products: {0}"
    If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,0)

    SqlFind="SELECT id_category,id_supplier,name,flags,code FROM temp_import"
    SqlIns ="INSERT OR IGNORE INTO products(id_category,id_supplier,name,flags,code) VALUES(?,?,?,'A',?)"
    SqlUpd ="UPDATE products SET id_category=?,id_supplier=?,flags=?,code=? WHERE code=?"
    DBase.ExecNonQuery("UPDATE products SET flags='X'") ' Mark all products as "Inactive"

    SenderFilter = DBase.ExecQueryAsync("SQL", SqlFind, Null)
    Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, RS As ResultSet)
    If success=False Then Return False

    NRows=0
    Do While RS.NextRow
        CodProd=RS.GetString("code")
        Id_Prod=DBase.ExecQuerySingleResult2("SELECT id FROM products WHERE code=?",Array As String(CodProd))

        Pars.Initialize
        For i=0 To RS.ColumnCount-1
            Pars.Add(RS.GetString2(i))
        Next
        Try
            If Id_Prod<>0 Then
                DBase.ExecNonQuery2(SqlUpd,Pars)
            Else
                DBase.ExecNonQuery2(SqlIns,Pars)
            End If
        Catch
            Log(LastException)
        End Try
        NRows=NRows+1
        If NRows Mod 10=0 Then
            If HasShowStatus Then CallSub3(Caller,"ShowStatus",StatusText,NRows)
        End If
    Loop
    RS.close
    DBase.TransactionSuccessful
    DBase.EndTransaction
    DBase.ExecNonQuery("DELETE FROM products WHERE flags='X'") ' Delete all old products
    If HasShowStatus Then CallSub3(Caller,"ShowStatus","",0)
    Return True
End Sub
 
Last edited:
Upvote 0
peacemaker

peacemaker

Expert
Licensed User
Longtime User
Are you sure that Caller is correct (and its sub "ShowStatus" exists) ?
 
Upvote 0
peacemaker

peacemaker

Expert
Licensed User
Longtime User
Sleep(1) at the end of ShowStatus? (not zero)
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
The issue is still that your loops are 100% blocking! Nothing in those loops gives the system a breather to update your label. Both SQL calls made in those loops block. Let's say you import 1000 statements. The loop will process 1000 times, creating 2000 blocking SQL statements and 1000 blocking CallSub's. In your sub to update the label, setting the text on the label is not like calling CallSub to update the text, but it creates an Event (sorta like CallSubDelayed). The problems is that your loop (and the sub that displays the text) does not give you app any chance to process any events. Therefore, your label is never updated. That is why DoEvents works (but don't use it). It stops your loop and gives the system a chance to process events, including the event that updates your label. Do not use ExecQuerySingleResult and do not use ExecNonQuery2 in the loop. Replace them with async calls! Plus since you call TransactionSuccessful outside the loop anyways, it makes more sense to use AddNonQueryToBatch inside the loop and ExecNonQueryBatch outside of the loop (it will then handle the transaction for you). The ExecQuerySingleResult needs to be replaced with ExecQueryAsync. Even then you may need to add a Sleep(with some value) to let the system catch up with the events.
 
Upvote 0
You must log in or register to reply here.
Top