0

I'm using ojdbc8 version 21.1.0.0 to be my JDBC. I have encountered issues that my connection stream will be closed everytime before calling close() when I execute my query that asked for long from resultSet for unknown reason, and my SQL runs fine on SQL developer. So I googled and see this bug reported on Oracle.

https://support.oracle.com/knowledge/Middleware/832903_1.html

Seem like it's a bug to their JDBCs? Does anyone know which version of ODBC8 doesn't have this bug? or other walkaround to avoid this bug?

==== Update ====

@Stephen C

Well, if you say so. But I don't think this would help anyway since the code itself is too simple to have such bug.

class Channel {
    private static DataSource ds = null;
    private static final String GET_ONE = "SELECT * FROM Channel WHERE Id=?";
    
    public Channel() {
        if(ds == null) {
            try {
                Context ctx = new InitialContext();
                ds = (DataSource) ctx.lookup("jdbc/oracle");
            } catch (NamingException e) {
                e.printStackTrace();
            }
        }
    }

    public HashMap<String, Object> getOne(String id) throws SQLException{
        HashMap<String, Object> result = null;
        ResultSet rs = null;
        
        try(Connection conn = ds.getConnection();
            PreparedStatement pstmt = con.prepareStatement(GET_ONE);){
            
            pstmt.setString(1, id);
            
            rs = pstmt.executeQuery();
            
            if(rs.next()){
                result = new HashMap<String, Object>();
                result.put("Time_Limit", rs.getLong("Time_Limit"));// recommended by @tgdavies
                result.put("Id", rs.getString("Id"));
                result.put("Name", rs.getString("Name"));
            }
        
        } catch(SQLException e){
            throw e;
        } finally {
            if(rs != null){
                try{
                    rs.close();
                } catch(SQLException e){}
            }
        }
        
        return result;
    }
}
Kaninchen
  • 455
  • 2
  • 7
  • 19
  • If you're using a really old driver version, then upgrade. If you're using the very latest version, and it seems buggy, then file a report and downgrade to the previous nearest stable version. – Tim Biegeleisen Jul 09 '21 at 04:14
  • Have you tried the solution of reading the LONG value from the ResultSet first, before any other column? See https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/ – tgdavies Jul 09 '21 at 04:45
  • @tgdavies tried to read Long values first and still got that error – Kaninchen Jul 09 '21 at 04:56
  • @TimBiegeleisen tried to downgrade to 19.7.0.0 and no luck for now – Kaninchen Jul 09 '21 at 05:00
  • It is quite possible that the real cause is a bug in >your< code. Either way, there's not much we can do to actually help you unless you show us the relevant code. Ideally a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – Stephen C Jul 09 '21 at 07:58
  • @StephenC Well, if you say so. Codes added, but I don't think this would help. – Kaninchen Jul 11 '21 at 18:26
  • (FWIW - catching `SQLException` and rethrowing it is not necessary ... unless that is the remainder of some debugging ...) – Stephen C Jul 12 '21 at 01:14
  • @StephenC It was a custom error message, but not the case that I'm facing now. So I removed that. – Kaninchen Jul 12 '21 at 03:25

1 Answers1

1

I think I know what the problem is.

(Caveat: You haven't included a stacktrace, so I can't confirm this. Furthermore, the link you posted is not open, so we can't see the details of the bug report you are talking about.)

    try(Connection conn = ds.getConnection();
        PreparedStatement pstmt = con.prepareStatement(GET_ONE);){
        
        pstmt.setString(1, id);
        
        rs = pstmt.executeQuery();
        
        if(rs.next()){
            result = new HashMap<String, Object>();
            result.put("Time_Limit", rs.getLong("Time_Limit"));// recommended by @tgdavies
            result.put("Id", rs.getString("Id"));
            result.put("Name", rs.getString("Name"));
        }
    
    } catch(SQLException e){
        throw e;
    } finally {
        if(rs != null){
            try{
                rs.close();
            } catch(SQLException e){}
        }
    }

The problem is that the finally gets executed AFTER the resources have been autoclosed. So your code will be calling rs.close() after the PreparedStatement has been closed. But closing the statement closes any result sets created from the statement.

Reference:

Possible solutions:

  1. Treat the ResultSet as a resource too; e.g. with a nested try with resource.
  2. Get rid of the finally ...
Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Something that I don't get it: I have it ignored with this line `} catch(SQLException e){}`, so I'm not suppose to get the stream closed exception while executing it. right? – Kaninchen Jul 12 '21 at 03:32
  • Well ... it should be clear from the stacktrace where the `SQLException` is being thrown. And it may be that it is happening within the try block, which would make my explanation incorrect. (But a minrep should include a stacktrace ...) – Stephen C Jul 12 '21 at 04:25
  • And if this really is a driver bug, then you should be talking to Oracle Support about workarounds and alternative versions. – Stephen C Jul 12 '21 at 05:20
  • This looks like the right answer. Remove the finally block. You don't need it. – Jean de Lavarene Jul 19 '21 at 17:56