Android Tutorial [B4X] SQL with Wait For

Discussion in 'Tutorials & Examples' started by Erel, May 15, 2017.

  1. Erel

    Erel Administrator Staff Member Licensed User

    The new resumable subs feature, introduced in B4J v5.50, B4i v4.00 and B4A v7.00 (to be soon released), makes it simpler to work with large data sets with minimum effect on the program responsiveness.

    The new standard way to insert data is:
    Code:
    For i = 1 To 1000
       
    sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)"Array(Rnd(0100000)))
    Next
    Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    The steps are:
    - Call AddNonQueryToBatch for each commands that should be issued.
    - Execute the commands with ExecNonQueryBatch. This is an asynchronous method. The commands will be executed in the background and the NonQueryComplete event will be raised when done.
    - This call returns an object that can be used as the sender filter parameter. This is important as there could be multiple background batch executions running. With the filter parameter the event will be caught by the correct Wait For call in all cases.
    - Note that SQL.ExecNonQueryBatch begins and ends a transaction internally.

    Queries

    In most cases the queries will be fast and should therefore be issued synchronously with SQL.ExecQuery2. However if there is a slow query then you should switch to SQL.ExecQueryAsync:
    Code:
    Dim SenderFilter As Object = sql.ExecQueryAsync("SQL""SELECT * FROM table1"Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
       
    Do While rs.NextRow
         
    Log(rs.GetInt2(0))
       
    Loop
       rs.Close
    Else
       
    Log(LastException)
    End If
    As in the previous case, the ExecQueryAsync method returns an object that is used as the sender filter parameter.

    Tips:
    1. ResultSet type in B4A extends the Cursor type. You can change it to Cursor if you prefer. The advantage of using ResultSet is that it is compatible with B4J and B4i.
    2. If the number of rows returned from the query is large then the Do While loop will be slow in debug mode. You can make it faster by putting it in a different sub and cleaning the project (Ctrl + P):
    Code:
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)
    If Success Then
       WorkWithResultSet(rs)
    Else
       
    Log(LastException)
    End If
    End Sub

    Sub WorkWithResultSet(rs As ResultSet)
       
    Do While rs.NextRow
         
    Log(rs.GetInt2(0))
       
    Loop
       rs.Close
    End Sub
    This is related to a debugger optimization that is currently disabled in resumable subs.
    The performance of both solutions will be the same in release mode.

    B4J

    - Requires jSQL v1.50+ (https://www.b4x.com/android/forum/threads/updates-to-internal-libaries.48274/#post-503552).
    - Recommended to set the journal mode to WAL: https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content
     
  2. swissmade

    swissmade Active Member Licensed User

    Nice Options.
    Thanks Erel
     
  3. Kwame Twum

    Kwame Twum Active Member Licensed User

    This doesn't seem to work with SQLCipher.
    Consider the following statement with regards to your first post
    Code:
    1.  For i = 0 To cl.Size - 1  'cl is a list
    2.    Dim m As Map = cl.Get(i)
    3.    sq.AddNonQueryToBatch("INSERT INTO user VALUES (???)"Array As Object(m.Get("id"),m.Get("name"),m.Get("age")))     'sq is an instance of SQLCipher
    4.  Next
    5Dim ob as Object = sq.ExecNonQueryBatch("sq5")    'sq is an instance of SQLCipher
    6Wait For sq5_NonQueryComplete (Success As Boolean)
    7Log("NonQuery: " & Success)
    I get: Cannot assign void value on line 5

    When I remove the assignment part:
    Code:
    1. sq.ExecNonQueryBatch("sq5")
    the error goes away but sq5_NonQueryComplete is never called and I realize nothing was stored in the DB.
    What could possibly be wrong?
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    The current SQLCipher library doesn't support asynchronous methods (with wait for or without it).
     
    Last edited: May 23, 2018
    Kwame Twum likes this.
  5. molder26

    molder26 Member Licensed User

    If i want to show the progress results of sql transaction, i should use this wait for?
    Regards
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    ExecNonQueryBatch doesn't report the progress.

    You can show a progress dialog:
    Code:
    ProgressDialogShow2("Inserting data..."False)
    For i = 1 To 1000
       
    sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)"Array(Rnd(0100000)))
    Next
    Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    ProgressDialogHide
     
    jinyistudio, MarcoRome and RauchG like this.
  7. Roberto P.

    Roberto P. Well-Known Member Licensed User

    Hello Erel
    I'm doing comparative tests to verify the performance differences of queries to load data from a table with about 38,000 elements.

    I measured the data upload time and it is practically identical (average 3 seconds). During loading, no graphic interface is displayed.

    Here are the queries

    Old query

    Code:
    sQuery.Append("SELECT Item AS Articolo, Description AS Descrizione, IsGood As Merce, ifnull(BaseUoM, '') AS BaseUoM, ifnull(BasePrice, '') AS PrezzoBase, ")
        sQuery.Append(
    "ifnull(ItemType, '') AS Tipo, ifnull(CommodityCtg, '') AS Merceologica, ifnull(HomogeneousCtg, '') AS Omogenea, ifnull(Producer, '') AS Produttore ")
        sQuery.Append(
    "FROM MA_Items WHERE Disabled = '0' AND IsGood = '1' ")
       
        sQuery.Append(
    " ORDER BY Item")
    '    Log(sQuery.ToString)
       
        
    Try
           
            
    Log("start " & DateTime.time(DateTime.Now) )
           
            aRecord = UserAndDeviceData.m_DB.ExecQuery( sQuery )
           
            
    For i = 0 To aRecord.RowCount - 1
                    aRecord.Position = i
               
                sCodice        =     aRecord.GetString(
    "Articolo")
           
                aLista.Add(sCodice & 
    " # " & aRecord.GetString("Descrizione") & " # " & aRecord.GetString("Tipo") )
               
            
    Next
           
            
    Log("Numero articoli caricati " & aLista.Size)
           
            mac_Articoli.SetItems(aLista)
           
        
    Catch
            
    ToastMessageShow("Si è verificato un errore nella lettura della tabella Articoli: " & LastExceptionTrue)
        
    End Try
       
        
    Log("stop " & DateTime.time(DateTime.Now) )
       
        aRecord.Close

    new query

    Code:
    Dim sQuery As StringBuilder
        sQuery.Initialize
       
        sQuery.Append(
    "SELECT Item AS Articolo, Description AS Descrizione, IsGood As Merce, ifnull(BaseUoM, '') AS BaseUoM, ifnull(BasePrice, '') AS PrezzoBase, ")
        sQuery.Append(
    "ifnull(ItemType, '') AS Tipo, ifnull(CommodityCtg, '') AS Merceologica, ifnull(HomogeneousCtg, '') AS Omogenea, ifnull(Producer, '') AS Produttore ")
        sQuery.Append(
    "FROM MA_Items WHERE Disabled = '0' AND IsGood = '1' ")
       
        sQuery.Append(
    " ORDER BY Item")
       
        
    Log("start " & DateTime.time(DateTime.Now) )
       
       
        
    Dim SenderFilter As Object = UserAndDeviceData.m_DB.ExecQueryAsync("SQL", sQuery.ToString, Null)
       
        
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As ResultSet)

        
    If Success Then
           
            
    Log("Numero articoli " & rs.RowCount)
       
            
    Do While rs.NextRow
                
    Log("Articolo " & rs.GetString("Articolo"))
            
    Loop
            rs.Close
        
    Else
            
    Log(LastException)
        
    End If
       
        
    Log("stop " & DateTime.time(DateTime.Now) )
    Is it normal that new queries do not work within modules?

    Thank you
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    The async methods are not faster than the synchronous methods. However they are executed in the background so they don't affect the main thread and don't cause the app to "freeze".

    Static code modules (in B4A) cannot handle events or resumable subs. You can use a class instead.
     
  9. Roberto P.

    Roberto P. Well-Known Member Licensed User

    ok thanks you
     
  10. ttsolution

    ttsolution Member Licensed User

    Hi All,

    What am I doing wrong in this code? it return Success=False
    Code:
    For i=0 To L1.Size-1
                 m=L1.get(i)
                 
    Try
                   mSQL=
    "INSERT INTO Customers Values (CustomerId,CustomerCode,CustomerName)"
                   L.Clear
                   L.Add(m.get(
    "CustomerId"))
                   L.Add(m.get(
    "CustomerCode"))
                   L.Add(m.get(
    "CustomerName"))
                   Starter.SQL.AddNonQueryToBatch(mSQL,L)
                 
    Catch
                   
    ToastMessageShow(LastException.Message,True)
                 
    End Try
               
    Next
               
    Dim SenderFilter As Object = Starter.SQL.ExecNonQueryBatch("SQL")
               
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
               
    Msgbox("NonQuery: " & Success,"")
    Many thanks for your help
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    Don't use msgbox to check the status. Use Log. If you do want to show a msgbox then call MsgboxAsync.

    Code:
    For Each m As Map in L1
     Starter.SQL.AddNonQueryToBatch(
    "INSERT INTO Customers Values (?,?,?)"Array(m.Get("CustomerId"), m.Get("CustomerCode"), m.Get("CustomerName")))
    Next
    Dim SenderFilter As Object = Starter.SQL.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log(Success)
    If Success = False Then
     
    Log(LastException)
    End If
     
  12. MrKim

    MrKim Active Member Licensed User

    The following line:
    Code:
    Dim SenderFilter As Object = sql1.InitializeAsync("Connect""net.sourceforge.jtds.jdbc.Driver" ,"jdbc:jtds:sqlserver://192.168.1.70:49959;databaseName=SkDataA1;user=skdata;password=shopkeeper;loginTimeout=10""skdata",  "shopkeeper")
    Gives "Cannot assign void value" and won't compile.
    I am using the JSQL Lib in B4A. The line runs fine on it's own.
    The examples you give above compile just fine.

    Thanks
     
  13. Erel

    Erel Administrator Staff Member Licensed User

    You don't need the "SenderFilter" parameter with InitializeAsync.

    Code:
    sql1.InitializeAsync("Connect", ...)
    Wait For Connect_Ready (Success As Boolean)
    If Success Then
     ...
    End If
     
    MrKim likes this.
  14. jinyistudio

    jinyistudio Active Member Licensed User

    Hi,

    What is the use of sendfilter ?
     
  15. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread for this question.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice