系統使用VB6.0,數據庫為Oracle 10g,使用ADODB. RECORDSET連接數據庫,正常情況都能執行OK,當執行多個系統程式,每一秒發一個transaction調用store procedure,就會出現此錯誤: Object variable or With block variable not set程序結構為:調用程序部分:
Call SP_MOVE_OUT(tmpRetCode, tmpRetDesc, PG_FAB, Trim(array1(tmpBookMark, 0)), G_WorkCtr, G_EqName, " ", PG_OPERATORS, TxtComment.Text, "1", 0, " ", " ", " ")如下為SP_MOVE_OUT的function:
Public Function SP_MOVE_OUT(RET_CODE As Integer, RET_DESC As String, FAC_ID As String, Pnl_ID As String, OPER As Integer, EQ_Nbr As String, NEW_CST As String, OPERATORS As String, Comments As String, MODE As String, Mat_Count As Integer, Mat_Type As String, Mat_ID As String, Mat_Lot As String) As Boolean
On Error GoTo ErrSP_MOVE_OUT
SP_MOVE_OUT = False
Dim SPCALL As New ADODB.Command
Set SPCALL.ActiveConnection = cnnWIP
SPCALL.CommandText = "SP_MOVE_OUT"
SPCALL.CommandType = adCmdStoredProc Dim param(14) As New ADODB.Parameter
Set param(0) = SPCALL.CreateParameter("RETURN CODE", adInteger, adParamOutput, , RET_CODE)
SPCALL.Parameters.Append param(0)
Set param(1) = SPCALL.CreateParameter("RETURN DESC", adChar, adParamOutput, 240, RET_DESC)
SPCALL.Parameters.Append param(1)
Set param(2) = SPCALL.CreateParameter("FAB ID", adChar, adParamInput, 1, NU(FAC_ID))
SPCALL.Parameters.Append param(2)
Set param(3) = SPCALL.CreateParameter("PANEL ID", adChar, adParamInputOutput, 12, Pnl_ID)
SPCALL.Parameters.Append param(3)
Set param(4) = SPCALL.CreateParameter("WORK CENTER", adInteger, adParamInputOutput, , OPER)
SPCALL.Parameters.Append param(4)
Set param(5) = SPCALL.CreateParameter("EQUIP NUMBER", adChar, adParamInput, 10, EQ_Nbr)
SPCALL.Parameters.Append param(5)
Set param(6) = SPCALL.CreateParameter("NEW CASSETTE", adChar, adParamInput, 30, NEW_CST) '--8 to 30
SPCALL.Parameters.Append param(6)
Set param(7) = SPCALL.CreateParameter("OPERATORS", adChar, adParamInput, 6, OPERATORS)
SPCALL.Parameters.Append param(7)
Set param(8) = SPCALL.CreateParameter("COMMENTS", adChar, adParamInput, 80, Comments)
SPCALL.Parameters.Append param(8)
Set param(9) = SPCALL.CreateParameter("MODE", adChar, adParamInput, 1, MODE)
SPCALL.Parameters.Append param(9)
Set param(10) = SPCALL.CreateParameter("MATERIAL COUNT", adInteger, adParamInput, , Mat_Count)
SPCALL.Parameters.Append param(10)
Set param(11) = SPCALL.CreateParameter("MATERIAL TYPE", adChar, adParamInput, 160, Mat_Type)
SPCALL.Parameters.Append param(11)
Set param(12) = SPCALL.CreateParameter("MATERIAL ID", adChar, adParamInput, 160, Mat_ID)
SPCALL.Parameters.Append param(12)
Set param(13) = SPCALL.CreateParameter("MATERIAL LOT", adChar, adParamInput, 330, Mat_Lot)
SPCALL.Parameters.Append param(13)
Set param(14) = SPCALL.CreateParameter("HOSTNAME", adChar, adParamInput, 30, PG_HostName)
SPCALL.Parameters.Append param(14)
If ExeSP(SPCALL) = True Then
RET_CODE = SPCALL(0)
RET_DESC = Trim(SPCALL(1))
Call DelParam(SPCALL)
Set SPCALL = Nothing
SP_MOVE_OUT = True
Exit Function
End If
ErrSP_MOVE_OUT:
SP_MOVE_OUT = False
RET_CODE = -1
RET_DESC = "EXECUTE STORE PROCEDURE FAIL!!"
Call DelParam(SPCALL)
Set SPCALL = Nothing
WriteErr "Err in <<SP_MOVE_OUT>>, Err desc=" & RET_DESC, True
End Function
Public Function ExeSP(SPCMD As ADODB.Command, Optional LogType As eLogType = detail) As Boolean
On Error GoTo ErrHandle
Dim ISPLog As clsLog
Dim S_timer As Single
Dim E_Timer As Single
Dim rsTmp As New ADODB.RecordSet
Dim strPath As String
Dim i As IntegerDim intStart As Integer
Dim intEnd As Integer
Dim strSPname As String
Dim strSQL As String Set ISPLog = New clsLog
ISPLog.LogINI SPLog
With SPCMD
If LogType <> Skip Then
ISPLog.WriteToLog ""
WriteLog "!! Start Call store SP:" & SPCMD.CommandText
End If
If LogType = detail Then
For i = 0 To .Parameters.Count - 1
If .Parameters(i).Direction <> adParamOutput Then
If .Parameters(i).Value = "" Then .Parameters(i).Value = " "
ISPLog.WriteToLog .Parameters(i).Name & ":" & .Parameters(i).Value
End If
Next i
End If
S_timer = Timer SPCMD.Execute
E_Timer = Timer - S_timer
If LogType <> Skip Then
WriteLog "!! Finish Call store SP:" & SPCMD.CommandText & ",Spend[" & E_Timer & "]"
If PG_KeepSP = "Y" Then
intStart = InStr(1, SPCMD.CommandText, "SP", vbTextCompare)
intEnd = InStr(1, SPCMD.CommandText, "(", vbTextCompare)
strSPname = Mid(SPCMD.CommandText, IIf(intStart = 0, 1, intStart), intEnd - intStart)
Dim ssql As String
Dim rss As ADODB.RecordSet
Dim inst As String
Dim session As Integer
ssql = "SELECT sys.v_$instance.instance_number inst_id,(SELECT COUNT(*) FROM sys.v_$session where username not like 'SYS%') session_qty FROM sys.v_$instance"
Set rss = GetRecordSet(ssql, cnnWIP)
If rss.RecordCount > 0 Then
inst = rss(0)
session = rss(1)
End If
strSQL = "" & "insert into STRESS trans_time,SP_name,oper,respone_time,hostname,inst_id,session_qty) "
strSQL = strSQL & "values(TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24.MI.SSxFF'),'" & strSPname & "'," & PG_WorkCtr & ",'" & Replace(E_Timer, ",", ".") & "','" & PG_HostName & "','" & inst & "'," & session & ");"
If updateTable(strSQL, cnnWIP) <> True Then
MsgBox "Insert [STRESS] table fail!", vbCritical
Exit Function
End If
End If
WriteLog "Out PUT Result:"
For i = 0 To .Parameters.Count - 1
If .Parameters(i).Direction <> adParamInput Then
ISPLog.WriteToLog .Parameters(i).Name & ":" & .Parameters(i).Value
End If
Next i
End If
End With
ExeSP = True
Exit Function
ErrHandle:
WriteErr "Err in <<ExeSP>>, Err desc=" & Err.Description, True
ExeSP = False
End Function出錯位置為 SPCMD.Execute,即在調用store procedure時就出錯了,麻煩哪位高手幫忙指點一下,目前公司在做DB的壓力測試,此問題出現後,派專人監控系統都忙不過來,解決了加分,先跪拜謝恩啦
Call SP_MOVE_OUT(tmpRetCode, tmpRetDesc, PG_FAB, Trim(array1(tmpBookMark, 0)), G_WorkCtr, G_EqName, " ", PG_OPERATORS, TxtComment.Text, "1", 0, " ", " ", " ")如下為SP_MOVE_OUT的function:
Public Function SP_MOVE_OUT(RET_CODE As Integer, RET_DESC As String, FAC_ID As String, Pnl_ID As String, OPER As Integer, EQ_Nbr As String, NEW_CST As String, OPERATORS As String, Comments As String, MODE As String, Mat_Count As Integer, Mat_Type As String, Mat_ID As String, Mat_Lot As String) As Boolean
On Error GoTo ErrSP_MOVE_OUT
SP_MOVE_OUT = False
Dim SPCALL As New ADODB.Command
Set SPCALL.ActiveConnection = cnnWIP
SPCALL.CommandText = "SP_MOVE_OUT"
SPCALL.CommandType = adCmdStoredProc Dim param(14) As New ADODB.Parameter
Set param(0) = SPCALL.CreateParameter("RETURN CODE", adInteger, adParamOutput, , RET_CODE)
SPCALL.Parameters.Append param(0)
Set param(1) = SPCALL.CreateParameter("RETURN DESC", adChar, adParamOutput, 240, RET_DESC)
SPCALL.Parameters.Append param(1)
Set param(2) = SPCALL.CreateParameter("FAB ID", adChar, adParamInput, 1, NU(FAC_ID))
SPCALL.Parameters.Append param(2)
Set param(3) = SPCALL.CreateParameter("PANEL ID", adChar, adParamInputOutput, 12, Pnl_ID)
SPCALL.Parameters.Append param(3)
Set param(4) = SPCALL.CreateParameter("WORK CENTER", adInteger, adParamInputOutput, , OPER)
SPCALL.Parameters.Append param(4)
Set param(5) = SPCALL.CreateParameter("EQUIP NUMBER", adChar, adParamInput, 10, EQ_Nbr)
SPCALL.Parameters.Append param(5)
Set param(6) = SPCALL.CreateParameter("NEW CASSETTE", adChar, adParamInput, 30, NEW_CST) '--8 to 30
SPCALL.Parameters.Append param(6)
Set param(7) = SPCALL.CreateParameter("OPERATORS", adChar, adParamInput, 6, OPERATORS)
SPCALL.Parameters.Append param(7)
Set param(8) = SPCALL.CreateParameter("COMMENTS", adChar, adParamInput, 80, Comments)
SPCALL.Parameters.Append param(8)
Set param(9) = SPCALL.CreateParameter("MODE", adChar, adParamInput, 1, MODE)
SPCALL.Parameters.Append param(9)
Set param(10) = SPCALL.CreateParameter("MATERIAL COUNT", adInteger, adParamInput, , Mat_Count)
SPCALL.Parameters.Append param(10)
Set param(11) = SPCALL.CreateParameter("MATERIAL TYPE", adChar, adParamInput, 160, Mat_Type)
SPCALL.Parameters.Append param(11)
Set param(12) = SPCALL.CreateParameter("MATERIAL ID", adChar, adParamInput, 160, Mat_ID)
SPCALL.Parameters.Append param(12)
Set param(13) = SPCALL.CreateParameter("MATERIAL LOT", adChar, adParamInput, 330, Mat_Lot)
SPCALL.Parameters.Append param(13)
Set param(14) = SPCALL.CreateParameter("HOSTNAME", adChar, adParamInput, 30, PG_HostName)
SPCALL.Parameters.Append param(14)
If ExeSP(SPCALL) = True Then
RET_CODE = SPCALL(0)
RET_DESC = Trim(SPCALL(1))
Call DelParam(SPCALL)
Set SPCALL = Nothing
SP_MOVE_OUT = True
Exit Function
End If
ErrSP_MOVE_OUT:
SP_MOVE_OUT = False
RET_CODE = -1
RET_DESC = "EXECUTE STORE PROCEDURE FAIL!!"
Call DelParam(SPCALL)
Set SPCALL = Nothing
WriteErr "Err in <<SP_MOVE_OUT>>, Err desc=" & RET_DESC, True
End Function
Public Function ExeSP(SPCMD As ADODB.Command, Optional LogType As eLogType = detail) As Boolean
On Error GoTo ErrHandle
Dim ISPLog As clsLog
Dim S_timer As Single
Dim E_Timer As Single
Dim rsTmp As New ADODB.RecordSet
Dim strPath As String
Dim i As IntegerDim intStart As Integer
Dim intEnd As Integer
Dim strSPname As String
Dim strSQL As String Set ISPLog = New clsLog
ISPLog.LogINI SPLog
With SPCMD
If LogType <> Skip Then
ISPLog.WriteToLog ""
WriteLog "!! Start Call store SP:" & SPCMD.CommandText
End If
If LogType = detail Then
For i = 0 To .Parameters.Count - 1
If .Parameters(i).Direction <> adParamOutput Then
If .Parameters(i).Value = "" Then .Parameters(i).Value = " "
ISPLog.WriteToLog .Parameters(i).Name & ":" & .Parameters(i).Value
End If
Next i
End If
S_timer = Timer SPCMD.Execute
E_Timer = Timer - S_timer
If LogType <> Skip Then
WriteLog "!! Finish Call store SP:" & SPCMD.CommandText & ",Spend[" & E_Timer & "]"
If PG_KeepSP = "Y" Then
intStart = InStr(1, SPCMD.CommandText, "SP", vbTextCompare)
intEnd = InStr(1, SPCMD.CommandText, "(", vbTextCompare)
strSPname = Mid(SPCMD.CommandText, IIf(intStart = 0, 1, intStart), intEnd - intStart)
Dim ssql As String
Dim rss As ADODB.RecordSet
Dim inst As String
Dim session As Integer
ssql = "SELECT sys.v_$instance.instance_number inst_id,(SELECT COUNT(*) FROM sys.v_$session where username not like 'SYS%') session_qty FROM sys.v_$instance"
Set rss = GetRecordSet(ssql, cnnWIP)
If rss.RecordCount > 0 Then
inst = rss(0)
session = rss(1)
End If
strSQL = "" & "insert into STRESS trans_time,SP_name,oper,respone_time,hostname,inst_id,session_qty) "
strSQL = strSQL & "values(TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24.MI.SSxFF'),'" & strSPname & "'," & PG_WorkCtr & ",'" & Replace(E_Timer, ",", ".") & "','" & PG_HostName & "','" & inst & "'," & session & ");"
If updateTable(strSQL, cnnWIP) <> True Then
MsgBox "Insert [STRESS] table fail!", vbCritical
Exit Function
End If
End If
WriteLog "Out PUT Result:"
For i = 0 To .Parameters.Count - 1
If .Parameters(i).Direction <> adParamInput Then
ISPLog.WriteToLog .Parameters(i).Name & ":" & .Parameters(i).Value
End If
Next i
End If
End With
ExeSP = True
Exit Function
ErrHandle:
WriteErr "Err in <<ExeSP>>, Err desc=" & Err.Description, True
ExeSP = False
End Function出錯位置為 SPCMD.Execute,即在調用store procedure時就出錯了,麻煩哪位高手幫忙指點一下,目前公司在做DB的壓力測試,此問題出現後,派專人監控系統都忙不過來,解決了加分,先跪拜謝恩啦
Set SPCALL.ActiveConnection = cnnWIP 在多线程的情况下, 所有的SPCALL实例都在使用同一个cnnWIP进行数据库连接, 当压力大的时候, 一定会出现问题推荐解决方案,
1. 每一个 SPCALL 使用一个新的 Connection 实例。
2. 如果觉得方案1太浪费资源,可以使用线程池
3. 也可以配置Oracle 的线程池设定 。祝你好运
function SP_MOVE_OUT 是一个公用模块,有很多form都会调用,修改VB代码是不现实的做法,
有没有可能在oracle数据库的设置上做动作啊?