How to check a condition whether table exist or not in RMysql

Question

I want to check a condition in Shiny app whether mysql table exist or not in specific databse. If table does not exist it should return a null value. Here is my code looking like.

loadData <- function(){

db <- dbConnect(MySQL(), dbname = databaseName, host = host, 
              port = port, user = user, password = password)

res <- dbSendQuery(db, "SELECT * FROM some_table")
final_data <- dbFetch(res)
dbDisconnect(db)
return(final_data)

}

I want to handle exception thrown by dbSendQuery(db, "SELECT * FROM some_table") if some_table does not exist in databse. Please help..

Answer 1

Here's what I do. Maybe there's something else that's more robust or generalizable?

Just "show tables" as a query and check for the presence of your table name in the result.

loadData <- function() {
  db <- dbConnect(
    MySQL(),
    dbname = databaseName,
    host = host,
    port = port,
    user = user,
    password = password
  )

  rs <- dbSendQuery(con, "show tables")
  table.frame <- fetch(rs, n = -1)
  if ("some_table" %in% table.frame[, 1]) {

    res <- dbSendQuery(db, "SELECT * FROM some_table")
    final_data <- dbFetch(res)
    dbDisconnect(db)
    return(final_data)

  } else {
    return(NULL)
  }
}