Extract Data from a MySQL Database View

Question

I am trying to access data from a View in a MySQL database using RStudio. When I connect to the database, it shows the names of all Views in the Connections window. I can even return the names of every View using the dbListTables() function.

But, when I try to run the tbl() function, I get the following error:

Error: nanodbc/nanodbc.cpp:1655: HY000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.3.34- 
MariaDB-log]Prepared statement needs to be re-prepared 
<SQL> 'SELECT *
FROM `database_view` AS `q01`
WHERE (0 = 1)'

Here are the packages I have loaded:

library(tidyverse)
library(dbplyr)
library(DBI)
library(odbc)

Here is my tbl() code:

tbl(con, "database_view")

Here is my connection code (I replaced the actual values with placeholders in brackets):

con <- DBI::dbConnect(odbc::odbc(),
                  Driver = "MySQL ODBC 8.0 ANSI Driver",
                  Server = "[Server]",
                  UID = "[UID]",
                  PWD = "[PWD]",
                  Port = 3306,
                  Database = "[Database]")

Any help would be much appreciated!

Answer 1

It looks like this is related to the backend you're using for the DBI package. The RMariaDB::MariaDB() backend seems not to work with views. Luckily, RMySQL::MySQL() does work, so you should be able to use that instead.

Since you're set up with ODBC, I'm guessing you're using the Maria DB ODBC driver. If you reconfigure your ODBC connection to use a MySQL driver, it should fix the issue.

Alternatively, it's a little bit less work to just connect to the database directly. You can just install the RMySQL package and use the code below.

# load necessary libraries
library(DBI)

# unable to fetch data from views using the RMariaDB package
mysql <- 
  DBI::dbConnect(
    RMariaDB::MariaDB(),
    host = Sys.getenv('host'), 
    user = Sys.getenv('uid'), 
    password = Sys.getenv('pwd')
  ) 

data <- dbGetQuery(mysql, 'select * from view')
#> Error: Prepared statement needs to be re-prepared [1615]


# but it works with the RMySQL package
mysql <- 
  DBI::dbConnect(
    RMySQL::MySQL(), 
    host = Sys.getenv('host'), 
    user = Sys.getenv('uid'), 
    password = Sys.getenv('pwd')
  )  

data <- dbGetQuery(mysql, 'select * from view')