dbAppendTable() isn't returning the number of rows affected

[adamcagle]

Issue Description and Expected Result

dbAppendTable() does not return the number of rows affected

Database

PostgreSQL 11.0.6

Reproducible Example

library(DBI)

# Appends table and gives the correct number of rows affected
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbCreateTable(con, "iris", iris)
rows_affected <- dbAppendTable(con, "iris", iris)
#> Warning: Factors converted to character
rows_affected
#> [1] 150
head(dbReadTable(con, "iris"))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa
dbDisconnect(con)

# Appends table but doesn't return the number of rows affected
con <- DBI::dbConnect(odbc::odbc(), "postgresql_testing")
dbCreateTable(con, "iris", iris)
rows_affected <-dbAppendTable(con, "iris", iris)
rows_affected
#> [1] NA
head(dbReadTable(con, "iris"))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa
dbRemoveTable(con, "iris")
dbDisconnect(con)

# Appends table but always returns 1 as the number of rows affected
con <- DBI::dbConnect(odbc::odbc(), "postgresql_testing")
dbCreateTable(con, "iris", iris)
template <- sqlAppendTableTemplate(con = con, table = "iris", values = iris, row.names = FALSE)
rows_affected <- dbExecute(conn = con, statement = template, params = iris)
rows_affected
#> [1] 1
head(dbReadTable(con, "iris"))
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2       1
#> 2          4.9         3.0          1.4         0.2       1
#> 3          4.7         3.2          1.3         0.2       1
#> 4          4.6         3.1          1.5         0.2       1
#> 5          5.0         3.6          1.4         0.2       1
#> 6          5.4         3.9          1.7         0.4       1
dbRemoveTable(con, "iris")
dbDisconnect(con)

# Also tried this approach. Appends table but always returns 1 as the number of rows affected
con <- DBI::dbConnect(odbc::odbc(), "postgresql_testing")
dbCreateTable(con, "iris", iris)
template <- sqlAppendTableTemplate(con = con, table = "iris", values = iris, row.names = FALSE)
dm_statement <- dbSendStatement(conn = con, statement = template)
(dbBind(dm_statement, params = iris))
#> <OdbcResult>
#>   SQL  INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (?, ?, ?, ?, ?)
#>   ROWS Fetched: 0 [complete]
#>        Changed: 1
rows_affected <- dbGetRowsAffected(dm_statement)
rows_affected
#> [1] 1
head(dbReadTable(con, "iris"))
#> Warning in new_result(connection@ptr, statement, immediate): Cancelling previous
#> query
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2       1
#> 2          4.9         3.0          1.4         0.2       1
#> 3          4.7         3.2          1.3         0.2       1
#> 4          4.6         3.1          1.5         0.2       1
#> 5          5.0         3.6          1.4         0.2       1
#> 6          5.4         3.9          1.7         0.4       1
dbRemoveTable(con, "iris")
dbClearResult(dm_statement)
#> Warning in dbClearResult(dm_statement): Result already cleared
dbDisconnect(con)

# DB Info
con <- DBI::dbConnect(odbc::odbc(), "postgresql_testing")
DBI::dbGetInfo(con)
#> $dbname
#> [1] "testing"
#> 
#> $dbms.name
#> [1] "PostgreSQL"
#> 
#> $db.version
#> [1] "11.0.6"
#> 
#> $username
#> [1] "script_user"
#> 
#> $host
#> [1] ""
#> 
#> $port
#> [1] ""
#> 
#> $sourcename
#> [1] "postgresql_testing"
#> 
#> $servername
#> [1] "10.1.2.82"
#> 
#> $drivername
#> [1] "psqlodbcw.so"
#> 
#> $odbc.version
#> [1] "03.52"
#> 
#> $driver.version
#> [1] "12.01.0000"
#> 
#> $odbcdriver.version
#> [1] "03.51"
#> 
#> $supports.transactions
#> [1] TRUE
#> 
#> $getdata.extensions.any_column
#> [1] TRUE
#> 
#> $getdata.extensions.any_order
#> [1] TRUE
#> 
#> attr(,"class")
#> [1] "PostgreSQL"  "driver_info" "list"
dbDisconnect(con)

# Session Info
sessionInfo()
#> R version 3.6.3 (2020-02-29)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 18.04.4 LTS
#> 
#> Matrix products: default
#> BLAS:   /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
#> LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1
#> 
#> locale:
#>  [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
#>  [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
#>  [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
#>  [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
#>  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
#> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] DBI_1.1.1
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.6        knitr_1.32        magrittr_2.0.1    hms_1.0.0        
#>  [5] odbc_1.3.2        bit_4.0.4         rlang_0.4.10      fastmap_1.1.0    
#>  [9] fansi_0.4.2       blob_1.2.1        stringr_1.4.0     styler_1.4.1     
#> [13] highr_0.9         tools_3.6.3       xfun_0.22         utf8_1.2.1       
#> [17] withr_2.4.2       htmltools_0.5.1.1 ellipsis_0.3.1    bit64_4.0.5      
#> [21] yaml_2.2.1        digest_0.6.27     tibble_3.1.1      lifecycle_1.0.0  
#> [25] crayon_1.4.1      purrr_0.3.4       vctrs_0.3.7       fs_1.5.0         
#> [29] cachem_1.0.4      memoise_2.0.0     RSQLite_2.2.6     glue_1.4.2       
#> [33] evaluate_0.14     rmarkdown_2.7     reprex_2.0.0      stringi_1.5.3    
#> [37] compiler_3.6.3    pillar_1.6.0      backports_1.1.6   pkgconfig_2.0.3

^{Created on 2021-04-20 by the reprex package (v2.0.0)}

[simonpcouch]

The documentation for the DBI generic reads:

Value
dbAppendTable() returns a scalar numeric.

The documentation for the odbcConnection method doesn't document a return value, though the method always returns NA_real_.

I think this method outputs as intended. 🙂