Android Question B4A Wait For usage

[David Elkington]

Hi All,

I have an app which is bringing data back from an SQL server to update local tables (SQLite) so that the app can be used offline. I have about 20 tables that need updating when the app starts, and I have been using a sleep statement in between each one to reduce the occurrences of the job done event being overwritten between statements.

I was wondering if it was possible to speed things up as I have so many tables to update, currently it takes over 30 seconds to complete. Admittedly it is in the background, but the chances of a table being updated when in use is a worry.

Here is how I am calling the events... This is just a quarter of the calls.

Sub UpdateStaticData_RA_Dynamic
    Try
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","aqr")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","cli")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","con")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","eqt")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","nmt")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","bio")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","cls")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","haz")
        Sleep(1000)
        'CallSub2(Me,"RefreshStaticDataDB","tal")
        'CallSub2(Me,"RefreshStaticDataDB","tir")
        CallSub2(Me,"RefreshStaticDataDB","tco")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","tsp")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","stt")
        Sleep(1000)
        CallSub2(Me,"RefreshStaticDataDB","ppe")
        
        
    Catch
        Log(LastException)
    End Try

Then the Refresh Static does the following....

If strSection="aqr" Then
       Dim cmd As DBCommand = CreateCommandIn("select_static_aqr_34567", Null)
       Wait For (req.ExecuteQuery(cmd, 0, "static_aqr")) JobDone(j As HttpJob)
   End If
  
   If strSection="cli" Then
       Dim cmd As DBCommand = CreateCommandIn("select_static_cli_34567", Null)
       Wait For (req.ExecuteQuery(cmd, 0, "static_cli")) JobDone(j As HttpJob)
   End If
  
   If strSection="con" Then
       Dim cmd As DBCommand = CreateCommandIn("select_static_con_34567", Null)
       Wait For (req.ExecuteQuery(cmd, 0, "static_con")) JobDone(j As HttpJob)
   End If

..... ETC

And in the job done, I call the update for the sqlite database where it drops the table and recreates, and then adds the records...

else if sf.Left(Job.Tag,7)="static_" Then
        CallSub2(Me,"JobDoneStat",Job)
        
        else if sf.Left(Job.Tag,7)="static-" Then
        CallSub2(Me,"JobDoneJob",Job)

Which I had to split as the sub was getting too big.

reqManager.HandleJobAsync(Job, "req")
       Wait For (reqManager) req_Result(res As DBResult)
                  
    
        If res.Tag="static-jobs" Then
           'work with result
               Dim m As Map
               Dim listofmaps As List
              If Job.Success Then

                   Dim m As Map
                   Dim listofmaps As List
                   If res.Rows.Size>0 Then
                       'We know we have data, remove the job data table and recreate
                       'SQLJobs.ExecNonQuery("Delete from job")
                       ChkndropJob("job")
                    'If SQLJobs.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='job'") = 1 Then
                    '    strSQL = "DROP TABLE 'job'"
                    '    SQLJobs.ExecNonQuery(strSQL)
                    'End If

                    strSQL ="CREATE TABLE 'job' ( `job_ID` INTEGER NOT NULL, `job_type` INTEGER NOT NULL, `job_status_ID` INTEGER NOT NULL, `job_status` TEXT, `job_object_type_ID` INTEGER NOT NULL, `job_object_ID` INTEGER, `job_object_name` TEXT, `job_allocated_ID` INTEGER, `job_allocated` TEXT, `job_department_ID` INTEGER, `job_department` TEXT, `job_source_ID` INTEGER, `job_source` TEXT, `job_short_desc` TEXT, `job_long_desc` TEXT, `job_created` TEXT, `job_required` TEXT, `job_question_set_ID` INTEGER, `job_warning` TEXT ,`job_depot_ID` INTEGER, PRIMARY KEY(`job_ID`) )"
                    SQLJobs.ExecNonQuery(strSQL)
                  
                   'Add the new data
                   For myrows = 0 To res.Rows.Size - 1
                           Dim Record() As Object = res.Rows.Get(myrows)
                        
                        'Here we are going to get all the data and post it into the table
                        m.Initialize
                        listofmaps.Initialize
                        
                        m.Put("job_ID",Record(0))
                        m.Put("job_type",Record(1))
                        m.Put("job_status_ID",Record(2))
                        m.Put("job_status",Record(3))
                        m.Put("job_object_type_ID",Record(4))
                        m.Put("job_object_ID",Record(5))
                        m.Put("job_object_name",Record(6))
                        m.Put("job_allocated_ID",Record(7))
                        m.Put("job_allocated",Record(8))
                        m.Put("job_department_ID",Record(9))
                        m.Put("job_department",Record(10))
                        m.Put("job_source_ID",Record(11))
                        m.Put("job_source",Record(12))
                        m.Put("job_short_desc",Record(13))
                        m.Put("job_long_desc",Record(14))
                        m.Put("job_created",Record(15))
                        m.Put("job_required",Record(16))
                        m.Put("job_question_set_ID",Record(17))
                        m.Put("job_warning",Record(18))
                        m.Put("job_depot_ID",Record(19))
                        
                        listofmaps.Add(m)
                        DBUtils.InsertMaps(SQLJobs,"Job",listofmaps)
                        
                       Next
                   Else
                       ToastMessageShow("No Job data",False)
                End If
               'Job.Release
               Else
                 ToastMessageShow("Job data could not be updated",False)
            End If
            
            
        else if res.Tag="static-js" Then

                    If Job.Success Then

It seems to work but feels clunky, I don't know how to call the event, make it pause until the SQLite table is up to date and then immediately move onto the next statement, so an help would be greatly appreciated.

David

 

[DonManfred]

https://www.b4x.com/android/forum/threads/b4x-resumable-subs-sleep-wait-for.78601/

 

[David Elkington]

Yes, I went through that tutorial but I am confused on how to call the update records sub in a way that pauses the job done event, I think I have to pass back something on complete, but can’t work it out.

 

[DonManfred]

Also see this: https://www.b4x.com/android/forum/threads/b4x-resumable-subs-that-return-values-resumablesub.82670/

 

[David Elkington]

Ah, many thanks, that is what I am looking for. I was concerned that I needed to return a value but I read again and I can just return a null onto an object.