append_data() throws warnings

[mthomas-ketchbrook]

Issue

Within the append_single_data() function, the DBI::dbSendQuery() function is used to delete any rows of data that exist in the same DATA_PERIOD in the database for the table being operated on. Running this for a specific quarter's Call Report data results in the following:

Step 1/2: Removing 2024_09 data from INST
Step 2/2: Adding 2024_09 data to INST
2024_09 data added to INST
Step 1/2: Removing 2024_09 data from RC
Step 2/2: Adding 2024_09 data to RC
2024_09 data added to RC
...
There were 35 warnings (use warnings() to see them)
> warnings()
Warning messages:
1: Closing open result set, cancelling previous query
2: Closing open result set, cancelling previous query
...

We should not be using DBI::dbSendQuery(), since we are executing a SQL statement, not a SQL query. We should instead be using a statement-based function (e.g., DBI::dbSendStatement(); or DBI::dbExecute() if we want to send the statement, retrieve the number of affected rows, and clear the result set before running another query/statement)

Reproducible Example

# Create an in-memory SQLite database using 'mtcars'
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "cars", head(cars, 3))
DBI::dbReadTable(con, "cars")   # there are 3 rows

# Execute a 'DELETE' SQL statement, but don't clear the results
DBI::dbSendQuery(
  con,
  "DELETE FROM cars WHERE dist = 10"
)

# Running this should produce a warning
DBI::dbReadTable(con, "cars")

Solution

Use DBI::dbExecute() with the same SQL statement, instead of DBI::dbSendQuery(). We could also save the result (representing the number of affected rows) to an object that can be passed into the output message, letting the user know how many rows were deleted from the target table.