PRB: Connection Timeout and Query Timeout Not Supported with Microsoft Oracle ODBC Driver and OLE DB Provider (251248)

MORE INFORMATION

When you attempt to set the Connection Timeout or Query Timeout with the Microsoft Oracle ODBC driver, the following error messages occur:

Attempting to set SQL_ATTR_LOGIN_TIMEOUT to 30 seconds with the SQLSetConnectAttr ODBC API:
Attempting to set SQL_ATTR_QUERY_TIMEOUT to 30 seconds by using SQLSetConnectAttr: Sometimes applications attempt to set the connection timeout and query timeout when the application attempts to make the connection to the database. These applications receive error messages indicating that the attempt to set these options failed. However, the application is still able to connect to Oracle because these are just informational messages, not fatal errors.

ADO ConnectionTimeout Workaround

Use the adAsyncConnect option with the Open method of the ADO Connection object. After the Open, begin a loop for a number of seconds until the value of the Connection object's State property has changed to adStateOpen or the desired number of seconds has elapsed. Inside the loop check the State property for the connection. If adStateOpen is returned the connection to the Oracle server was successful and you can break out of the loop. If, after a certain period of time the State property is still adStateConnecting, there is an issue of either a server down, a database down, or the connection is taking a very long time to connect. In that case just Close the connection and display a dialog box for the user that states the situation.

The following is an example of how you can do this:

Private Declare Function GetTickCount Lib "Kernel32" () As Long

Private Sub Connect_Click()

Dim startTime As Long

Dim conn As New ADODB.Connection
conn.Open "Provider=MSDAORA.1;Data Source=myOracleServer;", "myUser", "myPassword", adAsyncConnect

Dim timeout As Long
timeout = 60    'Number of seconds to wait before timing out

startTime = GetTickCount()

'Wait until timeout seconds have passed, or the connection is open
While ((GetTickCount() - startTime) < timeout * 1000) And (Not conn.State = adStateOpen)
Wend

If Not conn.State = adStateOpen Then
    MsgBox "Timeout occurred"
    If conn.State = adStateConnecting Then
        conn.Cancel
    End If
Else
    MsgBox "Connection is open!"
    conn.Close
End If

End Sub
				

NOTE: You cannot extend this workaround to work for a query timeout. After the query has been sent to the Oracle server, there is no way to cancel the query by using the Oracle OCI. In the case of the preceding connection timeout, you are canceling the request for the connection before it has been completed.