Does this cause a JDBC connection leak?

Question

I'm getting this SQL exception:

  16043 10.8.38.30> 2012-03-06 14:21:00,870 | INFO  | SubThread-10 | DatabaseSender: Executing SQL Statement...; SQL Statement = select ERRORCODE from ERR_MASTER where ErrorDescription='State must be 'pre-installed', 'active', 'inactive' or 'deactive''
  16044 10.8.38.30> 2012-03-06 14:21:00,872 | ERROR | SubThread-10 | Exception caught: E_01_003_0 ORA-00933: SQL command not properly ended
  16045 
  16046 10.8.38.30> 2012-03-06 14:21:00,872 | ERROR | SubThread-10 | SQLState: 42000
  16047 10.8.38.30> 2012-03-06 14:21:00,872 | ERROR | SubThread-10 | Errorcode: 933
  16048 10.8.38.30> 2012-03-06 14:21:00,872 | ERROR | SubThread-10 | Exception Stack:
  16049 10.8.38.30> 2012-03-06 14:21:00,873 | ERROR | SubThread-10 | java.sql.SQLException: ORA-00933: SQL command not properly ended

Of course the problem was from ErrorDescription='State must be 'pre-installed', 'active', 'inactive' or 'deactive'' having single quoted string inside a single quote.

But given the code below, does it cause a connection leak when the above exception is encountered?

Code executing the SQL:

private String getErrorCode(Connection conn, PreparedStatement ps, ResultSet result, String sErrorDesc) throws Exception {
    String sErrorCode = null;
    StringBuffer sBuffer = new StringBuffer();
    sBuffer.append("SELECT ").append(aaConstants.COL_ERRMASTER_ERRORCODE);
    sBuffer.append(" from ").append(aaConstants.TABLE_ERRMASTER);
    sBuffer.append(" where ").append(aaConstants.COL_ERRMASTER_ERRORDESCRIPTION);
    sBuffer.append(" = '").append(sErrorDesc).append("'");
    try{
        ps = conn.prepareStatement(sBuffer.toString());
        Trace.info("sql : " +sBuffer.toString());       
        result = ps.executeQuery();

        if (result.next())
            sErrorCode = result.getString(aaConstants.COL_ERRMASTER_ERRORCODE);

    }finally{
        aaUtils.cleanUp(null, ps, result);
    }
    if (sErrorCode == null || sErrorCode.equals(""))
        sErrorCode = aaErrorCode.MsgCode_Default_ErrorCode;

    return sErrorCode;
}       

The above method was called here:

public String getServerExpiredResponse() throws AuthException {
    String retstr="", sessionid="";
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet result = null;

    if (!loginok) {

        try{
            conn = aaUtils.getDBConnection();
            String session_loginfailure_faultcode = getErrorCode(conn, ps, result, sErrorDesc);
            String session_loginfailure_faultstring = sErrorDesc;

            if (session_loginfailure_faultcode==null) {session_loginfailure_faultcode="";}
            if (session_loginfailure_faultstring==null) {session_loginfailure_faultstring="Failed.";} 
            retstr +=
                "<faultcode>"
                    + session_loginfailure_faultcode
                + "</faultcode>"
                + "<faultstring>"
                    + session_loginfailure_faultstring
                + "</faultstring>";

        } catch (Exception e) {
            throw new AuthException(Trace.stack2string(e));
        }finally{
            aaUtils.cleanUp(conn, ps, result);
        }
    }
    Trace.info("In getServerExpiredResponse " +retstr);
    return retstr;
}

The DB connection was close in the method calling getErrorCode(), since the exception occured in getErrorCode(), will the connection still be close in getServerExpiredResponse()?

You can see that inside getErrorCode() only the Resultset and PreparedStatement are close.

Answer 1

But given the code below, does it cause a connection leak when the above exception is encountered?

It depends what aaUtils.cleanUp(null, ps, result) does. If it closes the statement and the result set, then you shouldn't get a resource leak.