Lesson Learned #368: Connection Retry-Logic using ODBC API code

by Contributed | Jun 11, 2023 | Technology

This article is contributed. See the original author and article here.

This week I had a service request where our customer didn’t have a connection retry logic implemented in their application code in the event of a connection failure to Azure SQL. I would like to share an example about how to implement it. 

First the C# code using ODBC API:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using System.Diagnostics;

namespace DotNetExample
{
    class ClsODBCAPI
    {
        // Import the ODBC API functions using P/Invoke
        [DllImport("odbc32.dll")]
        private static extern short SQLAllocHandle(short handleType, IntPtr inputHandle, out IntPtr outputHandle);

        [DllImport("odbc32.dll")]
        private static extern short SQLSetEnvAttr(IntPtr environmentHandle, int attribute, IntPtr valuePtr, int stringLength);

        [DllImport("odbc32.dll")]
        private static extern short SQLConnect(IntPtr connectionHandle, string serverName, short nameLength1, string userName, short nameLength2, string password, short nameLength3);

        [DllImport("odbc32.dll")]
        private static extern short SQLExecDirect(IntPtr statementHandle, string query, int textLength);

        [DllImport("odbc32.dll")]
        private static extern short SQLFetch(IntPtr statementHandle);

        [DllImport("odbc32.dll")]
        private static extern short SQLGetData(IntPtr statementHandle, short columnIndex, short targetType, IntPtr targetValue, int bufferLength, out int indicatorValue);

        [DllImport("odbc32.dll")]
        private static extern short SQLDisconnect(IntPtr connectionHandle);

        [DllImport("odbc32.dll")]
        private static extern short SQLFreeHandle(short handleType, IntPtr handle);

        [DllImport("odbc32.dll")]
        private static extern short SQLGetDiagRec(
            short handleType,
            IntPtr handle,
            short recordNumber,
            IntPtr sqlState,
            out int nativeError,
            IntPtr messageText,
            short bufferLength,
            out short textLength
        );

        public void Main()
        {
            // Initialize ODBC environment handle
            IntPtr environmentHandle = IntPtr.Zero;
            SQLAllocHandle(1, IntPtr.Zero, out environmentHandle);
            SQLSetEnvAttr(environmentHandle, 200, (IntPtr)3, 0);

            // Initialize ODBC connection and statement handles
            IntPtr connectionHandle = IntPtr.Zero;
            IntPtr statementHandle = IntPtr.Zero;
            short retcode;
            retcode = SQLAllocHandle(2, environmentHandle, out connectionHandle);

            try
            {
                // Connect to the database
                retcode = RetryLogicUsingODBCAPI(connectionHandle);

                if( retcode != 1  )
                    {
                        return;
                    }

                retcode = SQLAllocHandle(3, connectionHandle, out statementHandle);
                // Prepare and execute a query
                SQLExecDirect(statementHandle, "SELECT top 200 TextToSearch FROM PerformanceVarcharNVarchar", 60);

                // Fetch and display the result set
                int id = 0;
                while (SQLFetch(statementHandle) == 0)
                {
                    // Retrieve data for each column
                    id = id + 1;
                    int nameLength = 200;
                    IntPtr namePtr = Marshal.AllocHGlobal(nameLength);
                    SQLGetData(statementHandle, 1, 1, namePtr, nameLength, out nameLength);
                    string name = Marshal.PtrToStringAnsi(namePtr);

                    Console.WriteLine("ID: " + id);
                    Console.WriteLine("Name: " + name);

                    Marshal.FreeHGlobal(namePtr);
                }
            }
            catch (Exception ex)
            {
                // Handle any errors that occur
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                // Disconnect and free resources
                SQLDisconnect(connectionHandle);
                SQLFreeHandle(3, statementHandle);
                SQLFreeHandle(2, connectionHandle);
                SQLFreeHandle(1, environmentHandle);
            }

        }

        private short RetryLogicUsingODBCAPI(IntPtr connectionHandle)
        {
            int maxRetryAttempts = 5;
            int retryIntervalSeconds = 10;
            int retryCount = 0;
            short retcode = 0;

            TimeSpan ts;
            string elapsedTime;

            Stopwatch oConnTime = new Stopwatch();

            while (retryCount < maxRetryAttempts)
            {
                try
                {
                    retryCount++;
                    retcode = SQLConnect(connectionHandle, "DSNName", 7, "username", 8, "Password", 8);

                    if (retcode == 1)
                    {
                        ts = oConnTime.Elapsed;
                        elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
                        Console.WriteLine("Connected to the database. Time Spent:" + elapsedTime);
                        return retcode;
                    }
                    else
                    {
                        Console.WriteLine("SQLConnect failed with retcode: " + retcode);
                        GetODBCErrorDetails(connectionHandle);
                        Console.WriteLine("Retrying connection...in...{0} ms", (1000 * retryIntervalSeconds));
                        System.Threading.Thread.Sleep(1000 * retryIntervalSeconds);
                        retryIntervalSeconds = Convert.ToInt32(retryIntervalSeconds * 1.5);
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
            return -1;
        }

        static void GetODBCErrorDetails(IntPtr handle)
        {
            const int SQL_HANDLE_ENV = 1;
            const int SQL_HANDLE_DBC = 2;

            IntPtr sqlStatePtr = Marshal.AllocHGlobal(6);
            IntPtr messageTextPtr = Marshal.AllocHGlobal(1024);
            int nativeError;
            short textLength;

            short retcode = SQLGetDiagRec(
                SQL_HANDLE_DBC,
                handle,
                1,
                sqlStatePtr,
                out nativeError,
                messageTextPtr,
                1024,
                out textLength
            );

            if (retcode == 0)
            {
                string sqlState = Marshal.PtrToStringAnsi(sqlStatePtr);
                string messageText = Marshal.PtrToStringAnsi(messageTextPtr, textLength);
                Console.WriteLine("ODBC Error Details:");
                Console.WriteLine("SQLState: " + sqlState);
                Console.WriteLine("Native Error: " + nativeError);
                Console.WriteLine("Message: " + messageText);
            }
            else
            {
                Console.WriteLine("Failed to retrieve ODBC error details.");
            }

            Marshal.FreeHGlobal(sqlStatePtr);
            Marshal.FreeHGlobal(messageTextPtr);
        }
    }

}

This first of the code declares and imports the required functions from the odbc32.dll library using P/Invoke. These functions are used to interact with the ODBC API.

In the Main method, the ODBC environment handle is initialized using SQLAllocHandle function. The SQLSetEnvAttr function is used to set the environment attribute. Then, the ODBC connection and statement handles are initialized using SQLAllocHandle.

Inside the try block, the RetryLogicUsingODBCAPI method is called to establish a connection to the database. If the connection is successful (retcode = 1), a query is executed using SQLExecDirect. The result set is fetched using SQLFetch, and the data is displayed.

In case of any errors, the catch block handles and displays the exception message. The finally block is used to disconnect from the database and free the allocated resources.

The RetryLogicUsingODBCAPI method is responsible for implementing the connection retry logic. It attempts to connect to the database using SQLConnect within a while loop. If the connection is successful (retcode = 1), it returns the retcode. Otherwise, it displays the failure details, waits for a specified interval, and increases the interval for subsequent retries.

The GetODBCErrorDetails method retrieves ODBC error details using SQLGetDiagRec function. It takes the handle as input and retrieves the SQLState, native error code, and message text associated with the error.

Enjoy!