Android Tutorial [B4X] SQL with Wait For

Status
Not open for further replies.

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

Well-Known Member
Licensed User
Nice Options.
Thanks Erel
 

Kwame Twum

Active Member
Licensed User
This doesn't seem to work with SQLCipher.
Consider the following statement with regards to your first post
B4X:
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:
B4X:
 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

Administrator
Staff member
Licensed User
ExecNonQueryBatch doesn't report the progress.

You can show a progress dialog:
B4X:
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.

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

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

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

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

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.

Well-Known 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.
ok thanks you
 

ttsolution

Member
Licensed User
Hi All,

What am I doing wrong in this code? it return Success=False
B4X:
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

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.

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

Well-Known Member
Licensed User
The following line:
B4X:
        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
 

jinyistudio

Well-Known Member
Licensed User
Hi,

What is the use of sendfilter ?
 

imbault

Well-Known 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:
B4X:
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:
B4X:
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):
B4X:
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
 
Status
Not open for further replies.
Top