Microsoft KB Archive/251248

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

Article ID: 251248

Article Last Modified on 9/18/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5

-



This article was previously published under Q251248



SYMPTOMS
The Microsoft Oracle ODBC driver and Microsoft OLE DB Provider for Oracle do not support setting connection timeouts or query timeouts.



CAUSE
This is a limitation of version 7.x of the Oracle Call Interface (OCI). Microsoft's Oracle ODBC driver and OLEDB provider use the 7.x OCI API calls to communicate with the Oracle server even if the Oracle server is version 8.x or 9i. This applies to all version of the Microsoft Oracle ODBC driver and OLE DB Provider.



RESOLUTION
There is no workaround to allow setting a query timeout.

For connection timeouts you can work around the issue, if your database application framework supports asynchronous operations. By putting the call to open the connection in an asynchronous loop and checking the status of the connection, you can terminate the connection if it does not occur in the stated duration of time. The implementation of this solution is application-dependent, but an example using ActiveX Data Objects (ADO) is shown in the "More Information" section of this article.



STATUS
This behavior is by design.



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:

szSqlState = "S1C00"

szErrorMsg = "[Microsoft][ODBC driver for Oracle]Driver not capable"

Attempting to set SQL_ATTR_QUERY_TIMEOUT to 30 seconds by using SQLSetConnectAttr:

szSqlState = "S1C00"

szErrorMsg = "[Microsoft][ODBC driver for Oracle]Driver not capable"

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.

Additional query words: time out connectiontimeout connecttimeout querytimeout commandtimeout

Keywords: kboracle kbprovider kbcodesnippet kbdatabase kbprb kbdriver KB251248

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.