Problem inserting data into MySQL from R with a loop

Question

good afternoon and thanks for reading. I currently have to manually enter data into SQL and am trying to automate the data using a loop in R, but when entering the query and reviewing the information in MySQL, I don't see any new observations added.

Basically, that's the code to input the observations that I'm using (using other data that I'm looping out of a data frame):

library(dplyr)
library(odbc)
library(DBI)
library(RMySQL)

connection <- dbConnect(RMySQL::MySQL(),
                        dbname = "xxx",
                        host=  "xxx",
                        port = xxx,
                        user = "xxx",
                        password = "xxx")

payout_history <- paste0("INSERT INTO `payout_history` (`uuid`, `trip_id`, `delivery_order_id`, `amount`, `payout_type_id`, `payout_invoice_id`) VALUES (uuid(), '",id,"', '",order_id,"', '",amount,"', '7', '",head(payout_invoice$id,1),"');")
    
dbExecute(connection,payout_history)

The query works when I try to use it within the MySQL manager, but within the R console with the dbExecute() function it doesn't add anything, do you know if I should use any other function to be able to insert observations?

Answer 1

I haven't taken a close look to make sure your SQL statement has proper syntax. Assuming that isn't the problem, have you tried using dbSendQuery()?

I've accomplished what it looks like you are trying to do using

result <- dbSendQuery(connection, payout_history)
dbClearResult(result)

Once caveat, I was connecting to a MySQL server using

connection <- dbConnect(RMariaDB::MariaDB(), ...