How to insert integer values with query in MySQL in R?

Question

I come to the situation where I need to insert the integer values in the table with query which is written in R. for Example:

>n1<-20
>n2<-30
>library(DBI)
>library(RMySQL)
>drv<-dbDriver("MySQL")
>con<-dnConnect(drv,user="root",password="sam123",dbname="user")
>dbSendQuery(con,"insert into test values(n1,n2);") # Problem with this lines only

following error I am getting:

Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: Unknown column 'n1' in 'field list')

Please help me out of these problem

score 0 · Accepted Answer · answered Feb 04 '13 at 06:37

0

try the paste function to concatenate

 dbSendQuery(con,paste("insert into test values(",n1,",",n2,");",sep=""));

answered Feb 04 '13 at 06:37

Aditya Sihag

5,057
4
32
43

Thank you so much its working fine for me.If possible can you explain me that "how the above code is working?" – Saurabh Feb 04 '13 at 06:50
@user1985919 the paste function is the concatenation function for R. It takes as input strings (eg "insert into test values") and variables (eg n1, n2). the "," inside the function just indicates "concatenate", while sep parameter acts as "concatenate by". you can always type ?paste and see the examples provided. your original code used n1 and n2 within quotes, and hence it was not interpreted as variable names, but literally as the string "n1,n2" – Aditya Sihag Feb 04 '13 at 06:53

score 0 · Answer 2 · answered Feb 05 '13 at 00:04

0

Try this:

library(gsubfn)

fn$dbSendQuery( con, "insert into test values($n1, $n2)" )

answered Feb 05 '13 at 00:04

G. Grothendieck

254,981
17
203
341

How to insert integer values with query in MySQL in R?

2 Answers2

Linked