Passing recordset back from a stored procedure to a VB COM 1

[towser]

I have a VB com which must take a user input ( ref number )selected on an ASP page .

A Stored Procedure is then executed on an Oracle DB with the ref number as a passed param. The Stored Procedure will then pass back many records back to the VB component.

I am having great trouble with in accessing the returned recordset in VB, any tips ?

So far I have defined all the returning fields in the Stored Procedure as Tables indexed by binary integers and then used Resultset in VB to access them into a recordset, this is working to a point, but for some reason I get an unspecified error when I attempt to pass anymore than 13 parameters ????

Any help or pointers will be much appreciated.

 

[Antzz]

Can you just show me the sample piece of code where the data is returned from Oracle and you are trapping it inside VB?

 

[towser]

Antzz, here is the VB code you requested I hope you can help.


Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command

Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim a As String


Private Sub Command1_Click()
Set Rs.Source = CPw1

Rs.Open
Print Rs.Fields.Count
While Not Rs.EOF

Print "Client Number: " & Rs(0)
' Print the rest of the parameters out here

MsgBox "Client Number: " & Rs(0)
Rs.MoveNext
Wend

Rs.Close
End Sub


Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
Conn = "Provider=MSDAORA.1;Password=******;User ID=*****;Data Source=trackdev"

Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With

'QSQL = "{call test_claimtracking.get_summaries({resultset 500, OUT_client_number, OUT_claim_branch, OUT_claim_department, OUT_claim_prefix, OUT_claim_number, OUT_broker_reference, OUT_accident_date, OUT_registration_number, OUT_driver_title, OUT_driver_initial, OUT_driver_name, OUT_insurance_year, OUT_policy_prefix, OUT_policy_number, OUT_policy_section, OUT_paid_ad, OUT_paid_ft, OUT_paid_tppd, OUT_paid_tppi, OUT_total_paid, OUT_estimated_ad, OUT_estimated_ft, OUT_estimated_tppd, OUT_estimated_tppi, OUT_total_estimated, OUT_grand_total,})}"

Set CPw1 = New ADODB.Command

With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With

Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set Rs = Nothing
End Sub

 

[Antzz]

OK...I got a question for you. I see some parameters in your stored procedure as prefixed with the word "OUT". Does it mean that you are passing out these parameters? If so, then you can try another method.

ADO allows you to browse through recordsets. So you can make these parameters as a record as in a "Select" statement and execute it and then pass your original recordset out.

Does it make sense to u?

Let me know if you need more explanation.

 

[towser]

Antzz, I'm sorry I don't understand your alternative method, please explain.

But I may have found the reason why I'm getting problems. On the Microsoft site it states if proper Oracle patches have not been installed you application may have access problems if a stored procedure has more than 10 output params !!!! I will speak to our DBA's.

Thanks

 

[Antzz]

OK here goes....
In a stored procedure, you do some processing and then return your recordset :
Select field1, field2, field3,....etc from <tablename>
Where <condition>

Instead of returning parameters as OUT, you can also return them in a SELECT statement :
Select param1, param2, param3,.....etc.

Now in the frontend, once you browse through the first recordset, you can jump onto the second recordset(in this case it is the second SELECT statement)by using the NextRecordset method of the recordset object.

Let me know if it helped, because this is the way I do in SQL Server and I am sure it should work for Oracle too unless of course you have the patch problem.

 

[PongPing]

i'm not sure if i totally caught your problem - but:

Instead of returning a couple of out parameters you can get a complete recordset back from oracle. this article explains all you need to know about that

http://www.aspwatch.com/c/200028/d45A6239D524A11D4AEEC00A0C9E95208.asp?printme=1

This did it for me.. hope it helps

 

[towser]

PongPing, many thanks that works great from an ASP page.

As with your solution and mine which used a resulset they both work when applied in an ASP page, but when I deploy them in a VB component I get an unspecified error. This tells me we have something not quite right set up somewhere !!

As deadlines are closing in I am dumping the component idea and using your ref cursor solution.

Cheers

 

[WP]

I am trying really hard to get my head round the syntax of all this but failing miserably.

I am developing an app which has a grid filled from an Oracle view.

At present I just fill from direct SQL using the following code:

Dim objConn as ADODB.Connection
Dim obhRS as ADODB.Recordset

Dim gstrOracleSystem, gstrOracleUser, gstrOraclePwd As String

gstrOracleSystem = GetDbName() :gstrOracleUser = GetUser():gstrOraclePwd = GetPwd()

Set objConn = New ADODB.Connection
objConn.Open &quot;Provider=msdaora; Data Source=&quot; & gstrOracleSystem & &quot;;&quot; & _
&quot;User Id=&quot; & gstrOracleUser & &quot;; Password=&quot; & _
gstrOraclePwd & &quot;;&quot;

objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.Open &quot;select * from V_OMLOB_DB2&quot;, objConn, adOpenStatic, adLockOptimistic

Set grdDataGrid.DataSource = objRS

I would like to refine this further and have a server-side SP return the above recordset.

Can someone help me with the PL/SQL SP I'll need and the ADO call code? Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch