Quantcast

RODBC results from stored procedure

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RODBC results from stored procedure

tradenet
Short of uploading a SQL server database, I don't think I can make this example reproducible, but I hope it's not so complicated as to require reproducibility....

I'm using RODBC to get data from Microsoft SQL Server.
I can call a parametrized stored procedure without a problem and the proc does indeed execute successfully.  However, even though the proc returns the results I found that I had to modify the proc so that, in addition to returning the results to the caller, it also saved the results to an actual SQL Server table.  Then I was able to make second call to sqlQuery with a simple select statement for the results table and retrieve the results back into R.  My question is:  can I get stored proc results directly back to R without having to populate and query a results table?


>dbdata<-sqlQuery(conn,"sp_GetReturns,'2009-07-10','2009-07-14'")
>head(dbdata)

character(0)  [....no data here....]

>dbdata<-sqlQuery(conn,"select * from returns order by Date asc")  [...success...]
>head(dbdata)

  Date          Asset1   Asset2....
2009-07-10  0.01        0.02
2009-07-13  0.007      -0.003
...

I'd appreciate any suggestions,

Andrew
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RODBC results from stored procedure

Dieter Menne

tradenet wrote
Short of uploading a SQL server database, I don't think I can make this example reproducible, but I hope it's not so complicated as to require reproducibility....

I can call a parametrized stored procedure without a problem and the proc does indeed execute successfully.
This works for me with the popular Northwind database

channel = odbcConnect("northwind") # Assume this is configured correctly
sqlQuery(channel,"EXEC CustOrderHist @CustomerID=ALFKI")

Try with a non-date query first, the switch to the tricky date format in the parameter.

Dieter

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RODBC results from stored procedure

tradenet
Thanks Dieter.

The date argument isn't a problem.  When I invoke the stored proc execution with the date arguments the stored proc runs fine, but RODBC doesn't wait for the stored proc to finish and return results.

Regards,

Andrew

Dieter Menne wrote
tradenet wrote
Short of uploading a SQL server database, I don't think I can make this example reproducible, but I hope it's not so complicated as to require reproducibility....

I can call a parametrized stored procedure without a problem and the proc does indeed execute successfully.
This works for me with the popular Northwind database

channel = odbcConnect("northwind") # Assume this is configured correctly
sqlQuery(channel,"EXEC CustOrderHist @CustomerID=ALFKI")

Try with a non-date query first, the switch to the tricky date format in the parameter.

Dieter
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RODBC results from stored procedure

Dieter Menne

tradenet wrote
Thanks Dieter.

The date argument isn't a problem.  When I invoke the stored proc execution with the date arguments the stored proc runs fine, but RODBC doesn't wait for the stored proc to finish and return results.
Don't understand that one. How do you know that is did not wait to finish?

Did the call I showed you work with the Northwind database?

Dieter
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RODBC results from stored procedure

tradenet
Thanks Dieter.

The stored proc drops and recreates a result table that contains a copy of the same result set that is returned by the query.

If I pause after line 1, line 2 returns a valid result set and line 4 displays a table of data.
If I do not pause the script after line 1 or 2, line 3 fails, saying datatable does not exist (line 1 query has not completed the recreation of datatable) and line 4 displays no data.

Also, the query in line one returns a single result set, a select statement from datatable, but R doesn't see this returned result set after invocation of only line 1.


1.) dbdata<-sqlQuery(conn,"sp_GetData)  #returns a result set in query analyzer, not in R
2.) head(dbdata) #it's empty
3.) dbdata<-sqlQuery(conn,"select * from datatable order by Date asc")
4.) head(dbdata) #the data is in dbdata


My workaround is to run line 1's query manually before running any R scripts.  Not ideal, but I'm at a loss for what else to try for this prickly case.

Warm regards,

Andrew



Dieter Menne wrote
tradenet wrote
Thanks Dieter.

The date argument isn't a problem.  When I invoke the stored proc execution with the date arguments the stored proc runs fine, but RODBC doesn't wait for the stored proc to finish and return results.
Don't understand that one. How do you know that is did not wait to finish?

Did the call I showed you work with the Northwind database?

Dieter
ang
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RODBC results from stored procedure

ang
I know this thread is from a while back, but hopefully I can still get some help on this.

I also used RODBC to connect to a SQL Server, and my stored procedure returns a results set (that is not stored as a temp or permanent table).

So I was wondering if there was a way to access this results set, or am I forced to store the results into a table before I can access it?

Some options I have considered:
a)modifying the stored procedures to insert the results into tables
b)creating temp tables (trying to stay away from this as it is not as dynamic and would require defining of many tables if I wanted to run some ad hoc analysis)

The reason I do not want to modify the queries/create temp or perm tables is because the SQL end is maintained by a separate team, and I am simply using R to connect/run analyses off the data.  I am trying to keep my side as self contained and independent as possible.  Any suggestions/advice would be greatly appreciated.

Thanks a lot,
Adrian
Loading...