Lesson Learned #260: Java vs Connection is closed error message.

[Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message using Java code: Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1202)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1063)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:445)
at testconnectionms.SQLTest.main(SQLTest.java:64). I would like to share with you what was my lessons learned here.

The first thing is to isolate the problem running the suggested Java code to see the results:

package testconnectionms;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.lang.model.util.ElementScanner6;
import javax.sql.DataSource;

/**
 * Simple connection test.
 *
 */
public class SQLTest
{
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (Exception ex) {
			System.err.println("Unable to load JDBC driver");
			ex.printStackTrace();
			System.exit(1);
		}
	}

	public static void main(String[] args)
		throws SQLException
	{
		String username   = "username";
		String password   = "Password";
		String envname    = "env";
		String seconds   = "10";
		        
		System.out.println("Arguements are: username="+username+",password="+password+",envname="+envname+",seconds="+seconds);
		
		String url = String.format("jdbc:sqlserver://servername.database.windows.net:1433;database=dbname;sslProtocol=TLSv1.2", envname,envname);

		System.out.println("-- Connecting to " + url);
		
		long start = System.currentTimeMillis();
		Connection connection = DriverManager.getConnection(url, username, password);

		if (false) {
			
		}
		
		final long time1 = System.currentTimeMillis();
		long time2 = time1;
		long cnt = 0;
		Integer secInt = Integer.valueOf(seconds);

        PreparedStatement st = connection.prepareStatement("SELECT * from demo");
		st.setQueryTimeout(20);
		while (time1 + (secInt.intValue() * 1000) >= time2)
		{
			cnt++;
			
			
			ResultSet rs = st.executeQuery();
			rs.next();
			rs.close();
			
			
			time2 = System.currentTimeMillis();
		}
		st.close();
		
		Long statementsCount = Long.valueOf(cnt);
		Long statementsPerSecond = Long.valueOf(cnt / ((time2 - time1) / 1000));
		Double timePerStatement = Double.valueOf(1000.0 / (cnt / ((time2 - time1) / 1000.0)));
		
		System.out.println("statementsCount="+statementsCount);
		System.out.println("statementsPerSecond="+statementsPerSecond);
		System.out.println("timePerStatement="+timePerStatement);
	}
	
	private static void usage(String msg)
	{
        System.err.println("Usage: SQLTest -username username -password password -envname envname -seconds seconds");
        System.err.println(msg);
	}
}

This Java script worked well, also, using TCPView  we could see that even using redirect the connection is working fine. 

But, what is happening if we have any transient issue in the connectivity?, in this situation, we are going to have a similar error message The connection is closed that we shared before and in this situation, the suggestion is always to use a connection and execution retry-logic.

Example for connection retry-logic class.

package testconnectionms;

import java.sql.*;


public class ClsRetryLogic 
{
    private Connection oConnection;
    private boolean bClose = true;
    private long lConnectiontime=0;
    public boolean HazUnaConexionConReintentos(String sConnection)
    {
     int retryIntervalSeconds = 10;
     double calculation;
     boolean returnBool=false;
    
     for (int tries = 1; tries <= 5; tries++)
     {
       try
         { 
          if(tries>1) 
          {
            System.out.println("Waiting time: " + retryIntervalSeconds);
            Thread.sleep(1000 * retryIntervalSeconds);
            calculation = retryIntervalSeconds * 1.5;
            retryIntervalSeconds = (int) calculation;            
          }
         System.out.println("Connecting to Database");
         final long time1 = System.currentTimeMillis();
         oConnection = DriverManager.getConnection(sConnection);
         System.out.println("Connected to Database");         

         final long time2 = System.currentTimeMillis();
         this.setConnectiontime((time2 - time1));
  

         returnBool = true;
         break;
         }
       catch(Exception e)
       {
        System.out.println("Error connecting to .. " + e.getMessage());
       }  
    }  
    return returnBool;
}

  public Connection getConn() {
    return oConnection;
  }

  public void setCloseConnection(boolean bCloseIndicator)
  {
      this.bClose=bCloseIndicator;
  }

  public boolean getCloseConnection()
  {
      return this.bClose;
  }

  public void setConnectiontime(long lTime)
  {
      this.lConnectiontime=lTime;
  }

  public long getConnectiontime()
  {
      return this.lConnectiontime;
  }


}

Example for Execution retry-logic class.

package testconnectionms;
import java.sql.*;

public class ErrorClient {
    private boolean bClose = true;
    private boolean bReadingSQLData = true;
    private int iTotalIteractions = 1;
    private int iDelay=0;
    private String sSQLConnection = "";
    private String sSQLToExecuteRead = "";
    private int iTimeSQLCommandTimeout=30;

    public void LoadData() throws SQLException 
    {
    ClsRetryLogic[] oRetryLogic = new ClsRetryLogic[this.getTotalIteractions()];
    for(int i=0;i<this.getTotalIteractions();i++)
    {
      oRetryLogic[i]=new ClsRetryLogic();  
      oRetryLogic[i].setCloseConnection(bClose);
      System.out.println("Interaction # " + i);
      if( oRetryLogic[i].HazUnaConexionConReintentos(this.getSQLConnection() ));
      {
        if(this.getReadingSQLData())
        {
         readData(oRetryLogic[i].getConn());
        }  
        if(oRetryLogic[i].getCloseConnection())
        {
            oRetryLogic[i].getConn().close();
        }
      }
    }
}
    private void readData(Connection connection) {
    for (int tries = 1; tries <= 5; tries++)
     {        
      try
       {
        System.out.println("Reading Data! " + this.getSQLReadToExecute());
        PreparedStatement readStatement = connection.prepareStatement(this.getSQLReadToExecute());
		final long time1 = System.currentTimeMillis();
        readStatement.setQueryTimeout(this.getSQLCommandTimeout());
        ResultSet resultSet = readStatement.executeQuery();
        while(resultSet.next())
        {
          System.out.println(resultSet.getLong("id"));
        }  
        final long time2 = System.currentTimeMillis();
		Long statementsPerSecond = Long.valueOf((time2 - time1) / 1000);
		System.out.println("Time="+statementsPerSecond);
        break;
      }   
      catch(Exception e)
      {
       System.out.println("Reading Data " + tries + " - Error  .. " + e.getMessage());
      }  
     }
    }

    public void setCloseConnection(boolean bCloseIndicator)
    {
        this.bClose=bCloseIndicator;
    }
  
    public boolean getCloseConnection()
    {
        return this.bClose;
    }

    public void setReadingSQLData(boolean bReadingSQLData)
    {
        this.bReadingSQLData=bReadingSQLData;
    }
  
    public boolean getReadingSQLData()
    {
        return this.bReadingSQLData;
    }

    public void setTotalIteractions(int iTotalIteractions)
    {
        this.iTotalIteractions=iTotalIteractions;
    }
  
    public int getTotalIteractions()
    {
        return this.iTotalIteractions;
    }

    public void setDelay(int iDelay)
    {
        this.iDelay=iDelay;
    }
  
    public int getDelay()
    {
        return this.iDelay;
    }

    public void setSQLConnection(String sSQL)
    {
        this.sSQLConnection=sSQL;
    }
  
    public String getSQLConnection()
    {
        return this.sSQLConnection;
    }

    public void setSQLReadToExecute(String sSQL)
    {
        this.sSQLToExecuteRead=sSQL;
    }
  
    public String getSQLReadToExecute()
    {
        return this.sSQLToExecuteRead;
    }
    public void setSQLCommandTimeout(int i)
    {
        this.iTimeSQLCommandTimeout=i;
    }
  
    public int getSQLCommandTimeout()
    {
        return this.iTimeSQLCommandTimeout;
    }
 

}

Enjoy!