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

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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. 

 

Jose_Manuel_Jurado_0-1672226464340.png

 

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.