Search code examples
rrodbcr-dbiroracle

R DBI dbWriteTable "Not compatible with requested type" error when writing R data frame to Oracle SQL database

I would like to write a data frame from R to an Oracle SQL database using the DBI package. I am able to do this successfully with the RODBC package, but I'd like to be able to correct this error with the DBI package.

The error I get is Error: Not compatible with requested type: [type=character; target=double]. (see below)

CODE using DBI::dbWriteTable() - Error with this

# data frame in R, use mtcars
data("mtcars")

# connect to Oracle database
STG <- DBI::dbConnect(odbc::odbc(), dsn="XXX", uid="XX", pwd="XXXXX")

# write table to SQL database
DBI::dbWriteTable(STG, name = "DBM_TEST", value = mtcars[1:5, ], overwrite = TRUE)

# disconnect
DBI::dbDisconnect(STG)

OUTPUT using DBI::dbWriteTable() - Error with this

> data(mtcars)
> STG <- DBI::dbConnect(odbc::odbc(), dsn = "XXX", uid = "XX", 
+     pwd = "XXXXX")
> DBI::dbWriteTable(STG, name = "DBM_TEST", value = mtcars[1:5, ], overwrite = TRUE)
Error: Not compatible with requested type: [type=character; target=double].

Here, the table in the Oracle DB is created, but it is always empty.

CODE using RODBC::sqlSave() - works fine, but I want DBI to work

library(RODBC)

# connection to STG in UWDB
STG <- odbcConnect("XXX", uid="XXX", pwd="XXXXX")

# write table
try(sqlDrop(STG, 'DBM_TEST', errors = FALSE), silent = TRUE)
sqlSave(STG, mtcars, 'DBM_TEST', rownames = 'INDEX', addPK = TRUE, safer = FALSE)

# check table output from DB
sqlFetch(STG, "DBM_TEST", max = 5)

# disconnect
odbcClose(STG)

OUTPUT using RODBC::sqlSave()

> data(mtcars)
> STG <- odbcConnect("XXX", uid="XXX", pwd="XXXXX")
> try(sqlDrop(STG, 'DBM_TEST', errors = FALSE), silent = TRUE)
> sqlSave(STG, mtcars, 'DBM_TEST', rownames = 'INDEX', addPK = TRUE, safer = FALSE)
> sqlFetch(STG, "DBM_TEST", max = 5)
              INDEX  mpg cyl disp  hp drat    wt  qsec vs am gear carb
1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
> odbcClose(STG)

I tried specifying my own field.types (parameter in dbWriteTable()) and looked at the SQL data type translation that is automatically fed into dbWriteTable().

> DBI::dbDataType(STG, mtcars)
            mpg             cyl            disp              hp            drat              wt            qsec              vs              am 
"BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" "BINARY_DOUBLE" 
           gear            carb 
"BINARY_DOUBLE" "BINARY_DOUBLE"

I've tried a number of different data frames including a simple data frame with just integers. I've researched the error, but can't seem to figure it out in this context.

The tables are created in the Oracle DB, but never populated with the data.

Thank you in advance!

Solution

  • I've run into this issue after updating packages about a month ago. Dug into it today and the cause (for me) is the {odbc} v. 1.3.5 update from 2023-06-29.

    Try reverting to 1.3.4 via remotes::install_version("odbc", "1.3.4") until this issue is resolved (hopefully) in the next {odbc} update.