Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

INSERT with placeholders via dbExecute does not work #391

Open
stefan-m-lenz opened this issue Jan 31, 2022 · 4 comments
Open

INSERT with placeholders via dbExecute does not work #391

stefan-m-lenz opened this issue Jan 31, 2022 · 4 comments

Comments

@stefan-m-lenz
Copy link

stefan-m-lenz commented Jan 31, 2022

I try to execute an INSERT statement with placeholders in my Postgres DB. This gives me a syntax error.
This problem occurs also with the example from the DBI package for the DBI::dbExecute function.

The following is the code from the example for DBI::dbExecute, with the first line for the connection replaced with a Postgres data base,

con <- dbConnect(RPostgres::Postgres(),
                 dbname = "test",
                 user = "postgres",
                 password = "password")

dbWriteTable(con, "cars", head(cars, 3))
dbReadTable(con, "cars")   # there are 3 rows
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)"
)
dbReadTable(con, "cars")   # there are now 6 rows

# Pass values using the param argument:
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (?, ?)",
  params = list(4:7, 5:8)
)

dbDisconnect(con)

The first call to dbExecute works, but the second one with the placeholders yields an error:

Error: Failed to prepare query: FEHLER:  Syntax error at »,«
LINE 1: INSERT INTO cars (speed, dist) VALUES (?, ?)

I use PostgreSQL version 14, R version 4.1.2 and a freshly installed DBI and RPostgres package on Windows 10.
Is this a bug or do I miss something?

@krlmlr
Copy link
Member

krlmlr commented Jan 31, 2022

Thanks. Do you need $1 and $2 instead of ? ?

What's the best place to document this?

@stefan-m-lenz
Copy link
Author

Thank you very much for the fast response.

Yes, this works:

dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES ($1, $2)",
  params = list(4:7, 5:8)
)

I thought that all SQL dialects use a question mark as placeholder. Well, I was wrong. Thanks for helping me to learn this.

I would suggest to add a simple example for an INSERT or other statement that uses placeholders in the documentation. The README example could be a good place, as using placeholders is a practice that should be encouraged.

Additionally, the postgres-query documentation item ("Execute a SQL statement on a database connection") could be expanded with such a simple INSERT example and a brief explanation could be added there that PostgreSQL uses different placeholders.

@medeirosramos

This comment was marked as off-topic.

@krlmlr

This comment was marked as off-topic.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants