sql - RODBC:sqlUpdate()无法识别索引列

Question

我的数据库表大致如下所示:

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

第一列(ID1)定义为PK。但是，ID2也是唯一的。

现在，我希望能够使用 FilePath2包中的 Status使用R更改 sqlUpdate()和 RODBC。因此，我尝试以下操作:

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)

其中 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 |
+-----+-------+--------------------+--------------------+----------+

我收到以下错误:

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

这个问题可能是什么原因？

编辑:我已经通过发送直接的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)

尽管这可能不是一种整洁的方法，但它可以做应做的事情。但是，我仍然不了解 sqlUpdate有什么问题，所以我希望有人能对此有所了解。

Answer 1

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

这是详细信息:

在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')

在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

原因是RMySQL连接中的(default？)属性具有:

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

因此，将myDF中的名字 myName1更改为 myname1内的 sqlUpdate，以使其与给定索引的 myName1不匹配。

请注意，如果使用 index = 'myname1'更改了调用，它将无法正常工作。将报告 index column(s) myName1 not in database table错误。因为在MySQL表中，列名是myName。

解决方案是在连接时或连接后将案例属性设置为“nochange”:

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

以下是更多详细信息:
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
    }

注意对 intable和 cname的 coldata调用。