B4J Question MySQL AddNonQueryToBatch

aaronk

Well-Known Member
Licensed User
Longtime User
Hi,

I am using B4J as a non-UI app. I am using SQL as per below:

B4X:
Sub Process_Globals
    Dim MySQL As SQL
End Sub

#AdditionalJar: mysql-connector-java-5.1.27-bin.jar

Sub AppStart (Args() As String)
    MySQL.Initialize2("com.mysql.jdbc.Driver","jdbc:mysql://localhost/database?characterEncoding=utf8","usenrame","password")
    StartMessageLoop
End Sub

Sub RunMe
    Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
End Sub

Sub AddEvent(table As String, event As String, eventid As String)
    MySQL.AddNonQueryToBatch("INSERT INTO `" & table & "_events` (time,event,eventid) select ?, ?, ? WHERE Not EXISTS(SELECT 1 FROM `" & table & "_events` WHERE event = ?)", Array(DateTime.now, event.Trim, eventid.trim, event.Trim))
  
End Sub

Every time I get a specific MQTT message, I am running the code AddEvent to log the event. There could be 100-200 messages per minute and at times no message for a few minutes. I don't want to add the event when the MQTT is received and I want to add it 10-20 seconds after the event as a batch insert.

When I run the code RunMe sometimes it shows that the SQL is not connected and I need to re-run the B4J app to make it connect.

Of course I can add the MySQL.Initialize2 code to the RunMe but from what I can work out it will clear the items I added to MySQL.AddNonQueryToBatch.

What is the correct way in connecting to the MySQL and then inserting the items without losing the events its trying to add to the database?

Should I put the items in a map when I run the code AddEvent, and then later on run RunMe (add MySQL.Initialize2 at the start) and get the items from the map when adding the item to the QueryToBatch routine ?
 

walt61

Active Member
Licensed User
Longtime User
Should I put the items in a map when I run the code AddEvent, and then later on run RunMe (add MySQL.Initialize2 at the start) and get the items from the map when adding the item to the QueryToBatch routine ?

I think so. I seem to remember (but don't remember where I read it) that you can't count on the MySQL connection to remain open.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
Maybe you can try to use
B4X:
MySQL.InitializeAsync("sql", "com.mysql.jdbc.Driver", "jdbc:mysql://localhost/database?characterEncoding=utf8", "username", "password")

Before executing the batch, you can try check if the database is initialized or opened.
B4X:
If MySQL.IsInitialized Then
    Dim SenderFilter As Object = MySQL.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
End If

Also consider to create it as a server app and use Connection Pool.
 
Upvote 0

aaronk

Well-Known Member
Licensed User
Longtime User
Before executing the batch, you can try check if the database is initialized or opened.
It know it is initialized, but can't detect if the connection is open or not.

I think I will need to go down the path of putting it in a map and then running the code so it re-initializes the connection before it runs the batch.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
It know it is initialized, but can't detect if the connection is open or not.
1730817595818.png
 
Upvote 0

aaronk

Well-Known Member
Licensed User
Longtime User
Oh, I didn't know it also checked it was opened (connected). I will give it a go.
 
Upvote 0
Top