Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Jul 2, 1999

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))
Kevin Shieh
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date