Use RDO to Access Stored Functions and Procedures on a Server

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))