CODEWITHSUNDEEP
mysqlsqlrrstudiorodbc
foboss
foboss

Reputation: 440

SQLQuery affect only one row with insert query

I have a problem with an insertion Into data base, i create a function to insert data into a table but it affects only the first row.

This is my function : 

add<- function (x,y,z){
    channel <- odbcConnect(dsn="R",uid="root",pwd="toor")
    num <- y
    s <- x
    l<-z
    requetesql <- paste("INSERT INTO `table`  (`S`,`Num`, `L`) VALUES ('",s,"','",num,"', '",l,"');")
    sqlQuery(channel,requetesql)
}

I am using SQLQuery with RODBC. There is sqlSave which works with dataframes, but i cannot use it, because I have to change all my program and do it from scratch.

You can see here that my query is working perfectly

  [1] "INSERT INTO `table`  (`S`,`Num`, `L`) VALUES (' 1 ', ' 3 ', ' a');"
  [2] "INSERT INTO `table`  (`S`,`Num`, `L`) VALUES (' 1 ', ' 3 ', ' b ');"
  [3] "INSERT INTO `table`  (`S`,`Num`, `L`) VALUES (' 1 ', ' 3 ', ' c ');"
  [4] "INSERT INTO `table`  (`S`,`Num`, `L`) VALUES (' 1  ', ' 3 ', ' d ');" 
  [5] "INSERT INTO `table`  (`S`,`Num`, `L`) VALUES (' 1 ', ' 3 ', ' e ');"

Upvotes: 0

Views: 172

Answers (1)

rischan
rischan

Reputation: 1585

I think for inserting multiple rows you need to give parameter number of rows to fetch.

sqlQuery(channel, query, errors = TRUE, ..., rows_at_time)
rows_at_time The number of rows to fetch at a time, between 1 and 1024. See ‘Details’

you can see the complete manual => page number 18 http://cran.r-project.org/web/packages/RODBC/RODBC.pdf

thank you

Upvotes: 1

Related Questions