Other Error using MySQL...

[rbghongade]

Dear friends,
Let me explain the setup first:
1)I have a MySQL server running on a remote PC (Windows 10) and mosquitto service on the same machine (let us call it server)
2) A central user interface shows the node data (written in B4J) on the same server
3) Nodes connected via wifi in same network transmit data using MQTT to the server. MQTT works fine,
4)A client program (also written in B4J) and connected on the same network is expected to show the node data too. It receives the messages properly.
Problem: However when the client tries to access the node information ( not the data) from the server , I get an error: ConnectionisClosed: No operations allowed after connection is closed.
I cannot attach the error logs directly because the setup is located on the user premises. But I have attached the screen shots (apologies for the same)
Previously the client was working fine.

 

[Erel]

Post the relevant code...

 

[rbghongade]

Sub init
    sql1.Initialize2("com.mysql.cj.jdbc.Driver", "jdbc:mysql://"&srvr&"/NODES?characterEncoding=utf8","root","*****")
    Log("ok")
    Try
        sql1.ExecNonQuery("CREATE TABLE sensors (UID TEXT , LOC TEXT,MACHINE TEXT, SETCO2_LOW DOUBLE, SETTEMP_LOW DOUBLE, SETCO2_HIGH DOUBLE, SETTEMP_HIGH DOUBLE, MODEL TEXT)")
        Log("Table created")
    Catch
        Log(LastException)
    End Try
    
End Sub

Sub Client_MessageArrived1 (Topic As String, Payload() As Byte)
    Log("Message arrived. Topic="& Topic & " payload: "& bc.stringFromBytes(Payload,"ASCII"))
    Dim msg As String=bc.stringFromBytes(Payload,"ASCII")
    If msg="OFFLINE"  Then
        Dim device(2),uid As String
        device=Regex.Split("/",Topic)
        uid=device(0)
        status.Put(uid,"OFFLINE")
    Else
        Dim devicedata(4) As String
        devicedata=Regex.Split(",",msg)
        Dim device(2),uid As String
        device=Regex.Split("/",Topic)
        uid=device(0)
        status.Put(uid,"ONLINE")
    End If
        
    Select Topic
                    
        Case uid&"/DATA"
            'Try
            
            date=DateTime.GetDayOfMonth(DateTime.Now)&"/"&DateTime.GetMonth(DateTime.Now)&"/"&DateTime.GetYear(DateTime.Now)
            time=DateTime.time(DateTime.Now)
            
            Dim data As String
            Dim sco2low As Double
            Dim stemplow As Double
            Dim sco2high As Double
            Dim stemphigh As Double
            Dim SLOC As String
            Dim mac As String
                
            tempmap.Put(uid,devicedata(1))
            co2map.Put(uid,devicedata(2))
            hummap.Put(uid,devicedata(3))
            lblDateTime.Text=date &" "&time
            'Try
            rs1 = sql1.ExecQuery("SELECT * FROM sensors WHERE UID ='"&uid&"'")
                
            
            Dim recs As Int
            Do While rs1.NextRow
                sco2low=rs1.GetString("SETCO2_LOW")
                stemplow=rs1.GetString("SETTEMP_LOW")
                sco2high=rs1.GetString("SETCO2_HIGH")
                stemphigh=rs1.GetString("SETTEMP_HIGH")
                model1=rs1.GetString("MODEL")
                SLOC=rs1.GetString("LOC")
                mac=rs1.GetString("MACHINE")
                recs=recs+1
            Loop
            
            rs1.Close
            'Catch
            'End Try
            
    End Select
    
End Sub

 

[NikB4x]

Sub init
    sql1.Initialize2("com.mysql.cj.jdbc.Driver", "jdbc:mysql://"&srvr&"/NODES?characterEncoding=utf8","root","*****")
    Log("ok")
    Try
        sql1.ExecNonQuery("CREATE TABLE sensors (UID TEXT , LOC TEXT,MACHINE TEXT, SETCO2_LOW DOUBLE, SETTEMP_LOW DOUBLE, SETCO2_HIGH DOUBLE, SETTEMP_HIGH DOUBLE, MODEL TEXT)")
        Log("Table created")
    Catch
        Log(LastException)
    End Try
  
End Sub

Sub Client_MessageArrived1 (Topic As String, Payload() As Byte)
    Log("Message arrived. Topic="& Topic & " payload: "& bc.stringFromBytes(Payload,"ASCII"))
    Dim msg As String=bc.stringFromBytes(Payload,"ASCII")
    If msg="OFFLINE"  Then
        Dim device(2),uid As String
        device=Regex.Split("/",Topic)
        uid=device(0)
        status.Put(uid,"OFFLINE")
    Else
        Dim devicedata(4) As String
        devicedata=Regex.Split(",",msg)
        Dim device(2),uid As String
        device=Regex.Split("/",Topic)
        uid=device(0)
        status.Put(uid,"ONLINE")
    End If
      
    Select Topic
                  
        Case uid&"/DATA"
            'Try
          
            date=DateTime.GetDayOfMonth(DateTime.Now)&"/"&DateTime.GetMonth(DateTime.Now)&"/"&DateTime.GetYear(DateTime.Now)
            time=DateTime.time(DateTime.Now)
          
            Dim data As String
            Dim sco2low As Double
            Dim stemplow As Double
            Dim sco2high As Double
            Dim stemphigh As Double
            Dim SLOC As String
            Dim mac As String
              
            tempmap.Put(uid,devicedata(1))
            co2map.Put(uid,devicedata(2))
            hummap.Put(uid,devicedata(3))
            lblDateTime.Text=date &" "&time
            'Try
            rs1 = sql1.ExecQuery("SELECT * FROM sensors WHERE UID ='"&uid&"'")
              
          
            Dim recs As Int
            Do While rs1.NextRow
                sco2low=rs1.GetString("SETCO2_LOW")
                stemplow=rs1.GetString("SETTEMP_LOW")
                sco2high=rs1.GetString("SETCO2_HIGH")
                stemphigh=rs1.GetString("SETTEMP_HIGH")
                model1=rs1.GetString("MODEL")
                SLOC=rs1.GetString("LOC")
                mac=rs1.GetString("MACHINE")
                recs=recs+1
            Loop
          
            rs1.Close
            'Catch
            'End Try
          
    End Select
  
End Sub

Hi,
it seems that the connection is closed when you are trying to popolate the resultset.
Try to add @ line 38:
sql1.Initialize2("com.mysql.cj.jdbc.Driver", "jdbc:mysql://"&srvr&"/NODES?characterEncoding=utf8","root","*****")

Notice that Mysql connection has a timeout for the connection that can be modified

 

[Erel]

1. This is a big code smell: [B4X] "Code Smells" - common mistakes and other tips

 rs1 = sql1.ExecQuery("SELECT * FROM sensors WHERE UID ='"&uid&"'")

2. rs1 should be a local variable.
3. Assuming that it is a UI app, you should use the async sql methods as it is a remote database.
4. Use ConnectionPool and forget from connection issues.

jConnectionPool - ConnectionPool extracted from jServer

If you want to use ConnectionPool in a UI app then you should use this library instead of jServer. jServer is not compatible with B4J Packager 11. jConnectionPool library is a subset of jServer with only the ConnectionPool feature.

www.b4x.com

 

[rbghongade]

Thanks [U]Nicola Ciaramellano[/U], I shall try it out.

 

[Peter Simpson]

Here you go @rbghongade

1. Use

SQL1.InitializeAsync("SQL", ...)

2. Then use

Wait For SQL_Ready (Success As Boolean)

3. If connected to the database (Success = True), then do this

Dim SenderFilter As Object = SQL1.ExecQueryAsync("SQL", ...)

4. Followed by this

Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, RS As ResultSet)
If Success Then
    Do While RS.NextRow

Using the above technique is a better and safer way to connect too your databases and then to run your SQL queries.



Enjoy...

 

[rbghongade]

@Peter Simpson ,
Thanks for the huge help. Sorry I was busy and hence did not see your reply.