Solved

VB- RDO-Oracle

Posted on 1998-08-29
12
256 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

820 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