dbWriteTable in oracle not working with new version of DBI and ODBC

[apalacio9502]

Hello @krlmlr and @hadley,

dbWriteTable in Oracle does not work with the new version of DBI and ODBC, seeing the errors that occur I believe that the cause of the problem is DBI and ODBC.

We currently use version 2023-05 of the Oracle professional drivers provided by Posit with the Posit Connect license.

Using DBI version 1.1.3 and ODBC 1.3.4 I can execute the following line of code.

  dbWriteTable(
    conn = conexion_destino,
    name = SQL("SCHEMA.TABLE"),
    value = datos,
    overwrite = TRUE,
    append = FALSE
  )

---

If you update the DBI version to 1.2.0, the following error appears:

Error in name@name[["table"]] : subscript out of bounds

---

If you update the ODBC version to 1.4.1, the following error appears:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbExistsTable’ for signature ‘"Oracle", "NULL"’

---

If you update the DBI version to 1.2.0 and ODBC version to 1.4.1, the following error appears:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbExistsTable’ for signature ‘"Oracle", "NULL"’

---

It should be noted that I do not use Id(schema="SCHEMA",table ="TABLE") instead of SQL("SCHEMA.TABLE") because whenever I have tried to use Id with Oracle the following error appears #439

Error: nanodbc/nanodbc.cpp:1509: 00000: [RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-00942: la tabla o vista no existe

I would like to know how we can support them in future tests to prevent these unexpected errors from arising. For example, in the case of ODBC, version 1.3.5 and 1.4.0 had problems with Oracle, and now we believe that version 1.4.1 of ODBC and version 1.2.0 DBI.

Regards,

[hadley]

Please file this on the odbc repo. It would be most useful if you could include a reprex generated by the reprex package, edited by hand to remove any secrets.

[apalacio9502]

Close this bug in favor of r-dbi/odbc#710

[detule]

Hi @hadley @apalacio9502

I could be off here, but at least part of the issue, one related to updated behavior of dbUnquoteIdentifier may belong in r-dbi/DBI ( issue with underscores belongs in package:odbc).

With 1.1.3 ( named arguments ):

> DBI::dbUnquoteIdentifier(conn, DBI::SQL("SA.DELETE_ME"))
[[1]]
<Id> schema = SA, table = DELETE_ME

With 1.2.0 ( unnamed arguments ):

> str(DBI::dbUnquoteIdentifier(conn, DBI::SQL("SA.DELETE_ME"))[[1]])
Formal class 'Id' [package "DBI"] with 1 slot
..@ name: Named chr [1:2] "SA" "DELETE_ME"
.. ..- attr(*, "names")= chr [1:2] "" ""

[krlmlr]

Yeah, odbc seems to rely on named components of Id() in its id_field() helper function. To me, this looks like the DBI change (introduced in #422) breaks odbc here, which can be fixed by odbc adapting to the change.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.