Write data into oracle database in r using loop

Question

I was able to make a market basket analysis in r on the database. I've finished my analysis and i want to write results into oracle database row by row. I've tried this

sonuclar<-inspect(basket_rules[1:5])
mode(sonuclar)
[1] "list"
class(sonuclar)
[1] "data.frame"
for(row in 1:nrow(sonuclar)) {`dbGetQuery(jdbcConnection,paste0("insert into market_basket_analysis (lhs,rhs,support,confidence,lift)
values ('",sonuclar$lhs[row],"','",sonuclar$rhs[row],"','",sonuclar$support[row],"','",sonuclar$confidence[row],"','",sonuclar$lift[row],"')"))}`

but it writes for only one row , it doesn't work for other loop iteration steps and it returns an error message :

`Error in .verify.JDBC.result(md, "Unable to retrieve JDBC result set meta data for ",  : 
  Unable to retrieve JDBC result set meta data for insert into market_basket_analysis (lhs,rhs,support,confidence,lift) values ('{SPRITE GAZOZ1,5L}','{COCACOLA # 1,5LT}','0.00395004588437138','0.439024390243902','1.99010097534508') in dbSendQuery (ORA-00900: invalid SQL statement
)`

even though it gives an error , it inserted

{SPRITE GAZOZ1,5L},{COCACOLA # 1,5LT},'0.00395043993535644','0.439024390243902','1.98990246549237'

this is the first row.

I have not found meaningful information in the internet although i have searched a lot for over a week ; have any idea to solution about this error. Thanks in advance.

lrnzcig · Accepted Answer · 2016-03-18 16:24:56Z

1

For me it was also a bit surprising that it did not work, but yes, it is actually fairly easy to reproduce with the code you provide. Until the moment, whenever I had to do something similar I've used dbWriteTable, I wonder if you can't do it for whatever the reason.

Anyway, if you really need or want to insert in a loop, you should use bind variables. Your loop would look like this,

for(row in 1:nrow(sonuclar)) {
 inss <-  paste0("insert into temp (lhs,rhs,support,confidence,lift) values (?, ?, ?, ?, ?)")
 dbSendUpdate(con, inss, list=as.list(sonuclar[row, ]))
}

(Note that my connection is called con and the table temp, instead of your names...) Just for reference, I've defined your data.frame like this,

sonuclar <- data.frame(matrix(c('{SPRITE GAZOZ1,5L}','{COCACOLA # 1,5LT}',0.00395004588437138,0.439024390243902,1.99010097534508,
                                '{SPRITE GAZOZ1,5L}','{COCACOLA # 1,5LT}',0.00395043993535644,0.439024390243902,1.98990246549237),
                              nrow=2, ncol=5, byrow=TRUE))
colnames(sonuclar) <- c('lhs','rhs','support','confidence','lift')

and in the table I've used varchar(20) for lhs and rhs, and number(10,4) for the other columns.

A final note, I don't know how big is your loop, but it would be better if you disabled your autocommit (by default a TRUE),

.jcall(con@jc, "Z", "getAutoCommit")
[1] TRUE
.jcall(con@jc, "V", "setAutoCommit", FALSE)

But then you need to commit outside of your loop, just

dbCommit(con)

Hope it helps.

edited Mar 18, 2016 at 16:24

answered Mar 18, 2016 at 15:55

lrnzcig

3,9474 gold badges40 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pcpcne Over a year ago

yes it really helps , thanks a lot for that . at first i had used dbwritetable but it had returned similar error.

lrnzcig Over a year ago

Thank you, glad it was useful. For me it worked fine with the simple example dataframe when using dbWriteTable... but anyway it is true that dbWriteTable has some disadvantages, does not allow you full control.

Collectives™ on Stack Overflow

Write data into oracle database in r using loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related