B4J Question SQL Error: Operation not allowed after ResultSet closed

Discussion in 'B4J Questions' started by jmon, Jul 2, 2014.

  1. jmon

    jmon Well-Known Member Licensed User

    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:
    It happens with this "LoadProject_QueryComplete" sub:
    Code:
    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.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Which JDBC driver are you using?
     
  3. jmon

    jmon Well-Known Member Licensed User

    It must be the one from windows. This is how I connect:
    Code:
    SQL.Initialize2("com.mysql.jdbc.Driver""jdbc:mysql://xxx-xxx-xxx:3306/intranet?characterEncoding=utf8""xxxx""xxx")
    My SQL Connector is:
    Code:
    mysql-connector-java-5.1.27-bin.jar
    Using jSQL 1.12

    Thanks

    [EDIT] Edited the first line
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Use a ConnectionPool from the server library:

    Code:
    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 likes this.
  5. jmon

    jmon Well-Known Member Licensed User

    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:
    Code:
    Jul 042014 12:09:00 PM com.mchange.v2.log.MLog <clinit>
    INFO: MLog clients using java 
    1.4+ standard logging.
    Jul 
    042014 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 
    042014 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.
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  7. jmon

    jmon Well-Known Member Licensed User

    Thanks a lot.
     
  8. jmon

    jmon Well-Known Member Licensed User

    Hi,

    I just wanted to check something. is my understanding correct here:
    Code:
    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
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    Yes.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice