帮助〜我有一个关于VB6调用Oracle StoredProcedures的问题。 [英] Help~I have a problem about VB6 call Oracle StoredProcedures.

问题描述

==============

我的主要代码：

============= =



私有子Chk_RaCosignStatus（）

Dim sSQL As String

Dim RaNoArray（）As String

Dim res As String

Dim i As Integer

On Error GoTo ErrorHandle



''获取数据状态为ra_cosignstatus的活动表格并保留racosignno

sSQL =SELECT * FROM RA_COSIGNSTATUS WHERE STATUS =''ACTIVE''和UPDATETIME> SYSDATE - 按更新时间排序30



调用ODBC_QuerySQL（sSQL，myDBconn，myDBrs）



而不是myDBrs.EOF而不是IsNull（myDBrs）

如果myDBrs.Fields（status）=ACTIVE那么

ReDim保留RaNoArray（i）

RaNoArray（i）=修剪（IIf（IsNull（myDBrs.Fields（RANO））=真，，myDBrs.Fields（RANO）））

i = i + 1

结束如果

NextRule：

myDBrs.MoveNext

Wend



如果myDBrs.State = adStateOpen那么myDBrs.Close



如果i> 0那么

对于i = 0到UBound（RaNoArray）



''映射所有RANO

res = ODBC_Execute_RaCosignActive（RaNoArray（i），GlobalData.RaCosignActive_Name，myDBconn， myDBcomd）

如果res = 0那么

''记录映射结果e，返回0 resule是真的

logDatatoFileMain，0，Excute RACosignActive Procedure Success。

Else

''记录映射resule，return 1 resule is false

logDatatoFileMain，0，Excute RACosignActive Procedure Error。

结束如果



下一页

结束如果



退出Sub

ErrorHandle：

logDatatoFileMain，0，Chk_RaCosignStatus ErrMsg：& Err.Description&

结束次级





============== ==========================

调用oracle storeprocedure的函数：

== ======================================

==============
My main code:
==============

Private Sub Chk_RaCosignStatus()
Dim sSQL As String
Dim RaNoArray() As String
Dim res As String
Dim i As Integer
On Error GoTo ErrorHandle

''Get data which status is "ACTIVE form table of "ra_cosignstatus" and keep "racosignno"
sSQL = "SELECT * FROM RA_COSIGNSTATUS WHERE STATUS = ''ACTIVE'' AND UPDATETIME > SYSDATE - 30 order by updatetime"

Call ODBC_QuerySQL(sSQL, myDBconn, myDBrs)

While Not myDBrs.EOF And Not IsNull(myDBrs)
If myDBrs.Fields("status") = "ACTIVE" Then
ReDim Preserve RaNoArray(i)
RaNoArray(i) = Trim(IIf(IsNull(myDBrs.Fields("RANO")) = True, "", myDBrs.Fields("RANO")))
i = i + 1
End If
NextRule:
myDBrs.MoveNext
Wend

If myDBrs.State = adStateOpen Then myDBrs.Close

If i > 0 Then
For i = 0 To UBound(RaNoArray)

''Mapping all RANO
res = ODBC_Execute_RaCosignActive(RaNoArray(i), GlobalData.RaCosignActive_Name, myDBconn, myDBcomd)
If res = 0 Then
''Record mapping resule, return 0 resule is true
logDatatoFile "Main", 0, "Excute RACosignActive Procedure Success."
Else
''Record mapping resule, return 1 resule is false
logDatatoFile "Main", 0, "Excute RACosignActive Procedure Error."
End If

Next
End If

Exit Sub
ErrorHandle:
logDatatoFile "Main", 0, "Chk_RaCosignStatus ErrMsg: " & Err.Description & ""
End Sub


========================================
Function of call oracle storeprocedure:
========================================

Public Function ODBC_Execute_RaCosignActive(ByVal inputval As String, ByVal SPName As String, ByRef iRS_Conn As ADODB.Connection, Optional ByRef iRS_Command As ADODB.Command) As Integer
On Error GoTo ErrorHandle
    Dim res As String
    Dim adoP As ADODB.Parameter
    Set iRS_Command.ActiveConnection = iRS_Conn
    iRS_Command.CommandType = adCmdStoredProc
    iRS_Command.CommandText = SPName

    Set adoP = iRS_Command.CreateParameter("@s_RANO", adVarChar, adParamInput, 10, inputval)
    iRS_Command.Parameters.Append adoP

    Set adoP = iRS_Command.CreateParameter("@o_RTNSTR", adVarChar, adParamOutput, 10)
    iRS_Command.Parameters.Append adoP

    iRS_Command.Execute

    Set iRS_Command.ActiveConnection = Nothing

    ODBC_Execute_RaCosignActive = 0

    Exit Function
ErrorHandle:
    ODBC_Execute_RaCosignActive = 1
    logDatatoFile "modSpcDB", 0, "ODBC_Execute_RaCosignActive ErrMsg: " & Err.Description & ""
End Function


============
My problem is:
============

My main code first call call ODBC_Execute_RaCosignActive in loop(For i = 0 To UBound(RaNoArray)) will work.

But second call ODBC_Execute_RaCosignActive,

it will occor error when it run iRS_Command.Execute

error description is ORA-01036: illegal variable name/number

Someone can help me finding what;s problem is it ???!!!

推荐答案

adodb。 command.parameters需要再次清理call storeprocedure



所以在iRS_Command.Execute之后重置iRS_Commandaf将解决问题。

adodb.command.parameters need to clear up for call storeprocedure again

so reset iRS_Commandaf after iRS_Command.Execute will fix the problem.

这篇关于帮助〜我有一个关于VB6调用Oracle StoredProcedures的问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！