dbWriteTable or dbAppendTable fail for date column when appending to DB2-table

Question

I'm trying to append a data.table/data.frame to a DB2 database table using the built in methods in DBI and odbc (starting with dbWriteTable an dbAppendTable). I can get it to work with dbExecute and writing the SQL-statment directly. So the problem seems to be with the underlying sql-statement that isn't generated in the right form. The error message is always returned for the last column. I've tried with several different combos of columns and data types.

Executed code:

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


# Fix data table

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

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 (?)'

Answer 1

Couldn't get dbWriteTable or dbAppendTable to work with our setup for DB2 but dbSendQuery and dbBind work nicely with inserting data.

Loop over a list and run dbBind. After the loop is finished, run dbFetch

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


# Fix data table

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)

nobsQ <- paste(rep("?", ncol(dt)), collapse =",")
l.param.u <- list()

for (i in seq(nrow(dt)) ) { 
  l.param.u[[i]]  <-  unname( unclass( dt[i,] )) 
}

k.list <- length(l.param.u)

k.Insert <- paste0("Insert into TESTSCHEMA.TEST_TBL VALUES(",
                   nobsQ,")")

res2 <- dbSendQuery(con3, k.Insert)
for(i in 1:k.list) {
  
  l.tmp <- l.param.u[[i]]

  dbBind(res2, l.tmp)
  
}


system.time(dbFetch(res2))
dbClearResult(res2)