Microsoft KB Archive/251248

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:52, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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