This code illustrates a VB5 routine that calls a given server's stored functions or procedures. The first parameter is the stored function procedure name that resides on the server (ORACLE, SQL Server, and so on). The second parameter is a dynamic array that takes an arbitrary number of input arguments for the stored function or procedure. It returns data from the server:
Public db As rdoEngine
Public en As rdoEnvironment
Public cn1 As rdoConnection
Public Function Get_STOREDFUN(sFun As String, ParamArray _
sColumns() As Variant) As Variant [rdoResultset]
Dim sSQL As String
Dim Rs As rdoResultset
Dim Qry As rdoQuery
Dim X As Integer
sSQL = "{ ? = Call " & sFun
If UBound(sColumns) = -1 Then
'Do Nothing here
Else
sSQL = sSQL & " ("
For X = 0 To UBound(sColumns)
sSQL = sSQL & "?,"
Next
sSQL = Left(sSQL, Len(sSQL) - 1) & ")"
End If
sSQL = sSQL & " }"
Set Qry = cn1.CreateQuery("doFunction", sSQL)
Qry(0).Direction = rdParamReturnValue
For X = 0 To UBound(sColumns)
Qry(X + 1).Direction = rdParamInput
Qry.rdoParameters(X + 1) = sColumns(X)
Next
Set Rs = Qry.OpenResultset(rdOpenForwardOnly, _
rdConcurReadOnly)
Get_STOREDFUN = Qry(0)
[Set Get_STOREDFUN = Rs]
End Function
If you have three stored functions in a server, each one takes a different number of input arguments. You can call the same VB5 routine to get returning data:
sPrdPlant = Get_STOREDFUN("ZIP_2PLANT", CStr(txtZip))
sControl = Get_STOREDFUN("CONTRNUM")
fItemPrice = Get_STOREDFUN("GET_UnitPrice", Cstr(prd), _
Clng(qty))