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 rdoEnginePublic en As rdoEnvironmentPublic cn1 As rdoConnectionPublic 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 FunctionIf 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))
