Other Error using MySQL...

rbghongade

Active Member
Licensed User
Longtime User
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.
 

Attachments

  • 1.jpg
    1.jpg
    146.2 KB · Views: 195
  • 2.jpg
    2.jpg
    158 KB · Views: 198

rbghongade

Active Member
Licensed User
Longtime User
init:
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

error triggered here:
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
 
Upvote 0

NikB4x

Member
Licensed User
Longtime User
init:
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

error triggered here:
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
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
1. This is a big code smell: [B4X] "Code Smells" - common mistakes and other tips
B4X:
 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.
 
Upvote 0

Peter Simpson

Expert
Licensed User
Longtime User
Here you go @rbghongade

1. Use
B4X:
SQL1.InitializeAsync("SQL", ...)

2. Then use
B4X:
Wait For SQL_Ready (Success As Boolean)

3. If connected to the database (Success = True), then do this
B4X:
Dim SenderFilter As Object = SQL1.ExecQueryAsync("SQL", ...)

4. Followed by this
B4X:
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...
 
Last edited:
Upvote 0
Top