B4J Question Error probably unrelated to the text error reported - java.sql.SQLException: The result set is closed

[amorosik]

I have a sub that I use to read the new document number to assign to new records
The code flow proceeds correctly as expected, until a value is assigned from a local variable to another local variable, where an error is reported
Enviroment: B4J 9.80, Jdk 14.0.1, Win10 pro 64bit, db Firebird 3.01, jaybird-full-4.0.9.java11.jar
When line 53 of the code is executed, execution is stopped and the following error is reported "java.sql.SQLException: The result set is closed"
Which doesn't seem congruent with the simple assignment of a variable numero_max_attuale (long)
What can I check to eliminate this error?

Sub NUOVO_NUMERO_DOCUMENTO(tip_doc As String,ser_doc As String)As Long
  
    Try
        Dim cod_lin_num As Long= modDB.DLookup("CODICE_LINEA_NUMERAZIONE", "DOCUMENTITIPI", "TIPO='" & tip_doc & "' ")
        Dim passo_num As Long= modDB.DLookup("STEP", "LINEENUMERAZIONI", "CODICE=" & cod_lin_num)
        Dim su_base As String= modDB.DLookup("PROGRESSIVO_SU_BASE", "LINEENUMERAZIONI", "CODICE=" & cod_lin_num)
        Dim numero_doc As Long = 0, numero_max_attuale As Long = 0
  
        ' Cicla fra i documenti con la stessa linea numerazione
        Dim MyrsDocTipi As ResultSet = MyDB.ExecQuery("select TIPO from DOCUMENTITIPI where CODICE_LINEA_NUMERAZIONE=" & cod_lin_num)
  
        Do While MyrsDocTipi.NextRow
          
            If su_base = "ANNO" Then
                If MyDB.IsInitialized = False Then
                    MyDB.Initialize2(Db_Driver_Class,Db_Jdbc_Url,Db_Username,Db_Password)

                    If MyDB.IsInitialized = False Then
                        logga ("NUOVO_NUMERO_DOCUMENTO - ERRORE CONNESSIONE DATABASE => " & LastException)
                        Return 0
                        Else
                        logga("Connessione a DB eseguita correttamente" )
                        End If
                    End If
              
                Dim MyRsDocTes As ResultSet = MyDB.ExecQuery("select max(NUMERO_DOCUMENTO) as MAX_NUM_DOC from DOCUMENTITESTATE where TIPO_DOCUMENTO='" & tip_doc & "' and SERIE_DOCUMENTO='" & ser_doc & "' and DATA_DOCUMENTO >= '01/01/" & DateTime.GetYear(DateTime.Now) & "' and DATA_DOCUMENTO <= '12/31/" &  DateTime.GetYear(DateTime.Now) & "'  ")
                Do While MyRsDocTes.NextRow
                  
                    If MyRsDocTes.Getstring("MAX_NUM_DOC")=Null Then
                        Return 0
                        Else
              
                        If MyRsDocTes.Getstring("MAX_NUM_DOC").Length>0 Then
                            numero_doc = MyRsDocTes.Getstring("MAX_NUM_DOC")
                            If numero_doc >= numero_max_attuale Then
                                '----------------------------------------------
                                ' Quando passa la riga sotto e' tutto ok
                                '----------------------------------------------
                                numero_max_attuale = numero_doc
                                End If
                            Else
                            Return 0
                            End If
              
                        End If
                    Loop
                End If
      
            If numero_doc >= numero_max_attuale Then
                '************************************************
                ' Quando passa la riga sotto viene segnalato l'errore
                '************************************************
                numero_max_attuale = numero_doc
                End If
      
            Loop
          
        Dim prossimo_numero_disponibile As Long= passo_num + numero_max_attuale
      
        Catch
        Log(LastException)
        Dim prossimo_numero_disponibile As Long=1
        End Try
  
    MyDB.ExecNonQuery ("update LINEENUMERAZIONI set VALORE_ATTUALE=" & prossimo_numero_disponibile & " where CODICE=" & cod_lin_num)

    If IsNumber(prossimo_numero_disponibile) Then
        Return prossimo_numero_disponibile
        Else
        Return 1
        End If
End Sub

Error occurred on line: 1406 (Main)
java.sql.SQLException: The result set is closed
    at org.firebirdsql.jdbc.AbstractResultSet.checkOpen(AbstractResultSet.java:313)
    at org.firebirdsql.jdbc.AbstractResultSet.checkCursorMove(AbstractResultSet.java:301)
    at org.firebirdsql.jdbc.AbstractResultSet.next(AbstractResultSet.java:362)
    at anywheresoftware.b4j.objects.SQL$ResultSetWrapper.NextRow(SQL.java:503)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA$3.run(BA.java:267)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:832)

 

[drgottjr]

read this: https://stackoverflow.com/questions/935511/how-can-i-avoid-resultset-is-closed-exception-in-java. read the whole thing, please.

there are potentially issues with nested resultsets. excerpts from the link:

Sounds like you executed another statement in the same connection before traversing the result set from the first statement. If you're nesting the processing of two result sets from the same database, you're doing something wrong.

Some drivers do not allow nested statements

Your ResultSet may be returning no values. So when you try to access data from the ResultSet java will throw a ResultSetClosedException.

etc, etc.

as soon as i saw the nested resultsets in your posted code, i thought there might be a problem.

 

[amorosik]

Ah, I didn't know about the impossibility of using different resultsets with some db drivers
I have now used a MyDB2, a different connection, in the inner loop and it now seems to be running fine, many thanks