Java – Error handling & Retry in SQL Server on Ubuntu No.6

How to implement the error handling and retry with some interval in SQL Server on Ubuntu by Java is shown in this blog.

▼1. An error handling and a retry logic with interval are mandatory

When we connect to cloud services, we often experience the intermittent network issue. in this case, an error handling and a retry logic with some interval are mandatory to keep application working on.


▼2. Creating Java code to implement Error handling & Retry logic

2-1. Creating a Java code

This Java code implements 3 retries with 30 seconds interval using while after a connection failure. To cause the connection failure, listening port of SQL Server is changed incorrect 1435 from 1433.

import java.sql.*;
import java.util.Date;
import java.lang.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
public class ConnectionSQLSrvRetry {
    private static final Logger log = LoggerFactory.getLogger(ConnectionSQLSrvRetry.class);
 
    public static void main(String[] args) throws InterruptedException {
        Connection con = null;
        boolean success = false;
        int retryc = 0;
        int max_retries = 3;
        while (!success && retryc < max_retries) {
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                String connectionUrl = "jdbc:sqlserver://localhost:1435;" + "databaseName=TestDB;user=sa;password=password;";
                con = DriverManager.getConnection(connectionUrl);
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.info("Connect to SQL Server successfully");
                success = true;
            } catch (ClassNotFoundException e) {
                retryc++;
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.error("Error Message: {}, retry count is {}", e, retryc);
                // 30 seconds (30000 milliseconds) interval
                Thread.sleep(30000);
            } catch (SQLException se) {
                retryc++;
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.error("Error Messages: {}, retry count is {}", se, retryc);
                // 30 seconds (30000 milliseconds) interval
                Thread.sleep(30000);
            }
        }
        try {
            if (con != null) {
                con.close();
                System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
                log.info("The connection is closed");
            }else{
                log.info("The connection was closed");
            }
        } catch (SQLException sec) {
            System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
            log.error("Error MEssages of fin ", sec);
        }
    }
}

2-2. Results for this code

When the specified port in a connection string is incorrect, the following error happens. Port 1435 is specified in the connection string of this code instead of 1433. Since a default connection timeout of Microsoft JDBC Driver is 15 seconds and an interval time is 30 seconds, the error happens 3 times per about 45 seconds.

Current Timestamps: 2020-05-20 22:08:35.503
[main] ERROR ConnectionSQLSrvRetry – Error Messages: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1435 has failed. Error: “Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”., retry count is 1
Current Timestamps: 2020-05-20 22:09:20.159
[main] ERROR ConnectionSQLSrvRetry – Error Messages: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1435 has failed. Error: “Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”., retry count is 2
Current Timestamps: 2020-05-20 22:10:04.716
[main] ERROR ConnectionSQLSrvRetry – Error Messages: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1435 has failed. Error: “Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”., retry count is 3
[main] INFO ConnectionSQLSrvRetry – The connection was closed

▼3. Reference

That’s all. Have a nice day ahead !!!

Leave a Reply

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