Android Tutorial [B4X] SQL with Wait For

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:

For i = 1 To 1000
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
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:

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):

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

 

[Swissmade]

Nice Options.
Thanks Erel

 

[Kwame Twum]

This doesn't seem to work with SQLCipher.
Consider the following statement with regards to your first post

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
5. Dim ob as Object = sq.ExecNonQueryBatch("sq5")    'sq is an instance of SQLCipher
6. Wait For sq5_NonQueryComplete (Success As Boolean)
7. Log("NonQuery: " & Success)

I get: Cannot assign void value on line 5

When I remove the assignment part:

 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?

 

[Erel]

The current SQLCipher library doesn't support asynchronous methods (with wait for or without it).

 

[molder26]

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

 

[Erel]

ExecNonQueryBatch doesn't report the progress.

You can show a progress dialog:

ProgressDialogShow2("Inserting data...", False)
For i = 1 To 1000
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)
ProgressDialogHide

 

[Roberto P.]

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

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: " & LastException, True)
    End Try
   
    Log("stop " & DateTime.time(DateTime.Now) )
   
    aRecord.Close

new query

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

 

[Erel]

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".

Is it normal that new queries do not work within modules?

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

 

[Roberto P.]

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.

ok thanks you

 

[ttsolution]

Hi All,

What am I doing wrong in this code? it return Success=False

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

 

[Erel]

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

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

 

[MrKim]

The following line:

        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

 

[Erel]

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

sql1.InitializeAsync("Connect", ...)
Wait For Connect_Ready (Success As Boolean)
If Success Then
 ...
End If

 

[jinyistudio]

Hi,

What is the use of sendfilter ?

 

[Erel]

Please start a new thread for this question.

 

[imbault]

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:

For i = 1 To 1000
   sql.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array(Rnd(0, 100000)))
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:

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):

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

That's very interesting for long queries I have on SQL server
But sorry, that doesn't work on B4J using JDBC and querying Sql Server with sqljdbc42.jar
Maybe it's ok for SQLite...

Patrick

PAtrick

 

[Erel]

I don't see how we can help you with the information you provided.
If you want any assistance then start a new thread and explain what you are doing and what is not working.