Oracle 사용을 위한 팁 (3)

Oracle을 사용할때는 ADODB의 Connection Timeout 설정이 무효하다.

따라서 아래와 같은 방법을 사용하여 Timeout 기능을 구현해야 한다.

Oracle Connection Timeout - VB

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.