Lesson Learned #388:Retrying Execution in case of Connection Drops/Command Timeouts using ODBC API
Published Jul 02 2023 03:15 AM 2,778 Views

Based on Lesson Learned #368: Connection Retry-Logic using ODBC API code - Microsoft Community Hub I would like to share but was my lesson learned execution TSQL command. Executing a TSQL command connectivity issues or command timeouts can occur, leading to failed executions. To overcome these challenges, it is essential to implement robust error-handling mechanisms. In this article, we will explore how to leverage the ODBC API to retry the execution of TSQL commands when faced with connection drops or command timeouts. 

 

Implementing a Retry Mechanism: The following steps outline how to implement a retry mechanism using the ODBC API.

 

  1. Catch the error: Surround the TSQL command execution with a try-catch block or equivalent error-handling mechanism. In the catch block, examine the error code or exception to identify connection drops or command timeouts.

  2. Determine the retry conditions: Define the conditions under which a retry should occur. For example, you might retry when the error code corresponds to a dropped connection (e.g., SQLSTATE 08S01) or a command timeout (e.g., SQLSTATE HYT00).

  3. Set a maximum retry limit: To prevent infinite retries, set a maximum retry limit. It is essential to strike a balance between allowing enough retries to handle temporary issues and avoiding prolonged execution times.

  4. Introduce a delay between retries: To avoid overwhelming the database server, introduce a delay between retries. Exponential backoff is a popular technique where the delay increases exponentially after each retry, allowing the server time to recover.

  5. Retry the execution: Once the retry conditions are met, re-execute the TSQL command using the same connection. Remember to handle any additional exceptions that may arise during the retry process.

  6. Track retries: Keep track of the number of retries attempted to monitor the effectiveness of the retry mechanism. This information can be useful for troubleshooting and optimizing the system.

Code

 

 

 

        public void MainRetry()
        {
            // Initialize ODBC environment handle
            IntPtr environmentHandle = IntPtr.Zero;
            String sErrorMsg = "";
            Boolean bExecution = false;
            SQLAllocHandle(1, IntPtr.Zero, out environmentHandle);
            //SQLSetEnvAttr(environmentHandle, 201, (IntPtr)2, 0);
            SQLSetEnvAttr(environmentHandle, 200, (IntPtr)380, 0);

            bExecution = bMainRetryExecution(environmentHandle, ref sErrorMsg,"WAITFOR DELAY '00:02:50'",4 );
            if(!bExecution)
            {
                Console.WriteLine("Error: " + sErrorMsg);
            }
            else
            {
                Console.WriteLine("Execution correctly");
            }
            SQLFreeHandle(1, environmentHandle);
        }

        public Boolean bMainRetryExecution(IntPtr environmentHandle, 
                                           ref string sErrorMsg, 
                                           string sQuery = "SELECT 1", 
                                           int iRetryCount = 1)
        {
            // Initialize ODBC connection and statement handles
            Boolean bExecute = false;
            int retryIntervalSeconds = 2;

            for (int i = 1; i <= iRetryCount; i++)
            {
                try
                {
                    IntPtr connectionHandle = IntPtr.Zero;
                    IntPtr statementHandle = IntPtr.Zero;
                    int retcode;

                    Console.WriteLine("Try to execute {0} of {1} Query: {2}", i, iRetryCount, sQuery); 
                    retcode = SQLAllocHandle(2, environmentHandle, out connectionHandle);
                    if (retcode == -1)
                    {
                        sErrorMsg = "Not possible to obtain the environment Handle";
                    }
                    else
                    {
                      if (RetryLogicUsingODBCAPI(connectionHandle) == -1)
                      {
                            sErrorMsg = "Connection was not possible after the retries";
                      }
                      else
                      {
                            retcode = SQLAllocHandle(3, connectionHandle, out statementHandle);
                            if (retcode == -1)
                            {
                                sErrorMsg = "Not possible to obtain the statementHandle";
                            }
                            else
                            {
                                SQLSetStmtAttr(statementHandle, SQL_ATTR_QUERY_TIMEOUT, (IntPtr)(30*(i)), 0);
                                retcode = SQLExecDirect(statementHandle, sQuery, sQuery.Length);
                                if (retcode == -1)
                                {
                                    GetODBCErrorDetails(statementHandle, 3);
                                    sErrorMsg = "Error: not possible to execute the query.";
                                    System.Threading.Thread.Sleep(1000 * retryIntervalSeconds);
                                    retryIntervalSeconds = Convert.ToInt32(retryIntervalSeconds * 1.5);
                                }
                                else
                                {
                                    SQLDisconnect(connectionHandle);
                                    SQLFreeHandle(3, statementHandle);
                                    SQLFreeHandle(2, connectionHandle);
                                    sErrorMsg = "Command executed correctly";
                                    bExecute = true;
                                    break;
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                  Console.WriteLine("Error: " + ex.Message);
                  sErrorMsg = "Error: " + ex.Message;
                }
            }
            return bExecute;
        }

 

 

 

Explanation:

 

The script demonstrates a retry mechanism for executing TSQL commands using the ODBC API. Let's go through the code step by step:

  1. MainRetry() is the entry point method. It initializes the ODBC environment handle (environmentHandle) using the SQLAllocHandle() function. The SQLSetEnvAttr() function is called to set an attribute related to query execution time. Then, it calls the bMainRetryExecution() method to perform the actual execution.

  2. bMainRetryExecution() is the method responsible for executing the TSQL command and handling retries. It takes the ODBC environment handle (environmentHandle), an error message string (sErrorMsg), the TSQL query string (sQuery), and the number of retry attempts (iRetryCount) as parameters.

  3. Inside the method, a loop is set up to attempt the execution multiple times based on the specified iRetryCount. The loop starts with i set to 1 and continues until it reaches iRetryCount.

  4. Within each iteration of the loop, the method attempts to execute the TSQL command. It first initializes the ODBC connection handle (connectionHandle) using SQLAllocHandle().

  5. If obtaining the connection handle fails (retcode == -1), an error message is set, indicating the inability to obtain the environment handle.

  6. If obtaining the connection handle is successful, the method calls RetryLogicUsingODBCAPI() to handle the retry logic for the connection. The details of this method are not provided in the code snippet, but it likely includes connection establishment and retry mechanisms specific to the application. You could find more information here: Lesson Learned #368: Connection Retry-Logic using ODBC API code - Microsoft Community Hub

  7. Once the retry logic completes, the method proceeds to allocate the ODBC statement handle (statementHandle) using SQLAllocHandle(). If the allocation fails, an error message is set.

  8. If the statement handle is successfully allocated, the method sets the query timeout attribute using SQLSetStmtAttr(), adjusting the timeout value based on the current retry attempt (30*(i)). 

  9. The TSQL command is then executed using SQLExecDirect() with the statement handle and the provided query string. If the execution fails (retcode == -1), the GetODBCErrorDetails() method is likely called to retrieve specific error information. The code sets an appropriate error message in the sErrorMsg variable, waits for a specified interval using Thread.Sleep(), and increases the retry interval by multiplying it by 1.5. Of course, we could capture the error and depending if the execution and provide other ways to react , also, remember that the connection would be re-stablished - Lesson Learned #381: The connection is broken and recovery is not possible message using API ODBC - ...

  10. If the execution succeeds, the code disconnects from the database, frees the statement and connection handles using SQLDisconnect(), SQLFreeHandle(), and SQLFreeHandle(), respectively. It sets a success message in sErrorMsg, sets bExecute to true, and breaks out of the retry loop. 

  11. Finally, the method catches any exceptions that occur during the execution and sets the error message accordingly.

  12. The method returns the bExecute flag to indicate whether the execution was successful (true) or not (false).

The provided code showcases a basic retry mechanism utilizing the ODBC API for executing TSQL commands.

Version history
Last update:
‎Jul 02 2023 03:16 AM
Updated by: