odbc
odbc copied to clipboard
copy_to not working with given types since odbc 1.3.0
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
I found similar issue with Teradata as well.
- 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
>
- 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?
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
)
field.types
now needs to be a named vector so it's more clear how the types align with the fields.