How to pass data.frame for UPDATE with R DBI

Question

With RODBC, there were features such as lettingsqlUpdate(channel, dat, ...) you go through dat = data.frame(...)instead of creating your own SQL string.

However, with R DBI , all I see are functions of the type dbSendQuery(conn, statement, ...)that take only a string statementand do not allow you to directly specify data.frame.

So how to UPDATEuse data.framewith DBI?

Answer 1

Really late, my answer, but perhaps still useful ...

DBI/odbc ( ), ( , RODBC sqlUpdate), SQL-:

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), driver="{SQL Server Native Client 11.0}", server="dbserver.domain.com\\default,1234", Trusted_Connection = "yes", database = "test")  # assumes Microsoft SQL Server

dbWriteTable(con, "iris", iris, row.names = TRUE)      # create and populate a table (adding the row names as a separate columns used as row ID)

update <- dbSendQuery(con, 'update iris set "Sepal.Length"=?, "Sepal.Width"=?, "Petal.Length"=?, "Petal.Width"=?, "Species"=? WHERE row_names=?')

# create a modified version of `iris`
iris2 <- iris
iris2$Sepal.Length <- 5
iris2$Petal.Width[2] <- 1
iris2$row_names <- rownames(iris)  # use the row names as unique row ID

dbBind(update, iris2)  # send the updated data

dbClearResult(update)  # release the prepared statement

# now read the modified data - you will see the updates did work
data1 <- dbReadTable(con, "iris")

dbDisconnect(con)

, , , , , ...

odbc, DBI dbConnect, . : https://github.com/rstats-db/odbc