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:
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.
Additional query words: time out connectiontimeout connecttimeout querytimeout commandtimeout
Keywords: kboracle kbprovider kbcodesnippet kbdatabase kbprb kbdriver KB251248