RODBC：sqlUpdate（）无法识别索引列

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.

第一列（ID1）定义为PK。但是，ID2也是独一无二的。

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

现在，我希望能够使用RODBC包中的sqlUpdate（）更改FilePath2和Status with R.所以我尝试以下方法：

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:

其中db.df是一个数据框，其中一个行名和列名对应于数据库表中的那些（但是，我省略了一些列，在本例中为FilePath1，我也希望省略ID1，如果可能）。我的目标是获得以下内容：

+-----+-------+--------------------+--------------------+----------+
| 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:

编辑：我通过发送直接SQL查询绕过了问题：

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.

虽然这可能不是一种简洁的方式，但它可以做它应该做的事情。但是，我仍然不明白sqlUpdate有什么问题，所以我希望有人可以了解它。

3 个解决方案

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

在使用sqlUpdate更新MySQL中的表时，我遇到了类似的问题。我通过在R-MySQL连接中设置case属性来修复它。

Here is the detail:

这是详细信息：

In MySQL:

在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:

在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:

原因是RMySQL连接中的（默认？）属性具有：

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

因此，myDF中的colname myName1在sqlUpdate中更改为myname1，因此它与myName1给定的索引不匹配。

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.

请注意，如果使用index ='myname1'更改调用，它将无法工作。将报告索引列myName1不在数据库表中的错误。因为在MySQL表中，colname是myName。

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

解决方案是在连接时或连接后将case属性设置为'nochange'：

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

Here are more details:

这里有更多细节：

debugonce(sqlUpdate) gives:

debugonce（sqlUpdate）给出：

   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.

注意对cname和coldata的intable调用。

#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:

sqlUpdate为我工作。我唯一需要更改的是db.df - 需要加倍\字符，以便它不会尝试使用它来转义代码。我的测试表看起来像这样：

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.

我能够在更新中运行没有ID1或FilePath1字段的更新。如果您阅读文档（？sqlUpdate），它会声明：'首先它查找表的主键，然后查找数据库视为唯一定义行的最佳列的列，因此您没有使用主键，但如果您不知道ID2是唯一的，那么最好使用该主键。

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

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

示例：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")

indexNames <-list（ “ID2”）

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

sqlUpdate（myconn，db.df，tablename ='mytable'，index = as.character（“ID2”），verbose = TRUE）

#1