Error passing a parameter to SQL with RMySQL

Question

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.

Answer 1

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).