[R-sig-DB] RODBC: how to view multiple objects returned by a stored procedure?

Prof Brian Ripley r|p|ey @end|ng |rom @t@t@@ox@@c@uk
Mon Oct 18 08:20:30 CEST 2010


Sorry, this is a question about ODBC, not R.


On Sun, 17 Oct 2010, Maciej Ombach wrote:

>
>
> 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]]
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595




More information about the R-sig-DB mailing list