如何读取从 R 中的 SQL Server 存储过程返回的多个结果集 [英] How to read multiple result sets returned from a SQL Server stored procedure in R

问题描述

如何捕获从存储过程调用返回的多个表?

How do I capture multiple tables returned from a stored procedure call?

conn<- odbcDriverConnect('driver={SQL Server};server=server;database=db;trusted_connection=yes')
data <- sqlQuery(conn,paste("exec dbo.GetTest"),errors=FALSE)

它只捕获列表中返回的第一个表.

It only captures the first table returned in a list.

推荐答案

RODBC 可能没有从存储过程中检索多个记录集的功能.但是，Windows ADO 确实具有 NextRecordSet() 方法.考虑让 R 使用 RDCOMClient 库对 ADO 进行 COM 接口调用(当然假设您将 R 用于 Windows).在 ADO 的连接对象中，您传递与在 RODBC 中相同的连接字符串.

RODBC may not have a feature to retrieve multiple recordsets from a stored procedure. However, Windows ADO does have the NextRecordSet() method. Consider having R make a COM interface call to ADO using the RDCOMClient library (assuming of course you use R for Windows). In ADO's connection object, you pass the same connection string as you did in RODBC.

下面使用 ADO 的 GetRows() 返回一个在 R 中转换为嵌套列表的二维数组.

Below retrieves query results with ADO's GetRows() which returns a two-dimensional array translated as nested lists in R.

SQL Server (存储过程)

CREATE PROCEDURE MultipleResults 
AS    
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Table1;
    SELECT * FROM Table2;
END

R (ADO 调用)

library(RDCOMClient)

conn <- COMCreate("ADODB.Connection")
rst <- COMCreate("ADODB.Recordset")

conn$Open("driver={SQL Server};server=server;database=db;trusted_connection=yes;")

# FIRST QUERY RESULT
rst$Open("MultipleResults", conn)
dfList1 <- rst$GetRows()    
# RETRIEVE COLUMN NAMES
dfnames1 <- vapply(c(0:(rst[['Fields']]$Count()-1)), 
                   function(i) (rst$Fields(i)$Name()), character(1))

# SECOND QUERY RESULT
rst <- rst$NextRecordset()
dfList2 <- rst$GetRows()    
# RETRIEVE COLUMN NAMES
dfnames2 <- vapply(c(0:(rst[['Fields']]$Count()-1)), 
                   function(i) (rst$Fields(i)$Name()), character(1))   
# CLOSE OBJECTS
rst$Close(); conn$Close()

# FREE RESOURCES
rst <-  conn <- NULL
rm(rst, conn)
gc()

# CONVERT NESTED LISTS TO DATAFRAMES    
dfList1 <- lapply(dfList1, function(x) setNames(data.frame(x), dfnames1))    
df1 <- do.call(rbind, dfList1)

dfList2 <- lapply(dfList2, function(x) setNames(data.frame(x), dfnames2))      
df2 <- do.call(rbind, dfList2)

这篇关于如何读取从 R 中的 SQL Server 存储过程返回的多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！