B4J Question MySQL AddNonQueryToBatch

[aaronk]

Hi,

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

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]

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.

 

[aeric]

Maybe you can try to use

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.

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.

 

[aaronk]

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.

 

[aeric]

It know it is initialized, but can't detect if the connection is open or not.

 

[aaronk]

Oh, I didn't know it also checked it was opened (connected). I will give it a go.