Oracle Connection的Timeout要怎麼設??

目前結論是不能設!!不管您是用Oledb或OracleClient都不行!

最近在寫一個Web Service讓廠商叫用,後端資料庫是ORACLE,用Oledb的方式連到資料庫抓資料。

當初規格是開10秒內要回應,昨天下班前廠商說要測試資料庫連不上的情形,所以我就把DB關掉,但是廠商反映Web Service都會超過10秒才回應不符規格要求,我在ConnectionString的後面加上Connect Timeout=5,測試一下!

嗯!!DataAdapter.SelectCommand.Connection.ConnectionTime如願的被改為5了,DataAdapter.Fill()時也會丟出exception,ex.Message怪怪的,什麼叫"多重步驟 OLE DB 操作發生錯誤。請檢查每一個可用的 OLE DB 狀態值",看不太懂...應該沒問題吧!所以我就下班讓廠商慢慢測囉!

今天快中午,廠商又打電話來,說他測完了叫我可以開DB!...咦!我不是一早就開DB了,追蹤程式才發現是只要Connection.Open()就會出這個exception,拿掉連線字串中的Connect Timeout=5就沒問題!所以就改一下讓廠商繼續測其他項目!

Google一下錯誤訊息,在微軟找到答案http://support.microsoft.com/kb/269495/zh-tw 應該是文中所說的但 ADO 連線字串有問題,後來又查了一堆資料,也作了一堆的測試....最後在MSDN文件中找到確定沒解的答案http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection(vs.71).aspx

Note Unlike the Connection object in the other .NET Framework data providers (SQL Server, OLE DB, and ODBC), OracleConnection does not support a ConnectionTimeout property. Setting a connection timeout using a property or in the connection string has no effect and value returned is always zero. OracleConnection also does not support a Database property or a ChangeDatabase method.

微軟這邊沒支援,我想只好再找看看Oracle有沒有什麼方法可以作限制,試了半天改sqlnet.ora的參數names.request_retries、names.initial_retry_timeout、SQLNET.INBOUND_CONNECT_TIMEOUT、SQLNET.RECV_TIMEOUT、SQLNET.RECV_TIMEOUT都試過了都沒效!

明天上班再試試吧....

============2008/03/06 補充=====

時光飛逝,其實隔天我在MSDN就找到解決的方法了,他是一個VB6的範例,印象中他是用Timer去作計時,不過我現在忘記是用什麼關鍵字去搜尋到的,所以沒有辦法附原始範例的連結囉....

既然我們用的是.Net,所以當然不要用Timer這種方式來作,我的解法是用.Net多執行緒的方式來解,範例如下:

Imports System.Data.OracleClient
Public Class frmOracleClient
    Dim StartTime, EndTime As DateTime
    Dim Cn As New OracleConnection

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim th As Threading.Thread
        th = New Threading.Thread(AddressOf CreateConnection)
        Try
            Dim i As Integer = 0
            th.Start()
            '判斷是否另一個執行緒是否執行完畢戓是超過5秒
            While th.ThreadState <> Threading.ThreadState.Stopped And i < 5  
            i = DateDiff(DateInterval.Second, StartTime, Now)
            End While
            If th.ThreadState = Threading.ThreadState.Running Then
                th.Abort()
            End If
        Catch ex As Exception

        End Try
        MessageBox.Show("Stop")

    End Sub

    Private Sub CreateConnection()
        Try
            Cn.ConnectionString = "Data Source=Your_DB_Name;User ID=Your_ID;Password=Your_Password"
            StartTime = Now
            Cn.Open()
        Catch ex As Exception
            EndTime = Now
            MessageBox.Show(ex.Message & vbCrLf & _
                        "StartTime : " & StartTime.ToString & vbCrLf & _
                        "EndTime : " & EndTime.ToString)
        End Try

    End Sub
End Class