Execute parameterized SQL statements

Executes a parameterized SQL statement (UPDATE, DELETE, or INSERT) using DBI::dbExecute(). This is the preferred method for executing statements with dynamic values as it prevents SQL injection attacks.

Use positional parameters ($1, $2, etc. for PostgreSQL) in your SQL and provide values via the params argument.

Usage

db_art_execute(qry, params = NULL, cn = NULL)

Arguments

qry

Character. SQL query with parameter placeholders. For PostgreSQL, use $1, $2, etc. for positional parameters.

params

List. Parameter values to bind to the query placeholders. Values are bound in order ($1 = first element, $2 = second, etc.). Default: NULL (no parameters).

cn

Database connection. If NULL, creates a connection via artcore::dbc() and closes it on exit. Pass an existing connection to batch multiple queries efficiently.

Value

Integer. Number of rows affected by the query.

See also

• db_art_update() for raw SQL without parameters

• db_art_append() for inserting data.table rows

• DBI::dbExecute() for the underlying function

Other db-interface: db_art_append(), db_art_get(), db_art_update()

Examples

if (FALSE) { # \dontrun{
# Update a single field with parameterized query
n_updated <- db_art_execute(
  qry = "UPDATE app.artwork_index SET purchase_url = $1 WHERE art_uuid = $2",
  params = list("https://example.com/print", "99xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
)
message("Updated ", n_updated, " rows")

# Delete with parameterized query
n_deleted <- db_art_execute(
  qry = "DELETE FROM app.artwork_tags WHERE art_uuid = $1",
  params = list(artwork)
)

# Use with existing connection for batched operations
cn <- artcore::dbc()
on.exit(artcore::dbd(cn))

db_art_execute(
  "UPDATE app.artwork_stats SET is_featured = $1 WHERE art_uuid = $2",
  params = list(TRUE, artwork),
  cn = cn
)
} # }