CODEWITHSUNDEEP
rrodbcrmysql
Ami
Ami

Reputation: 319

Inserting a R dataframe in SQL table using a stored proc

I have a dataframe in R containing 10 rows and 7 columns. There's a stored procedure that does the few logic checks in the background and then inserts the data in the table 'commodity_price'.

library(RMySQL)
#Connection Settings
mydb = dbConnect(MySQL(),
                 user='uid',
                 password='pwd',
                 dbname='database_name',
                 host='localhost')

#Listing the tables
dbListTables(mydb)

f= data.frame(
    location= rep('Bhubaneshwar', 4),
    sourceid= c(8,8,9,2),
    product= c("Ingot", "Ingot", "Sow Ingot", "Alloy Ingot"),
    Specification = c('ie10','ic20','se07','se08'),
Price=c(14668,14200,14280,20980),
currency=rep('INR',4),
uom=rep('INR/MT',4)
)

For multiple rows insert, there's a pre-created stored proc 'PROC_COMMODITY_PRICE_INSERT', which I need to call.


for (i in 1:nrow(f))
{
  dbGetQuery(mydb,"CALL PROC_COMMODITY_PRICE_INSERT(
     paste(f$location[i],',',
f$sourceid[i],',',f$product[i],',',f$Specification[i],',',
f$Price[i],',',f$currency[i],',', f$uom[i],',',@xyz,')',sep='')
  );")
}

I am repeatedly getting error.

Error in .local(conn, statement, ...) : 
  could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[i],',',
f$sourceid[i],',',f$product[i],',',f$Specification' at line 2

I tried using RODBC but its not getting connected at all. How can I insert the data from the R dataframe in the 'commodity_price' table by calling a stored proc? Thanks in advance!

Upvotes: 0

Views: 112

Answers (2)

Ami
Ami

Reputation: 319

Trying the for loop:

for (i in 1:nrow(f))
{
  dbGetQuery(mydb,paste("CALL PROC_COMMODITY_PRICE_INSERT(","'",f$location[i],"'",',',"'",
f$sourceid[i],"'",',',"'",f$product[i],"'",',',"'",f$Specification[i],"'",',',"'",
f$Price[i],"'",',',"'",f$currency[i],"'",',',"'",f$uom[i],"'",',','@xyz',sep='',");"))
}

Upvotes: 0

Maylo
Maylo

Reputation: 562

That is probably due to your use of ', this might work:

for (i in 1:nrow(f))
{
  dbGetQuery(mydb,paste("CALL PROC_COMMODITY_PRICE_INSERT(",f$location[i],',',
f$sourceid[i],',',f$product[i],',',f$Specification[i],',',
f$Price[i],',',f$currency[i],',', f$uom[i],',',"@xyz",sep='',");"))
}

or the one-liner:

dbGetQuery(mydb,paste0("CALL PROC_COMMODITY_PRICE_INSERT('",apply(f, 1, paste0, collapse = "', '"),"');"))

Upvotes: 1

Related Questions