MySQL stored procedure fails when called from R

Question

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?

Answer 1

As far as I know, calling SQL procedures from R (dbCallProc) is not yet formally implemented (see reference manual of 24 july 2010 : http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf)

RMySQL is transferred from S3 to S4 programming style, and is currently still under development (version 0.7 being the current one). I suggest you ask the same question on the database mailing list for R :

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

If it is possible, they'll show you how. If it isn't, they'll tell you why.

Answer 2

Try adding:

client.flag=CLIENT_MULTI_STATEMENTS

to your connection parameters. It may help.

There are some details about this in the RMySQL PDF.

Answer 3

Don't now about R, but this

p <- paste('CALL lee_expout(', exp_id, ',', group_id,')', sep= ' ') 

does look a bit ugly, ie like string concatenation. Maybe R's database driver takes that badly. In general, you can use placeholders for variables and pass the values on as separate arguments. Besides various security arguments, this also takes care of any type/apostrophe/whatever issues - maybe here, too?