B4J Question SQL Error: Operation not allowed after ResultSet closed

[jmon]

Hi,

It's going to be difficult for me to do a working example of this issue, but I try to ask the question first and if needed I'll do a working example.

The problem I get, is when I connect to a remote SQL database, if I do too many "SQL.ExecQueryAsync", I get this "Operation not allowed after ResultSet closed" error:

pageproject._loadproject_querycomplete (java line: 262)
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:800)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6982)
at anywheresoftware.b4j.objects.SQL$ResultSetWrapper.NextRow(SQL.java:389)
at com.xxxx.xxxx.pageproject._loadproject_querycomplete(pageproject.java:262)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.BA$3.run(BA.java:178)
at com.sun.javafx.application.PlatformImpl.lambda$null$164(PlatformImpl.java:292)
at com.sun.javafx.application.PlatformImpl$$Lambda$47/12350821.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$165(PlatformImpl.java:291)
at com.sun.javafx.application.PlatformImpl$$Lambda$46/7135120.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$141(WinApplication.java:102)
at com.sun.glass.ui.win.WinApplication$$Lambda$37/24247422.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)

It happens with this "LoadProject_QueryComplete" sub:

Private Sub LoadProject(id As Int)
    currentProjectId = id
   
    'Get the notes:
    Dim s As StringBuilder : s.Initialize
    s.Append("SELECT note_message, note_user_id, note_timestamp, note_project_id ")
    s.Append("FROM project_notes ")
    s.Append("WHERE note_project_id = ? ")
    s.Append("ORDER BY note_timestamp ")
    s.Append(";")
    If Main.SQL.isInitialized = False Then Main.ConnectToSQL
    Main.SQL.ExecQueryAsync("LoadProject", s, Array(currentProjectId))

End Sub

Private Sub LoadProject_QueryComplete (Success As Boolean, Crsr As ResultSet)
    If Success Then
        lvNotes.Items.Clear
        Do While Crsr.NextRow '<--- CRASH happens HERE!
            Try
                Dim User As ChatUser = UserUtils.FindUserByUniqueId(Crsr.GetInt("note_user_id"))
                If User <> Null Then
                    Dim cm As ChatMessage
                    cm.Initialize(lvNotes)
                    cm.Image = User.Image40
                    cm.UserName = User.NickName
                    cm.Message = Crsr.GetString("note_message")
                    If User.UniqueId = Main.USER_MYSELF.UniqueId Then
                        cm.Color = Main.STYLE_COLOR_MSG_FROM_ME
                    Else
                        cm.color = Main.STYLE_COLOR_MSG_FROM_OTHERS
                    End If   
                    cm.setDateTime(Crsr.GetLong("note_timestamp"))
                    cm.Show
                End If
            Catch
                Log(LastException.Message)
            End Try   
        Loop
        Crsr.Close
    Else
        If LastException.isInitialized Then Debug.Write("PageProjects\LoadProject_QueryComplete", "SQL Exception: " & CRLF & LastException.Message, Debug.VERBOSE_EXCEPTION)
    End If   
End Sub

The weird thing here is that the error is not caught either by the "If Success..." nor by the "Try / Catch" and actually happens right when calling the "Do While Crsr.NextRow" loop.

The code doesn't crash if I use ExecQuery2 instead of ExecQueryAsync.

The error says that my result set is closed, but I close it after the loop. The only issue I can see, is that I have many asyncQueries happening in background, but all the others work as expected.

Any help would greatly be appreciated!

Regards
Jmon.

 

[Erel]

Which JDBC driver are you using?

 

[jmon]

Which JDBC driver are you using?

It must be the one from windows. This is how I connect:

SQL.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://xxx-xxx-xxx:3306/intranet?characterEncoding=utf8", "xxxx", "xxx")

My SQL Connector is:

mysql-connector-java-5.1.27-bin.jar

Using jSQL 1.12

Thanks

[EDIT] Edited the first line

 

[Erel]

Use a ConnectionPool from the server library:

Sub LoadProject
 ...
 Dim sq As SQL = Main.Pool.GetConnecton
 'execute async query
End Sub

Sub LoadProject_QueryComplete (Success As Boolean, Crsr As ResultSet)
 Dim sq As SQL = Sender
 sq.Close 'return the connection to the pool
End Sub

 

[jmon]

Thanks, that solved my problem and it was easy to implement.

I have 3 questions:

1. I still need to close the resultset (Crsr.close) right ?
2. Before I was closing SQL on program close. Do I need to change it to Pool.ClosePool?
3. I get some messages in red color in the log, I don't know if they are errors or not:

Jul 04, 2014 12:09:00 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Jul 04, 2014 12:09:00 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 11:16:28 +0000; debug? true; trace: 10]
Jul 04, 2014 12:09:01 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge0zf921yyvcnx1poq9zx|9735e0, debugUnreturnedConnectionStackTraces -> false, description ->...

Thanks.

 

[Erel]

1. Yes.
2. Yes. Though it will be closed automatically when the process is killed.
3. These are not errors. The connection pool library prints information to the stderr channel.

 

[jmon]

Thanks a lot.

 

[jmon]

Hi,

I just wanted to check something. is my understanding correct here:

Sub LoadProject
    ...
    Dim sq As SQL = Main.Pool.GetConnecton
    'execute async query
    sq.ExecQueryAsync("LoadProject", s, Null)
    'no need to call sq.close here right?
End Sub

Sub LoadProject_QueryComplete (Success As Boolean, Crsr As ResultSet)
    Dim sq As SQL = Sender
    if success then
        do while crsr.nextrow
            'loop through the results
        loop
        crsr.close
    end if
    sq.Close 'return the connection to the pool
End Sub

thanks

 

[Erel]

Yes.