Use RDO to Access Stored Functions and Procedures on a Server

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


Share the Post: