Is there any Groovy or Java code to check the server reachability in microsoft sql for 3 attempts in the interval of 30 minutes

Question

can you please help with Groovy or Java code to check the server reachability in microsoft sql for 3 attempts in the interval of 30 minutes.I have developed a code to execute query in msql.

But i need to check the failure condition If server is not reachable try for 3 attempts in the interval of 30 minutes.

I have shared the sample groovy code that i used to connect with Sql server and run the query .

def username="abcc"
def password="sdsadsdsad"
def result=""
def driver = Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver').newInstance()
def props = new Properties()
props.setProperty("user", username)
props.setProperty("password", password) 
def conn=driver.connect("jdbc:sqlserver://dbservername:port;databaseName=datatablename;integratedSecurity=true;authenticationScheme=NTLM", props)
def sql = new groovy.sql.Sql(conn)
def query="select top(10) taskid from aspn.task_history"
sql.query(query)
{ resultSet ->
while (resultSet.next())
{
  println resultSet.getString("taskid")
}
}
conn.close()

Answer 1

def username = "abcc"
def password = "sdsadsdsad"
def result = ""
def driver = Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver').newInstance()
def props = new Properties()
props.setProperty("user", username)
props.setProperty("password", password)

def withReconnect = { connect, action ->
  def tryConnect = {
    try {
      println 'Connecting...'
      connect().withCloseable { conn ->
         action(conn)
      }
      return true // all OK
    // use the type of Exception that happens when connection fails below
    } catch (ConnectionException e) {
      e.printStackTrace()
    }
    return false // FAIL
  }
  // try a few times
  for (def i in (1..3)) {
    if (i != 1) { // sleep only if not first time
      println 'Connection failed, will try again in 10 minutes...'
      sleep 10 * 60 * 1000
    }
    def ok = tryConnect()
    if (ok)
    {
      println 'Successfully executed query'
      return // end the script
    }
  }

  // if we get here, we did not have success
  throw new RuntimeException('Failed too many times, aborting...')
}

// define how to connect
def connect = {
  driver.connect(
    "jdbc:sqlserver://dbservername:port;databaseName=datatablename;integratedSecurity=true;authenticationScheme=NTLM",
  props)
}

// go!
withReconnect(connect) { conn ->
  def sql = new groovy.sql.Sql(conn)
  def query="select top(10) taskid from aspn.task_history"
  sql.query(query) { resultSet ->
    while (resultSet.next())
    {
      println resultSet.getString("taskid")
    }
  }
}