Solved

VB- RDO-Oracle

Posted on 1998-08-29
12
253 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

785 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