?
Solved

VB- RDO-Oracle

Posted on 1998-08-29
12
Medium Priority
?
279 Views
Last Modified: 2012-06-27
Hi,
I am using VB5.0 with RDO's. I want to know if one can get a set of records( in a resultset ) from a stored procedure from oracle. ( This can be done in SQL server). It is imp to be done through stored procedure? If it can be done t

0
Comment
Question by:disha
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 2

Expert Comment

by:chris_a
ID: 1431781
Forget the RDO, use ADO 2.0 it lets you get recordsets from stored procdures, does the async stuff from RDO and has a native OLEDB driver now
0
 
LVL 4

Expert Comment

by:tomook
ID: 1431782
Ditto chris_a, but you can do it in RDO if you have to. If you are doing new development, ADO is not a big step and MS has said RDO is no longer the current method and will become obsolete.
0
 

Expert Comment

by:vinoopauls
ID: 1431783
tomook can you please tell how it is possible in RDO.
ive tried but failed
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 1

Author Comment

by:disha
ID: 1431784
I have to do it with RDO....and oracle...and stored proc...how do u do it..could u pls be more explicit...Thanx
0
 
LVL 1

Author Comment

by:disha
ID: 1431785
RDO will handle the resultset...but what do u return from u'r PL/SQL stored procedure??

0
 

Accepted Solution

by:
susdash earned 400 total points
ID: 1431786
I am doing in a different way. I am passing a variant array to the storage procedure as a Byref Parameter. This array contains all the input and output values.Execute the storage procedure with the individual component of the array. Then I transfer the values into a resultset from that array.

 Otherwise make a prepare query for your storage procedure. Pass the the values as set parameters. Then u can get the result as a resultset.
0
 
LVL 1

Author Comment

by:disha
ID: 1431787
Hi Susdash.
how do u accept the array in stored procedure??..also
how do u pass array to that proc using RDO..
the second suggestion...does it work with oracle..i think it
does only with SQL.
0
 

Expert Comment

by:vinoopauls
ID: 1431788
I still dont get how one can return a set of records from Oracle
0
 

Expert Comment

by:susdash
ID: 1431789
Step1 : Declare an avnArray(0 to No of Cols)  for thet given Storage procedure. Then store all the input parametersin the array. Set all the output parameters to NULL.Remember, the 1st parameter should be from avnArray(1)
       
        Declare mardoqryQueries as rdoQuery

Step 2 : Initialize the storage procedure with the Handle(eg. lngQryH)

Step3: Prepare the parameters as follows

 
    lngLastParameter = UBound(avnArray)
   
    'Following loop sets all the parameters
    For lngIndex = 1 To lngLastParameter
     
      strParamNo = vbCrLf & "Parameter No = " & lngIndex _
      & " ; Value = " & avnArray(lngIndex)
     
      'Set the parameter for the prepared statement
      mardoqryQueries(lngQryH).rdoParameters(lngIndex).Value = orvnFields(lngIndex)
     
    Next lngIndex   'End of for loop
   
  End If            'End of checking whether any parameters exist


Step 4: Execute the SP and get back the array
 

  With mardoqryQueries(lngQryH)
 
    .Execute                                         'Execute the stored procedure
   
   
      If .rdoParameters.Count <> 0 Then
     
        'If stored procedure has returned some value
        For lngIndex = 0 To lngLastParameter
   
          'Get the parameter values
          avnArray(lngIndex) = .rdoParameters(lngIndex).Value
       
        Next lngIndex
       
      End If
     
    End If
   
  End With
 

Step 5: Now you can store the values from the array to your resultset.

0
 

Expert Comment

by:vinoopauls
ID: 1431790
But where are we getting the set of records one set out to get??This will only return the
output parameters. In SQL Server it is possible for the sp to return  a set of records in the
form of a select statement being the last executable statement in it.
Can the same function be done here?
0
 

Expert Comment

by:vinoopauls
ID: 1431791
But where are we getting the set of records one set out to get??This will only return the
output parameters. In SQL Server it is possible for the sp to return  a set of records in the
form of a select statement being the last executable statement in it.
Can the same function be done here?
0
 
LVL 1

Author Comment

by:disha
ID: 1431792
I dont think so ..it can be done...because how does PL SQL block
(SP) return the array ...i know RDO can accept it...but how will oracle send it.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question