[R-sig-DB] MySQL stored procedure fails when called from R
Jennifer Welsh
jenwe|@h @end|ng |rom y@hoo@com
Tue Aug 31 00:52:24 CEST 2010
Hi,
I posted this question at Stack Overflow (
http://stackoverflow.com/questions/3603865/mysql-stored-procedure-fails-when-called-from-r/3604472#3604472
) and was directed to this mailing list. I hope you can help.
Thanks,
Jen
This procedure works from the MySQL commandline both remotely and on localhost
and it works when called from PHP. In all cases the grants are adequate:
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
I'm trying to call it from 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)
The bare query runs fine. The procedure call fails with
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)
The MySQL docs say
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.
One would think that if a procedure were going to throw that error, it would be
thrown under all circumstances instead of just from R.
Any thoughts on how to fix this?
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list