从 R 调用时 MySQL 存储过程失败

Question

此过程在远程和本地主机上都可以从 MySQL 命令行运行，并且在从 PHP 调用时也可以运行。在所有情况下，赠款都是足够的:

CREATE PROCEDURE `myDB`.`lee_expout` (IN e int, IN g int)
BEGIN

select lm.groupname, lee.location, starttime, dark,
  inadist,smldist,lardist,emptydur,inadur,smldur,lardur,emptyct,entct,inact,smlct,larct
from lee join leegroup_map lm using (location)
where exp_id= e and std_interval!=0 and groupset_id= g
order by starttime,groupname,location;

END

我试图从 R 中调用它:

library(DBI)
library(RMySQL)

db <- dbConnect(MySQL(), user="user", password="pswd",
        dbname="myDB", host="the.host.com")

#args to pass to the procedure
exp_id<-16
group_id<-2

#the procedure call
p <- paste('CALL lee_expout(', exp_id, ',', group_id,')', sep= ' ') 

#the bare query
q <- paste('select lm.groupname, lee.location, starttime, dark,
inadist,smldist,lardist,emptydur,inadur,smldur,lardur,emptyct,entct,inact,smlct,larct
from lee join leegroup_map lm using (location)
where exp_id=', 
exp_id, 
' and std_interval!=0 and groupset_id=', 
group_id, 
'order by starttime,groupname,location', sep=' ') 

rs_p <- dbSendQuery(db, statement=p) #run procedure and fail
p_data<-fetch(rs_p,n=30)

rs_q <- dbSendQuery(db, statement=q) #or comment out p, run query and succeed
q_data<-fetch(rs_q,n=30)

裸查询运行良好。过程调用失败并显示

RApache Warning/Error!!!Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: PROCEDURE myDB.lee_expout can't return a result set in the given context)

MySQL docs说

For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs.

人们会认为，如果一个过程要抛出该错误，那么它将在所有情况下抛出，而不仅仅是从 R 中抛出。

关于如何解决这个问题有什么想法吗？

Answer 1

据我所知，从 R (dbCallProc) 调用 SQL 过程尚未正式实现(参见 2010 年 7 月 24 日的引用手册:http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf)

RMySQL 已从 S3 转移到 S4 编程风格，目前仍在开发中(当前版本为 0.7)。我建议你在 R 的数据库邮件列表上问同样的问题:

https://stat.ethz.ch/mailman/listinfo/r-sig-db

如果可能，他们会告诉你怎么做。如果不是，他们会告诉您原因。