0

Through a for loop with R language, I'm trying to use an insert statement to save rows in a table: One row example looks like this :

NUMPAT   NAME       FIRSTNAM     BIRTHDATE     SEX    DATPREL      ADICAP1   IDPAT   NUMERORUM 
 1       ELOSTE      JAMES      2003-09-27      1    2008-03-24    BHOTE4P1   468        2

What i've tried to write is:

 info<- paste("INSERT INTO tab_anapath_std1 VALUES (",matOp[i,1],", \",matOp[i,2],\",\",matOp[i,3], \",",matOp[i,4],",",matOp[i,5],",",matOp[i,6],",\",matOp[i,7],\",",matOp[i,8],",",matOp[i,9],")")
 sql_update_tbl_ds <- fn$dbSendQuery(dbconn, info)

And the output I got is : 

 NUMPAT   NAME       FIRSTNAM       BIRTHDATE     SEX    DATPREL      ADICAP1      IDPAT     NUMERORUM   
1    ,matOp[i,2],    ,matOp[i,3],    0000-00-00    1    0000-00-00   ,matOp[i,7],    468       2

I have a real problem to manage the quotes ; i've even tried to change without success; How may I write it please ?

freestyle
  • 67
  • 8

2 Answers2

1

If you are using fn$ there is no need to use paste. fn$ is an alternative to paste. Just write out the string and wherever you want to insert R code surround that R code with backticks. Here is a self contained example of using fn$ making use of the built in BOD data frame. 

library(sqldf)
matOp <- matrix(1:4, 1, 4)
i <- 1

sql <- "select * from BOD where Time = `matOp[i, 4]`"

fn$identity(sql)
## [1] "select * from BOD where Time = 4"

fn$sqldf(sql)
##   Time demand
## 1    4     16
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Applying your suggestion, here is the what I did : – freestyle Jul 17 '15 at 09:42
  • Applying your suggestion, here is the what I did : >`reqSql <- "INSERT INTO tab_anapath_std VALUES (`matOp[i, 1]`,\"`matOp[i, 2]`,`matOp[i, 3]`,`matOp[i, 4]`,`matOp[i, 5]`,`matOp[i, 6]`,`matOp[i, 7]`,`matOp[i, 8]`,`matOp[i, 9]`,`matOp[i, 10]`,`matOp[i, 11]`,`matOp[i, 12]`,`matOp[i, 13]`)" sql_update_tbl_ds <- fn$dbSendQuery(dbconn, reqSql)` That's the errors message: `could not run statement: You have an error... near '"ELOSTE,JAMES..` Like you can see i've used backticks but the same error I've – freestyle Jul 17 '15 at 10:11
  • 1
    Please make this reproducible by showing the value of `i` and `matOp[i,]` and `fn$identity(reqSql)` and put this in the question, not the comments since SO is evidently messing up the text in your comment due to the fact that back ticks have a special meaning when used in SO comments. It may be that you are not properly quoting your data as required but we can't really tell since what was posted in the comment is so messed up. – G. Grothendieck Jul 17 '15 at 11:11
0

Trying doing this with the sprintf function.

info <- sprintf("INSERT INTO tab_anapath_std1 VALUES ('%s', '%s', '%s', '%d', '%s', '%s', '%d', '%d')", matOp[i, 1], matOp[i, 2], matOp[i, 3], matOp[i, 4], matOp[i, 4], matOp[i, 5], matOp[i, 6], mat[i, 7], mat[i, 8], mat[i, 9])
Josh W.
  • 1,123
  • 1
  • 10
  • 17