dbWriteTable function returning TRUE status even if it fails to insert record as it violates the schema constraints

user_1994393 0
Viewed 420

I am using DBI, RMySql packages for interacting with MySql database.

Here are more details about configuration:

  • R version: 3.3.2
  • DBI version: 0.7
  • RMySql version: 0.10.13

Below is the schema for table Site:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| short_name | varchar(10) | NO   | UNI | NULL    |                |
| full_name  | varchar(50) | NO   | UNI | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

As you can see that the fields short_name & full_name are having UNIQUE & NOT NULL constraints.

On trying to insert a row with the duplicate short_name or full_name which already exist in the table, dbWriteTable does not let happen such row insertion but it returns the TRUE status even though it violates the UNIQUE constraint. Same thing happens for NOT NULL constraint as well.

Is this the expected behavior of DBI & dbWriteTable? Why it's not returning the FALSE status?

EDIT: I also observed that even dbSendStatement() and dbSendQuery() does not give any error in case of constraint violation. Is there any way to get to know this?

r-dbi rmysql shiny unique-constraint r
1 Answers

I feel like there has to be a better way to do this, but the solution I'm aware of is to use a tryCatch to capture the error messages. There are other options here than capturing the messages...you could for instance take an action instead.

The reason dbWriteTable doesn't return FALSE is explained in the documentation. It only returns FALSE if there was a problem with the DBI functions. Your problem is in the database. I know it is semantics, but it makes a difference here.

I made up an example because I didn't have one from the question.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbSendQuery(con,
            "CREATE TABLE mtcars
            (
              mpg REAL,
              cyl INTEGER,
              disp REAL,
              hp REAL, 
              drat REAL NOT NULL
            );")

dbWriteTable(con, "mtcars", mtcars[1:5,1:5], append = TRUE)

test_db <- dbGetQuery(con,"SELECT * FROM mtcars")
test_db
# mpg cyl disp  hp drat
# 1 21.0   6  160 110 3.90
# 2 21.0   6  160 110 3.90
# 3 22.8   4  108  93 3.85
# 4 21.4   6  258 110 3.08
# 5 18.7   8  360 175 3.15

# Can't capture output
result <- dbWriteTable(con, "mtcars", 
             data.frame(mpg = 1, cyl = 2, disp = 3, hp = 4, drat = NA),
             append = TRUE)
result
# Error: object 'result' not found

# Let's try checking exceptions
# This doesn't work because it is checking for exceptions in the DBI commands...not in the database.
result <- dbGetException(con)
result
# $errorNum
# [1] 0
# 
# $errorMsg
# [1] "OK"

# Because of the error there wasn't a change in the database.
test_db <- dbGetQuery(con,"SELECT * FROM mtcars")
test_db
# mpg cyl disp  hp drat
# 1 21.0   6  160 110 3.90
# 2 21.0   6  160 110 3.90
# 3 22.8   4  108  93 3.85
# 4 21.4   6  258 110 3.08
# 5 18.7   8  360 175 3.15

# We can use a tryCatch to capture error or warning messages
tryCatch({
  dbWriteTable(con, "mtcars", 
               data.frame(mpg = 1, cyl = 2, disp = 3, hp = 4, drat = NA),
               append = TRUE)
  result <- dbGetException(con)
},
  error = function(e) result <<- conditionMessage(e),
  warning = function(w) result <<- conditionMessage(w)
)
user_84852870
Related