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

Possible duplicate of [Exception handling in R](http://stackoverflow.com/questions/2622777/exception-handling-in-r) — Gábor Bakos, Oct 22 '16 at 17:14
Might worth checking: http://stackoverflow.com/documentation/r/4060/fault-tolerant-resilient-code/14150/using-trycatch#t=201610221715188281912 — Gábor Bakos, Oct 22 '16 at 17:15

score 1 · Accepted Answer · answered Oct 22 '16 at 16:17

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

answered Oct 22 '16 at 16:17

Mark Miller

3,011
1
14
34

1

good one, you can also do show tables like 'some_table' and will restrict the results to only tables called 'some_table', so if the query returns a row it means the table exists – Jayvee Oct 22 '16 at 16:28
1

yeah, that would be faster and more memory efficient, wouldn't it? you could do that by hard coding the specific table in the query, or you could make the table a parameter to the function, and then paste0() it into the second argument to dbSendQuery. – Mark Miller Oct 22 '16 at 16:41

score 1 · Answer 2 · answered Oct 22 '16 at 16:19

1

this will return null if table does not exist:

...
res <- dbSendQuery(db, "SELECT nullif(count(1),0) tableexists FROM information_schema.tables WHERE table_name='some_table'")
...

you might also want to include AND table_schema='your schema', if you are only interested if the table exists or not in one particular schema

answered Oct 22 '16 at 16:19

Jayvee

10,670
3
29
40

Best answer; short and sweet. – jogall Dec 06 '17 at 11:04

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

2 Answers2