Writing and Updating DB2 tables with r

Question

I can't figure out how to update an existing DB2 database in R or update a single value in it.

I can't find much information on this topic online other than very general information, but no specific examples.

        library(RJDBC)

        teachersalaries=data.frame(name=c("bob"), earnings=c(100))




        dbSendUpdate(conn, "UPDATE test1 salary",teachersalaries[1,2])

AND

       teachersalaries=data.frame(name=c("bob",'sally'), earnings=c(100,200))




        dbSendUpdate(conn, "INSERT INTO test1 salary", teachersalaries[which(teachersalaries$earnings>200,] )

Did you set up the connection smoothly? Is conn object valid? — submartingale, Feb 10 '16 at 19:28
Have you seen http://stackoverflow.com/questions/11743265/insert-multiple-rows-into-db2-database? — submartingale, Feb 10 '16 at 19:43
Yes, I have setup the connection. I can create a brand new table, but I don't know how to write additional values or update fields. I am having trouble and having trouble understanding the documentation out there. All I need is an example of how this might be done! I want to be able to add rows from an existing dataframe I have, not type in the values line by line. — runningbirds, Feb 10 '16 at 21:18
I moved away from RJDBC as much better functionality was provided by the IBM ODBC drivers which are now available in 32 and 64 bit. Once teh date source is setup on your machine the RODBC package gives you better control through functions such as RODBC::sqlupdate and RODBC::sqlsave — Feargal Ryan, Feb 16 '16 at 16:05

score 4 · Accepted Answer · answered Feb 15 '16 at 10:58

Have you tried passing a regular SQL statement like you would in other languages?

dbSendUpdate(conn, "UPDATE test1 set salary=? where id=?", teachersalary, teacherid)

or

dbSendUpdate(conn,"INSERT INTO test1 VALUES (?,?)",teacherid,teachersalary)

Basically you specify the regular SQL DML statement using parameter markers (those question marks) and provide a list of values as comma-separated parameters.

score 1 · Answer 2 · answered Feb 22 '16 at 02:52

1

Try this, it worked for me well.

dbSendUpdate(conn,"INSERT INTO test1 VALUES (?,?)",teacherid,teachersalary)

You just need to pass a regular SQL piece in the same way you do in any programing langs. Try it out.

answered Feb 22 '16 at 02:52

Iresha Rubasinghe

913
1
10
27

score 1 · Answer 3 · edited May 22 '17 at 03:20

To update multiple rows at the same time, I have built the following function.

I have tested it with batches of up to 10,000 rows and it works perfectly.

# Libraries
library(RJDBC)
library(dplyr)    

# Function upload data into database
db_write_table <- function(conn,table,df){
  # Format data to write
  batch <- apply(df,1,FUN = function(x) paste0("'",trimws(x),"'", collapse = ",")) %>%
  paste0("(",.,")",collapse = ",\n")

  #Build query
  query <- paste("INSERT INTO", table ,"VALUES", batch)

  # Send update
  dbSendUpdate(conn, query)
}

# Push data
db_write_table(conn,"schema.mytable",mydataframe)

Thanks to the other authors.

Writing and Updating DB2 tables with r

3 Answers3

Linked