I have been trying to find the reason of the error in this code, but I have not been successful. I would like to pass a variable to SQL, but it keeps on giving the same error.
A MWE is the following (just change user
and password
for the connection).
library(DBI)
library(RMySQL)
library(tidyverse)
data("mtcars")
head(mtcars)
mtcars <- mtcars |>
rownames_to_column("type") |>
as_tibble()
# Open a connection to MySQL
conn <- dbConnect(MySQL(), user = "myuser", password = "mypassword")
# Create the appropriate database
dbGetQuery(conn, "CREATE DATABASE mtcars;")
dbGetQuery(conn, "USE mtcars")
# Query that doesn't work:
dbGetQuery(conn,
"SELECT COUNT(*) FROM mtcars WHERE cyl = ? ",
params = list(8))
This is the error that I keep on getting.
Error in .local(conn, statement, ...) :
unused argument (params = list(8))
I have seen in a post ideas like using \
or \\
to escape ?
but it didn't work. I have tried to search for similar errors, but I haven't find anything remotely similar.
Thank you for any advice you can give me.
RMySQL
does not support params=
, and the author (on its github repo) says:
NOTE: this package is being phased out in favor of the new
RMariaDB
package.
Up front, your query should work just fine using the new package. However, I found that loading both RMySQL
and RMariaDB
breaks things.
Docker image setup (since I don't have mysql running anywhere):
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8
In R:
mysql <- DBI::dbConnect(RMySQL::MySQL(), user="root", password="my-secret-pw", host="127.0.0.1")
DBI::dbExecute(mysql, "create database quuxdb")
# [1] 1
DBI::dbExecute(mysql, "use quuxdb")
# [1] 0
DBI::dbExecute(mysql, "create table quux (AA int, BB int)")
# [1] 0
DBI::dbExecute(mysql, "insert into quux (AA,BB) values (1,11), (2,22), (3,33)")
# [1] 3
DBI::dbGetQuery(mysql, "select * from quux where AA > 1")
# AA BB
# 1 2 22
# 2 3 33
DBI::dbGetQuery(mysql, "select * from quux where AA > ?", params=list(1))
# Error in .local(conn, statement, ...) :
# unused argument (params = list(1))
After I install RMariaDB
in the same running R session, I tried to connect but get
maria <- DBI::dbConnect(RMariaDB::MariaDB(), host="127.0.0.1", password="my-secret-pw", user="root", database="quuxdb")
# Error in h(simpleError(msg, call)) :
# error in evaluating the argument 'statement' in selecting a method for function 'dbExecute': no slot of name "Id" for this object of class "MySQLConnection"
# Error in .local(dbObj, ...) :
# no slot of name "Id" for this object of class "MySQLConnection"
On a whim, I started a new R session and ran that again (no error this time) and then continued:
maria <- DBI::dbConnect(RMariaDB::MariaDB(), host="127.0.0.1", password="my-secret-pw", user="root", dbname="quuxdb")
DBI::dbGetQuery(maria, "select * from quux where AA > 1")
# AA BB
# 1 2 22
# 2 3 33
DBI::dbGetQuery(maria, "select * from quux where AA > ?", params = list(1))
# AA BB
# 1 2 22
# 2 3 33
I'm not certain if the Id
-error is a bug or just a nuance of having two packages that acknowledge connections to the same type of database. Either way, in a fresh R instance, RMariaDB::MariaDB()
works with params=
(to the same MySQL database).