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 !!!