RODBC: sqlUpdate() doesn't recognise index column

Question

My database table looks approximately like this:

+-----+-------+--------------------+-----------+----------+
| ID1 | ID2   | FilePath1          | FilePath2 | Status   |
+-----+-------+--------------------+-----------+----------+
| 1   | Test1 | MyFolder\R\Folder1 | NULL      | Open     |
| 2   | Test2 | MyFolder\R\Folder2 | NULL      | Open     |
| 3   | Test3 | MyFolder\R\Folder3 | NULL      | Finished |
| 4   | Test4 | MyFolder\R\Folder4 | NULL      | Finished |
+-----+-------+--------------------+-----------+----------+

The first column (ID1) is defined as PK. However, ID2 is also unique.

Now I'd like to be able to change FilePath2 and Status with R using sqlUpdate() from RODBC package. So I try the following:

db.df <- data.frame(ID1=1, ID2='Test1',
                    FilePath2='MyFolder\R\Folder5', Status='Finished',
                    stringsAsFactors=FALSE)

sqlUpdate(myconn, db.df, tablename='mytable', index='ID2', verbose=TRUE)

wherein db.df is a data frame with one row and column names corresponding to those within the database table (however, I leave out some of the columns, in this case FilePath1, and I'd prefer to leave out ID1, too, if possible). My aim is to get the following:

+-----+-------+--------------------+--------------------+----------+
| ID1 | ID2   | FilePath1          | FilePath2          | Status   |
+-----+-------+--------------------+--------------------+----------+
| 1   | Test1 | MyFolder\R\Folder1 | MyFolder\R\Folder5 | Finished |
| 2   | Test2 | MyFolder\R\Folder2 | NULL               | Open     |
| 3   | Test3 | MyFolder\R\Folder3 | NULL               | Finished |
| 4   | Test4 | MyFolder\R\Folder4 | NULL               | Finished |
+-----+-------+--------------------+--------------------+----------+

I get the folllowing error:

Error in sqlUpdate(myconn, db.df, tablename = 'mytable', index = 'ID2',  : 
index column(s) ID2 not in database table

What might be the reason for this problem?

---

EDIT: I've bypassed the problem by sending a direct SQL Query:

out.path <- 'MyFolder\\\\R\\\\Folder5'
update.query <- paste("UPDATE mytable ", 
                  "SET FilePath2='", out.path, "', Status='Finished' ",
                  "WHERE ID2='Test1'", sep="")
dummy <- sqlQuery(myconn, update.query)

Although this might not be a neat way, it does what it should do. However, I still don't understand what's the matter with sqlUpdate, so I hope someone can shed light on it.

Answer 1

I experienced a similar issue when using sqlUpdate to update a table in MySQL. I fixed it by setting the case attributes in R-MySQL connection.

Here is the detail:

In MySQL:

create table myTable (
myName1 INT NOT NULL PRIMARY KEY,
myName2 VARCHAR(10) NOT NULL,
);

insert into myTable values(111, 'Test1')
insert into myTable values(222, 'Test2')

In R:

myDF <- data.frame(myName1 = 111, myName2 = 'Test3')
sqlUpdate(myConn, myDF, tablename='myTable', index = 'myName1', verbose=TRUE)

#> Error in sqlUpdate(myConn, myDF, tablename='myTable', index = 'myName1', verbose=TRUE) : 
  index column(s) myName1 not in data frame

The reason is that the (default?) attributes in RMySQL connection has:

> attr(myConn, "case")
[1] "tolower"

So, colname myName1 in myDF is changed case to myname1 inside sqlUpdate, so it doesn't match to myName1 given index.

Note that it will not work if one change the call with index = 'myname1'. An error of index column(s) myName1 not in database table will be reported. Because in MySQL table, the colname is myName.

The solution is to set the case attributes to 'nochange' when or after connection:

attr(myConn, "case") <- 'nochange'

Here are more details:

debugonce(sqlUpdate) gives:

   cnames <- colnames(dat)
    cnames <- mangleColNames(cnames)
    cnames <- switch(attr(channel, "case"), nochange = cnames, 
        toupper = toupper(cnames), tolower = tolower(cnames))
    cdata <- sqlColumns(channel, tablename)
    coldata <- cdata[c(4L, 5L, 7L, 9L)]
    if (is.character(index)) {
        intable <- index %in% coldata[, 1L]
        if (any(!intable)) 
            stop("index column(s) ", paste(index[!intable], collapse = " "), 
                " not in database table")
        intable <- index %in% cnames
        if (any(!intable)) 
            stop("index column(s) ", paste(index[!intable], collapse = " "), 
                " not in data frame")
        indexcols <- index
    }

Note the intable calls to cname and coldata.