使用dbWriteTable/dbAppendTable向DB2表追加数据时日期列报错

问题重现 #

尝试用DBI和odbc的dbWriteTable/dbAppendTable追加data.table数据到DB2表时失败，但直接用dbExecute写SQL能正常执行，错误始终指向最后一列。执行代码和错误信息如下：

执行代码

library(data.table)
library(odbc)
library(DBI)
con3 <- dbConnect(odbc::odbc(), "DATABASENAME", uid = "UID", pwd = "PASSWORD", 
                  CCSID = 1208)


# 初始化数据表

dt.1 <- data.table(Id_n = as.integer(),
                   Lob = as.character(),
                   Val = as.numeric(),
                   Date_var = as.POSIXct(x = integer(0), 
                                   origin = "1970-01-01")) 
dt.2 <- copy(dt.1)

for (i in 1:1000) {
  
  dt.tmp <- data.table(Id_n = i,
                     Lob = "Text1",
                     Val = 100.1+i,
                     Date_var = as.POSIXct('2024-12-31'))
  
  dt.1 <- rbind(dt.1, dt.tmp)
  
}

for (i in 1:1000) {
  
  dt.tmp <- data.table(Id_n = i,
                       Lob = "Text2",
                       Val = 100.1+i,
                       Date_var = as.POSIXct('2024-12-31'))
  
  dt.2 <- rbind(dt.2, dt.tmp)
  
}

dt <- rbind(dt.1, dt.2)
dt <- unique(dt[, .(Date_var)])

dbWriteTable(conn =con3, 
             name = Id(Schema = "TEST_SCHEMA",
                       table = "TEST3"), 
             value = dt, 
             row.names = NULL,
             append = TRUE)

错误信息

Error in `dbWriteTable()`:
! ODBC failed with error 42S22 from [IBM][System i Access ODBC-drivrutin][DB2 for i5/OS].
✖ SQL0205 - Column "Date_var" not in table TEST3 in TESTSCHEMA.
• <SQL> 'INSERT INTO "TESTSCHEMA"."TEST3" ("Date_var")
• VALUES (?)'

问题分析 #

从错误信息看，自动生成的SQL用双引号包裹了标识符（Schema、表名、列名），但DB2的默认行为是：

• 创建对象时如果没加双引号，会自动将标识符转为大写存储
• 用双引号包裹的标识符会严格区分大小写，导致找不到对应的列或Schema

另外，代码中指定的Schema是TEST_SCHEMA，但错误提示里是TESTSCHEMA，说明下划线可能被ODBC驱动自动处理掉了，或者实际Schema名称是大写无下划线的TESTSCHEMA。

解决方案 #

1. 确认目标表的实际结构
   先执行以下代码查看表的真实列名和Schema信息：

   # 查看表的列名
   dbListFields(con3, Id(Schema = "TESTSCHEMA", table = "TEST3"))
   # 或者直接查询系统表
   dbGetQuery(con3, "SELECT COLUMN_NAME FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'TESTSCHEMA' AND TABLE_NAME = 'TEST3'")
   

   确认列名是否为大写（比如DATE_VAR），Schema名称是否正确。

2. 统一标识符大小写

   • 如果表中列名是大写，将R数据框的列名改为对应大写：

     setnames(dt, "Date_var", "DATE_VAR")
     

   • 同时修改dbWriteTable中的Schema参数为实际大写名称：

     dbWriteTable(conn = con3, 
                  name = Id(Schema = "TESTSCHEMA", table = "TEST3"), 
                  value = dt, 
                  row.names = NULL,
                  append = TRUE)
     

3. 关闭标识符引号
   设置全局选项让DBI生成的SQL不使用双引号包裹标识符，让DB2自动处理大小写转换：

   options(DBI_QUOTED_IDENTIFIERS = FALSE)
   

   之后再执行dbWriteTable即可。

4. 替代方案：用dbExecute批量插入
   既然直接写SQL能成功，可改用参数化查询批量插入，避免自动生成SQL的问题：

   # 替换为实际列名
   sql <- "INSERT INTO TESTSCHEMA.TEST3 (DATE_VAR) VALUES (?)"
   dbExecute(con3, sql, params = list(dt$DATE_VAR))
   

内容的提问来源于stack exchange，提问作者ErrantBard