Parameterized query in PostgreSQL vs SQL Server

Question

In one of my R projects, I am using the following code to insert some user information into a PostgreSQL database, but now I would like to switch this to a SQL Server. Unfortunately, this is failing.

library(DBI)
conn <- #database connection
# data$data is a list 
    dbExecute(
              conn,
              "INSERT INTO users (user_uuid, user_firstname, user_lastname, user_email, 
              user_role, owner_uuid, user_password, flag_user_active) VALUES ($1, $3, $4, $5, $6, $7, $2, TRUE)",
              params = c(
                list(user_uuid),
                list(user_password_encrypted),
                unname(data$data)
              )
            )

I have the feeling that SQL server handles the parameterized queries different than PostgreSQL, but unfortunately I was not able to find a clear answer on how to modify it so it works well. I hope somebody has this answer readily available. Thanks!

Answer 1

The code below does the job with SQL Server, but it is not as nice as the code in the question that works for PostgreSQL. The main draw back is that you cannot reference the variables in VALUES in the same way as done in PostgreSQL.

library(DBI)
conn <- #database connection
# data$data is a list 
    dbExecute(
              conn,
              "INSERT INTO users (user_uuid, user_firstname, user_lastname, user_email, 
              user_role, owner_uuid, user_password, flag_user_active) VALUES (?, ?, ?, ?, ?, ?)",
              params = c(
                list(user_uuid),
                list(user_password_encrypted),
                unname(data$data)
              )
            )

Note that the order of the parameters needs to be the same as the order in the INSERT statement.