Database connection fails due to error with SQLAllocHandle

[lorenzbr]

Hello,

I updated R from MS R Open and R 4.0.1 to R 4.3.1. Since then, I have trouble connecting to DB2 databases via the 'odbc' package (Version 1.3.5). I get the following error (trying to translate from German):

Error: nanodbc/nanodbc.cpp:1135: IM004: [Microsoft][ODBC Driver Manager] error with SQLAllocHandle for driver on SQL_HANDLE_EN

This is the code I am using:
Example:

connection <- odbc::dbConnect(
    odbc::odbc(), 
    .connection_string = paste0('DRIVER=', "{IBM DB2 ODBC DRIVER}",
        ';UID=', "username", ';PWD=', "yourpw", ';HOSTNAME=', "hostname",
        ';DATABASE=', "db_name", ';PORT=', "port", ';COMMITONEOF=0;'),
    encoding = "latin1"
)

However, I am not sure if this is helpful as the code generally works with the old version of R. When I run odbc::odbcListDrivers() I get the exact same list of drivers as before. So, I assume the issue is not related to the current driver. The 'IBM DB2 ODBC DRIVER' (Version 11.5) is installed and the connection via ODBC using other tools works perfectly.

In case, it may be relevant. I have Rtools 4.3 installed, but had some issues earlier with Rtools.

Interestingly, the connection via JDBC works well. So, it think, it must be an issue with ODBC.

Any ideas?

[simonpcouch]

Thanks for the issue, @lorenzbr. Do you still see this issue with odbc 1.4.1?

[simonpcouch]

As this is likely a driver config issue and we haven't been able to get more information to reproduce, I'm going to go ahead and close.

[lorenzbr]

No improvement when updating the odbc package.
However, it seems that we had some encoding issues
With Sys.setenv(DB2CODEPAGE=1208), the database connections works. Not sure why UTF-8 is required.

[simonpcouch]

Thanks for the additional details!

[simonpcouch]

Similar SO question: https://stackoverflow.com/questions/74735676/db2-odbc-connection-doesnt-work-on-r-4-2

The IBM website promotes a DB2-specific RODBC wrapper with source here. We may be able to pick up some tricks from their source, though they don't reference that envvar in their source.

EDIT: more docs on that envvar

Do not set DB2CODEPAGE unless explicitly stated in DB2 documentation, or directed to by IBM DB2 service personnel. Setting DB2CODEPAGE otherwise may produce unexpected results. Normally, setting DB2CODEPAGE is not required because DB2 derives the code page information from the operating system, and converts the data between the application and database code pages automatically.

[simonpcouch]

R 4.2.0 made many changes to encodings for Windows, and from the looks of your issue you're on Windows as well. Possibly the culprit.