switch from RODBC to DBI/odbc for connections to the local NASIS database

[dylanbeaudette]

Tiny example.

library(DBI)
library(odbc)
con <- dbConnect(odbc(), ...)

This started as #145.

[brownag]

Reprex for dealing with issues related to long (text) columns, truncation to 255 characters, and possible stumbling blocks in conversion to DBI. I thought that with the most recent {odbc} that dbGetQuery would not error, regardless of long ccolumn order, but apparently not.

# reprex for issues with column ordering and ODBC drivers

# RODBC truncates long columns to 255 characters
library(RODBC)
con2 <- soilDB:::.openNASISchannel()

res1 <- sqlQuery(con2, "SELECT * FROM calculation")
max(nchar(res1$calc)) # truncated to 255 characters
#> [1] 255
# manual casting of columns to ntext works, but not for simple queries like SELECT *
odbcClose(con2)

# compare DBI
library(DBI)
library(odbc) # driver to read MSSQL 
#> Warning: package 'odbc' was built under R version 4.0.3

con <- dbConnect(odbc::odbc(), 
                 DSN = "nasis_local", 
                 UID = "NasisSqlRO",
                 PWD = "nasisRe@d0n1y365")

# this produces an error; may be fixed with new odbc? 
#  see https://github.com/r-dbi/odbc/issues/309
dbGetQuery(con, "SELECT * FROM calculation")
#> Error in result_fetch(res@ptr, n): nanodbc/nanodbc.cpp:3011: 07009: [Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index
#> Warning in dbClearResult(rs): Result already cleared

# this produces same error
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
tbl(con, "calculation")
#> Error in result_fetch(res@ptr, n): nanodbc/nanodbc.cpp:3011: 07009: [Microsoft][SQL Server Native Client 11.0]Invalid Descriptor Index
#> Warning in dbClearResult(res): Result already cleared

# this works without error (manually identify and reorder long columns to end)
long_cols <- odbcConnectionColumns(con, "calculation") %>%
  filter(field.type == "varchar" & column_size == 0) %>%
  pull(name)

res2 <- tbl(con, "calculation") %>% 
  select(-all_of(long_cols), everything(), all_of(long_cols)) %>%
  collect()
max(nchar(res2$calc)) # proper length
#> [1] 3167991


dbDisconnect(con)

^{Created on 2020-11-03 by the reprex package (v0.3.0)}

[brownag]

This may be an interaction of the new {odbc}/nanodbc with the SQL Server Native Client 11.0. We should consider requesting the latest MS ODBC driver from IT for long-term stability of ODBC connections. Microsoft is several versions ahead of the version 11 we had to switch over because of changes to TLS policy.

The redistributable installer for Microsoft ODBC Driver 17 for SQL Server installs the client components, which are required during run time to take advantage of newer SQL Server features. It optionally installs the header files needed to develop an application that uses the ODBC API. Starting with version 17.4.2, the installer also includes and installs the Microsoft Active Directory Authentication Library (ADAL.dll).

Version 17.6.1 is the latest general availability (GA) version. If you have a previous version of Microsoft ODBC Driver 17 for SQL Server installed, installing 17.6.1 upgrades it to 17.6.1.

https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

[dylanbeaudette]

Thanks for looking into this--always more complicated than it would seem. I've been working towards adding explicit CASTing to ntext for all affected queries. There may be some remaining, but as far as I can tell most in soilDB have been fixed. I had to do similar work in the interpretation engine repo. This doesn't help folks who write ad hoc queries.