`dbExecute()` doesn't return the number of rows affected with Oracle

[ThomasSoeiro]

dbExecute() doesn't return the number of rows affected with Oracle.

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "xxx")
dbExecute(con, "CREATE TABLE MYTAB AS SELECT 1 AS X FROM DUAL;")
# [1] 0

Database

```r dbGetInfo(con) # $dbname # [1] "" # # $dbms.name # [1] "Oracle" # # $db.version # [1] "19.00.0000" # # $username # [1] "" # # $host # [1] "" # # $port # [1] "" # # $sourcename # [1] "xxx" # # $servername # [1] "xxx" # # $drivername # [1] "RStudio Oracle ODBC Driver" # # $odbc.version # [1] "03.52" # # $driver.version # [1] "2.0.2.1002" # # $odbcdriver.version # [1] "03.80" # # $supports.transactions # [1] TRUE # # $getdata.extensions.any_column # [1] TRUE # # $getdata.extensions.any_order # [1] TRUE # # attr(,"class") # [1] "Oracle" "driver_info" "list" ```

Session Info

```r sessionInfo() # R version 4.1.2 (2021-11-01) # Platform: x86_64-pc-linux-gnu (64-bit) # Running under: Red Hat Enterprise Linux Server 7.8 (Maipo) # # Matrix products: default # BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so # # locale: # [1] LC_CTYPE=fr_FR.UTF-8 LC_NUMERIC=C LC_TIME=fr_FR.UTF-8 LC_COLLATE=fr_FR.UTF-8 # [5] LC_MONETARY=fr_FR.UTF-8 LC_MESSAGES=fr_FR.UTF-8 LC_PAPER=fr_FR.UTF-8 LC_NAME=C # [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C # # attached base packages: # [1] stats graphics grDevices datasets utils methods base # # other attached packages: # [1] DBI_1.0.0 # # loaded via a namespace (and not attached): # [1] bit_1.1-14 odbc_1.3.5 compiler_4.1.2 ellipsis_0.3.2 hms_1.1.1 tools_4.1.2 Rcpp_1.0.7 bit64_0.9-7 # [9] vctrs_0.3.8 blob_1.2.2 lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12 ```

[hadley]

It does work for SQL server, suggesting that this might be a problem with the driver, i.e. outside the scope of odbc:

library(DBI)

con <- dbConnect(
  odbc::odbc(), 
  dsn = "MicrosoftSQLServer", 
  uid = "SA", 
  pwd = "BoopBop123"
)
dbExecute(con, "SELECT 1 as X INTO #mytab")
#> [1] 1

^{Created on 2024-01-19 with reprex v2.0.2.9000}

[ThomasSoeiro]

FWIW, The same issue also occurs with Oracle OEM driver:

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "zzz")
dbExecute(con, "CREATE TABLE MYTAB AS SELECT 1 AS X FROM DUAL;")
# [1] 0

But the table is actually created as expected in both cases:

dbReadTable(con, "MYTAB")
#   X
# 1 1

Database

```r dbGetInfo(con) # $dbname # [1] "" # # $dbms.name # [1] "Oracle" # # $db.version # [1] "19.00.0000" # # $username # [1] "" # # $host # [1] "" # # $port # [1] "" # # $sourcename # [1] "zzz" # # $servername # [1] "zzz" # # $drivername # [1] "SQORA32.DLL" # # $odbc.version # [1] "03.52" # # $driver.version # [1] "12.01.0020" # # $odbcdriver.version # [1] "03.52" # # $supports.transactions # [1] TRUE # # $getdata.extensions.any_column # [1] TRUE # # $getdata.extensions.any_order # [1] TRUE # # attr(,"class") # [1] "Oracle" "driver_info" "list" ```

Session Info

```r sessionInfo() # R version 4.1.2 (2021-11-01) # Platform: x86_64-pc-linux-gnu (64-bit) # Running under: Red Hat Enterprise Linux Server 7.8 (Maipo) # # Matrix products: default # BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so # # locale: # [1] LC_CTYPE=fr_FR.UTF-8 LC_NUMERIC=C LC_TIME=fr_FR.UTF-8 LC_COLLATE=fr_FR.UTF-8 # [5] LC_MONETARY=fr_FR.UTF-8 LC_MESSAGES=fr_FR.UTF-8 LC_PAPER=fr_FR.UTF-8 LC_NAME=C # [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C # # attached base packages: # [1] stats graphics grDevices datasets utils methods base # # other attached packages: # [1] DBI_1.0.0 # # loaded via a namespace (and not attached): # [1] bit_1.1-14 odbc_1.3.5 compiler_4.1.2 ellipsis_0.3.2 hms_1.1.1 tools_4.1.2 Rcpp_1.0.7 bit64_0.9-7 # [9] vctrs_0.3.8 blob_1.2.2 lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12 ```

[ThomasSoeiro]

It seems that whether the number of rows affected is correctly returned depends on the SQL statement:

dbExecute(con, "create table test as select 1 as num from dual;")
# [1] 0
dbExecute(con, "delete from test where num = 1;")
# [1] 1

[hadley]

I think that suggests even more strongly it's an issue with the underlying driver, and therefore there's unfortunately nothing we can do about it.

[ThomasSoeiro]

Should it be forwarded to the maker of RStudio ODBC driver?

[joranE]

So far the examples of it reporting zero rows affected all involve querying "dual". It might be worth confirming whether the issue is limited involving dual and whether that is actually the expected behavior or not (I can't recall how Oracle treats rows affected when querying dual).

[ThomasSoeiro]

@joranE

The issue is not limited to dual:

dbExecute(con, "
CREATE TABLE FOOBAR (
  FOO NUMBER,
  BAR NUMBER
);
")
# [1] 0

dbExecute(con, "INSERT INTO FOOBAR VALUES (1, 2);")
# [1] 1
dbExecute(con, "INSERT INTO FOOBAR VALUES (3, 4);")
# [1] 1

dbExecute(con, "DELETE FROM FOOBAR WHERE FOO = 1")
# [1] 1

dbExecute(con, "CREATE TABLE FOOBAR2 AS SELECT * FROM FOOBAR")
# [1] 0

[ThomasSoeiro]

@hadley

Should it be forwarded to the maker of RStudio ODBC driver? Thanks!

[hadley]

@ThomasSoeiro they are very slow to respond and this seems like a very minor issue, so I don't think it is worth it.