Sending Queries to a table in RMySQL, using R variables-CodePudding

I am trying to write data I have created in R and stored in arrays to MySQL table. Currently this is how I am doing it,

for(z in 1:60){
  dbSendQuery(
    mydb, 
    "insert into minutely_predictions(id,server_pred, server_hi, server_lo, os_pred, os_hi, os_lo, sessions_id) values(server__id,server_pred[i],server_hi[i],server_lo[i],os_pred[i],os_hi[i],os_lo[i],sesh_id)"
  )  
}

I'm getting MySQL syntax errors, is there a way to pass the variables I am trying to pass from R into MySQL?

Thanks.

CodePudding user response：

Consider the best practice of parameterization and avoid string concatenation or interpolation of variables to SQL statements.

# PREPARED STATEMENT WITH QMARK PLACEHOLDERZ
sql <- "insert into minutely_predictions(
            id, server_pred, server_hi, server_lo, 
            os_pred, os_hi, os_lo, sessions_id) 
        values (?, ?, ?, ?, ?, ?, ?, ?)"

# ITERATE THROUGH ARRAYS AND BIND PARAMS
for(i in 1:60) { 
    stmt <- dbSendStatement(mydb, sql)
    dbBind(
        stmt,
        list(
            server__id, server_pred[i], server_hi[i], server_lo[i],
            os_pred[i], os_hi[i], os_lo[i], sesh_id
        )
    )
    dbClearResult(stmt)
}

Note: above may only work if using the preferred RMariaDB (replacement of legacy RMySQL).

CodePudding user response：

I was able to fix it by using the function sprintf and storing the variable that way, hopefully this may help others in the future.

queryform = "insert into table(parameters) values('%d' .....)"
query = sprintf(queryform, value[1])
send = dbSendQuery(mydb,query)