Skip to content

RMySQL and Stored procedures

2 messages · Orvalho Augusto, Dieter Menne

Orvalho Augusto
# 
Can someone provide me a good way to circumvent the lack of calling
Stored Procedures from RMySQL?

I can not rewrite those stored procedures on R because there a lot
more folks here that only understands SQL.

The stored procedure returns a resultset.

Thanks
Caveman
1 day later
Dieter Menne
#
Orvalho Augusto wrote:
Use RODBC instead.

Assuming two stored procedures : 

CREATE PROCEDURE getusers (in userid INT)
BEGIN
  SELECT name from jos_users where id > userid;
END;

CREATE PROCEDURE getusers0() 
BEGIN
  SELECT name from jos_users;
END;

The following works
#####
library(RODBC)
con = odbcConnect("mysql",uid="xxx",pwd="yyy")
users0 = sqlQuery(con,"CALL getusers0")
users0
users = sqlQuery(con,"CALL getusers(20)")
users
close(con)
#####
Dieter