copy_to not working with given types since odbc 1.3.0

[shearer]

Issue Description and Expected Result

copy_to not working with given types since odbc 1.3.0

Database

SQL Server 2016

Reproducible Example

Example:

library(dplyr)
library(DBI)

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={ODBC Driver 17 for SQL Server};Server= ;Database= ;Uid= ;\nPwd= ;\n",  timeout = 100)

dat <- data.frame(x = rep(c("A", "B", "C"), 3), y = rep(c(1, 2, 3), 3), z = rep(NA, 9), d = rep(as.Date("1999-09-09"), 9))

copy_to(con, dat, types = c(
  "varchar(1)",
  "numeric(3,1)",
  "date",
  "date"
), overwrite = TRUE
)

copy_to(con, dat, types = c(
  "varchar(1)",
  "numeric(3,1)",
  "date",
  "date"
),
temporary = FALSE, overwrite = TRUE
)

copy_to stops with the following error message. With odbc 1.2.3 everything is working fine, but since 1.3.0 the error occurs.

Fehler in .local(conn, name, value, ...) : 
  rlang::is_null(field.types) || (rlang::is_named(field.types)) is not TRUE

[adithya604]

I found similar issue with Teradata as well.

1. With odbc 1.3.1

> library(DBI)
> library(dplyr)
  ...
  ...
> packageVersion("odbc")
[1] '1.3.1'
> con <- dbConnect(odbc::odbc(), "REPLACE_DSN")
> dat <- data.frame(x = rep(c("A", "B", "C"), 3), y = rep(c(1, 2, 3), 3), z = rep(NA, 9), d = rep(as.Date("1999-09-09"), 9))
> dat
  x y  z          d
1 A 1 NA 1999-09-09
2 B 2 NA 1999-09-09
3 C 3 NA 1999-09-09
4 A 1 NA 1999-09-09
5 B 2 NA 1999-09-09
6 C 3 NA 1999-09-09
7 A 1 NA 1999-09-09
8 B 2 NA 1999-09-09
9 C 3 NA 1999-09-09
> copy_to(con, dat, types = c(
+   "varchar(1)",
+   "numeric(3,1)",
+   "date",
+   "date"
+ ), overwrite = TRUE
+ )
Error in .local(conn, name, value, ...) :
  rlang::is_null(field.types) || (rlang::is_named(field.types)) is not TRUE
>

2. With odbc 1.2.3 and 1.2.2 (interestingly it failed with object not found even though the object dat exists)

> library(DBI)
> library(dplyr)
   ...
   ...
> packageVersion("odbc")
[1] '1.2.3'
> con <- dbConnect(odbc::odbc(), "REPLACE_DSN")
> dat <- data.frame(x = rep(c("A", "B", "C"), 3), y = rep(c(1, 2, 3), 3), z = rep(NA, 9), d = rep(as.Date("1999-09-09"), 9))
> copy_to(con, dat, types = c(
+   "varchar(1)",
+   "numeric(3,1)",
+   "date",
+   "date"
+ ), overwrite = TRUE
+ )
Error in new_result(connection@ptr, statement, immediate) :
  nanodbc/nanodbc.cpp:1374: 00000: [Teradata][ODBC Teradata Driver][Teradata Database](-3807)Object 'dat' does not exist.
> dat
  x y  z          d
1 A 1 NA 1999-09-09
2 B 2 NA 1999-09-09
3 C 3 NA 1999-09-09
4 A 1 NA 1999-09-09
5 B 2 NA 1999-09-09
6 C 3 NA 1999-09-09
7 A 1 NA 1999-09-09
8 B 2 NA 1999-09-09
9 C 3 NA 1999-09-09

Its a bit of surprise for me with odbc 1.2.3 and 1.2.2. Do not know if I am doing anything wrong here?

[shearer]

Works with odbc_1.3.1 with the following modification: The types vector must be a named vector.

Further I had to change the type for z to varchar due to only NAs in the column. But this has nothing to do with the underlying issue.

library(dplyr)
library(DBI)

con <- dbConnect(odbc::odbc(), .connection_string = "Driver={ODBC Driver 17 for SQL Server};Server= ;Database= ;Uid= ;\nPwd= ;\n",  timeout = 100)

dat <- data.frame(x = rep(c("A", "B", "C"), 3), y = rep(c(1, 2, 3), 3), z = rep(NA, 9), d = rep(as.Date("1999-09-09"), 9))


copy_to(con, dat, types = c(
  x = "varchar(1)",
  y = "numeric(3,1)",
  z = "varchar(4)",
  d = "date"
),
temporary = FALSE, overwrite = TRUE
)

[hadley]

field.types now needs to be a named vector so it's more clear how the types align with the fields.