Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

VB- RDO-Oracle

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
disha
Asked:
disha
  • 4
  • 4
  • 2
  • +2
1 Solution
 
chris_aCommented:
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
 
tomookCommented:
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
 
vinoopaulsCommented:
tomook can you please tell how it is possible in RDO.
ive tried but failed
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dishaAuthor Commented:
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
 
dishaAuthor Commented:
RDO will handle the resultset...but what do u return from u'r PL/SQL stored procedure??

0
 
susdashCommented:
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
 
dishaAuthor Commented:
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
 
vinoopaulsCommented:
I still dont get how one can return a set of records from Oracle
0
 
susdashCommented:
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
 
vinoopaulsCommented:
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
 
vinoopaulsCommented:
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
 
dishaAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now