[R-sig-DB] MySQL stored procedure fails when called from R

[Jennifer Welsh]

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]]