[R-sig-DB] RODBC: how to view multiple objects returned by a stored procedure?
Maciej Ombach
momb@ch @end|ng |rom hotm@||@com
Mon Oct 18 03:35:27 CEST 2010
I am trying to use a stored procedure (MS SQL Server 2005) that requires an xml input (R 2.11.0, RODBC 1.3-2). This stored procedure returns a header/metadata and a data table (which I verified in DBArtisan).
My R code looks like this:
library(RODBC)
query <- "
exec sp_GetIndexData @ApplicationName='XXXXX', @UserName='XXXXX',
@XMLInput=N'<getdata>
<header fromdate=\"12/31/2009\" todate=\"8/23/2010\" frequency=\"1\" download=\"0\" viewcurveorspread=\"1\"/>
<serieslist>
<series cusip=\"H0A0\" item=\"62\"/>
</serieslist>
</getdata>'
"
query1 <- gsub("\n", "", query)
dbSource <- odbcConnect("XXX", uid = "xxx", pwd = "xxx")
out <- sqlQuery(dbSource, query1)
odbcClose(dbSource)
This and other slightly modified calls return an empty character string (out). I am able to run simple select queries against this database, so the connection is working. The stored proc also works in Excel VBA, where it returns two recordsets: the first one is empty (header?), the second one has the data I need (in VBA, rs.NextRecordset allows to loop through recordsets).
At this point, my best guess is that something similar is happening in R. I am getting back an empty header and need to figure out how to view the actual data table. Any suggestions would be much appreciated.
Maciek
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list