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

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

• r
• odbc
• r-dbi

• 2 I use DBI and odbc for SQL server but I don't use dbAppendTable() because I have to many issues. Therefore I use a personnal insert function. But even in this case I have to convert POSIXct dates with base::format(myPosixCtDate, format = "%Y-%m-%d %H:%M:%S"). – Phil Commented Feb 10 at 14:10
• datas <- ( datas %>% dplyr::mutate( dplyr::across(tidyselect::where(lubridate::is.POSIXt), function (x) { base::format(x, format = "%Y-%m-%d %H:%M:%S")} ) ) ) – Phil Commented Feb 10 at 14:12
• @Phil: would you mind sharing the issues you're seeing with dbAppendTable() ? – krlmlr Commented Feb 11 at 6:10
• 1 @krlmlr do you see any reason why dbSendQuery & dbBind works while dbAppendTable doesn't? difference is supply a data.frame/data.table in one case and a list in the other – ErrantBard Commented Feb 12 at 13:17
• 1 No, both should work, but I also remember inconsistencies in odbc::dbAppendTable() . A reprex would help. – krlmlr Commented Feb 13 at 19:37

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