We recently got a load of trouble with our ODBC-connection to DB2/AS400 when we installed the gdata-package. After that we have done a nuke of all R-related files and started over but we still have a problem with the IBM DB2 connection. If the .Rprofile loads without error our DBS-odbc connection blanks (works fine outside of R). If I write something in the .Rprofile that leads to an error the ODBC-connection to DB2/AS400 works fine in both DBI/odbc and the older RODBC. DB2/AS400 has underlying CCSID = 278.
EDIT: I can establish the connection, but the object appears empty. For DBI I have added the CCSID = 1252 and gotten it to work. But no such luck yet for RODBC (that we're still using even if we plan to leave it)
What in the initializing process might go wrong that it only works when I insert an error in the .Rprofile? And how can I specify encoding in RODBC so that it works?
EDIT2: The problems are in R, since the ODBC-connection works in all other domains
DBI/odbc - do work:
library(DBI)
library(odbc)
con3 <- dbConnect(odbc::odbc(), "mydb", uid= "myuser", pwd = "mypwd",
CCSID = 1252)
Rodbc - don't work:
con3 <- odbcConnect(dsn=mydb,uid= myuser,mypwd,
DBMSencoding = "1252")
Session-info:
version 4.2.0 Patched (2022-05-12 r82348 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)
Matrix products: default
locale:
[1] LC_COLLATE=Swedish_Finland.1252 LC_CTYPE=Swedish_Finland.1252 LC_MONETARY=Swedish_Finland.1252
[4] LC_NUMERIC=C LC_TIME=Swedish_Finland.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] odbc_1.3.3 DBI_1.1.3
loaded via a namespace (and not attached):
[1] bit_4.0.4 compiler_4.2.0 ellipsis_0.3.2 cli_3.4.1 hms_1.1.2 tools_4.2.0
[7] Rcpp_1.0.9 bit64_4.0.5 vctrs_0.4.2 blob_1.2.3 lifecycle_1.0.3 pkgconfig_2.0.3
[13] rlang_1.0.6
The answer for DBI/odbc is to set the CCSID = 1252 and for RODBC to set Sys.setenv(NLS_LANG="LATIN1")
It makes the .Rprofile loading very slow but it works so there's that atleast.