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.

Tevis

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.

Baikal

Answer 2

The sqlUpdate worked for me. the only thing I had to change was the db.df - needed to double up the \ character so that it didn't try to escape the code with it. My test table looked like this:

CREATE TABLE mytable  (
    ID1 INT NOT NULL PRIMARY KEY,
    ID2 VARCHAR(10) NOT NULL,
    FilePath1 VARCHAR(50) NOT NULL,
    FilePath2 VARCHAR(50) NULL,
    Status VARCHAR(15) NOT NULL)

insert into mytable values(1,'Test1','MyFolder\R\Folder1',NULL,'Open')
insert into mytable values(2,'Test2','MyFolder\R\Folder2',NULL,'Open')
insert into mytable values(3,'Test3','MyFolder\R\Folder3',NULL,'Finished')
insert into mytable values(4,'Test4','MyFolder\R\Folder4',NULL,'Finished')

I was able to run the update without the ID1 or FilePath1 fields in the update. If you read the documentation (?sqlUpdate) it states: 'First it looks for a primary key for the table, then for the column(s) that the database regards as the optimal for defining a row uniquely' so you don't have to use the primary key, although if you didn't know ID2 was unique it would be best to use that primary key.

db.df <- data.frame(ID2='Test1', FilePath2='MyFolder\\R\\Folder5', Status='Finished',                    stringsAsFactors=FALSE)
sqlUpdate(myconn, db.df, tablename='mytable', index='ID2', verbose=TRUE)

Wellfixed

Answer 3

In some cases, especially if you are passing multiple columns you need to specify the column structure explicitly as column names.

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

Update: So it seems sometimes it still fails. The new work around I used is:

This allows you to pass a list of columns if needed. Don't know why it was being finicky the other way.

indexNames<-list("ID2")

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

Caudex