B4J Question help on understanding program flow

[marcick]

Difficult to find the correct title for this post.

I have this app on a server that receive packets from a websocket and store them in a MySql database.
Normally everything works ok, a lot of packet are managed without troubles.
It happens sometimes (maybe once a day) an error as you see below and I'm trying to understand why.

so:

1) websocket receive a packet and call sub CheckDuplicateAndInsert
2) this sub contains also a call to a geocoder to get the address from a couple of coordinates.
3) when all is done ConQ is closed.

Can you find where my code is not correct ?

Sub Process_Globals
    Private ConQ As SQL
end sub

Sub CheckDuplicateAndInsert(Bs As String)
    If ConQ.IsInitialized=False Then ConQ=rdcConnector1.GetConnection
    Dim exist As Boolean=False
    cursor1=ConQ.ExecQuery(query)        ' check if the record already exists in the db
    Do While cursor1.NextRow
        exist=True
    Loop
    If exist=True Then
        Log("duplicate record skipped " & rec)
    Else
        Try
            ConQ.ExecNonQuery2("INSERT INTO records ......)
            Inserted=True
        Catch
            Log("Record insert failed: " & rec)
        End Try   
        If Inserted=True Then
            wait For (ReverseGeocode (nl.Rlat, nl.Rlon)) Complete (Addr As String)   
            log(addr)
        End If
    End If
    If ConQ <> Null And ConQ.IsInitialized Then ConQ.close
End Sub


java.sql.SQLException: You can't operate on a closed Statement!!!
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:364)
        at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:342)
        at anywheresoftware.b4j.objects.SQL.ExecQuery(SQL.java:323)
        at jrdc.myapp.main$ResumableSub_CheckDuplicateAndInsert.resume(main.java:516)
        at jrdc.myapp.main._vvvvvvvvvv1(main.java:430)
        at jrdc.myapp.bls1connector._astream_newdata(bls1connector.java:386)

 

[OliverA]

Make ConQ local to the sub and do not check if it initialized, just get a new connection from the pool

'    If ConQ.IsInitialized=False Then ConQ=rdcConnector1.GetConnection
    Dim ConQ as SQL = rdcConnector1.GetConnection

If you don't do this the following can happen:
1) Websocket connection established (connection A), CheckDuplicateAndInsert is called. ConQ is not initialized, so get a new connection. Start processing the rest of the code.
2) Websocket connection established (connection B), CheckDuplicateAndInster is called. ConQ is initialzed, so we don't get our own SQL connection (we now have a shared one with connection A).
3) Connection A finished processing, and since ConQ is not Null and it is Initialized, ConQ is closed.
4) Connection B is at the part after checking for duplicate records and has found none. It is about to execute ConQ.ExecNonQuery2, but ConQ has been closed by Connection A. Boom.

 

[stevel05]

What happens when you close ConQ ? Does it leave an uninitialized object or a Null Object as you are not testing for Null before initializing it at the top of the Sub.

 

[stevel05]

OK, you've sorted it. Good.

 

[OliverA]

BTW, my explanation may not be quite correct. Is the code posted in post#1 in a shared code module?

 

[marcick]

BTW, my explanation may not be quite correct. Is the code posted in post#1 in a shared code module?

The posted code is in the main.
CheckDuplicateAndInsert can be called from websocket module and also from another module.
I think your explaination is what really happens. I'll make ConQ local as you suggest.
Thank you very much (Stevel05 too)