Returning rowset from sproc via VFP OLEDB driver

[Spice]

The documentation for the new VFP OLEDB driver says the following:

The stored procedures return results in rowsets, and are able to accept parameters.

I take this to mean that if I have a stored procedure called MySproc that runs a simple SELECT query, I can use ADO code like this:

Set oRecordset = oConnection.Execute("MySproc()&quot

and return the results of the query to an ADO Recordset via the VFP OLEDB driver. SQL Server sprocs work this way. In my testing however, the recordset returned is not the results of the query. Instead, it's a one record, one field recordset containing the return value of the sproc.

Does anyone know if what I want to do is possible? I haven't found much documentation for how to utilize the driver via ADO. Any help is appreciated.

 

[TomasDill]

What is 'sproc'?
Did you tried 'SET NOCOUNT ON' option of SQL Server?
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[Spice]

SPROC is a Stored PROCedure.

I'm not having problems returning a recordset from SQL Server. I'm having problems using the new VFP ADODB driver to return an ADO recordset from a VFP stored procedure.

 

[TomasDill]

Hi!

Take a look to the samples related to returning multiple recordsets using ADO. Probably driver return result from both sproc (called from MySproc) and MySproc, but you just did not get to the second result.

BTW, if you return a single value from the stored procedure, better use an Output parameter for it or RETURN (value) inside of sproc instead of returning the result of SP as a result set. When you use the result set approach, this result set is also returned through OLE DB and ODBC no matter how deep SP calls are nested.

Hope this helps.


Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[TomasDill]

Oops, sorry, there are no output parameters for SP in VFP. So use Return (Value) and call Sproc as a function.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs