search

r-dbi / odbc

Connect to ODBC databases (using the DBI interface)
https://odbc.r-dbi.org/
Other
388 stars 107 forks source link

dbDataType chooses `VARCHAR(255)` even when `nchar` > 255 #841

Closed metanoid closed 1 week ago

metanoid commented 2 weeks ago

For a connection of type NetezzaSQL, the dbDataType function returns "VARCHAR(255)" for seemingly all character vectors, regardless of the number of characters of the longest string in that vector

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "Netezza")
test <- paste0(rep("a", 1000), collapse = "")
inferred_type <- dbDataType(con, test)

This returns: "VARCHAR(255)" even though there are clearly more than 255 characters required

detule commented 1 week ago

Hi @metanoid:

I don't have a Netezza instance around so shooting from the hip a bit. Do you have the ability to test a development branch? If so, can you see if this version performs better with your mini example?

Thanks

metanoid commented 1 week ago

Hi,

Thanks for trying! I think something is missing from the commit. See output message:

devtools::load_all(".")

#>ℹ Loading odbc
#>Error in add_classes_to_exports(ns = nsenv, package = package, exports = exports,  : 
#>  in package ‘odbc’ classes NetezzaSQL were specified for export but not defined
#>In addition: Warning message:
#>package ‘testthat’ was built under R version 4.3.3
detule commented 1 week ago

Sorry about that @metanoid. Should be better now, hopefully. Let me know what you find.

Thanks again for testing.

metanoid commented 1 week ago

It works!

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "Netezza")
test <- paste0(rep("a", 1000), collapse = "")
inferred_type <- dbDataType(con, test)

#"varchar(1000)"

I'd be interested to hear why this works?

Thanks!

Closed by https://github.com/detule/odbc/commit/7b2dd4be0aebf2e8881eee5b02f07c8e2b6a786e