dbGetQuery() Returns Incorrect NULL Values When Using R ODBC Package on Linux

[ddl-chris-mutono]

Overview:
When connecting to a DB2 database using the R ODBC package on Linux, null values in a specific column are returned as unreadable characters instead of being correctly identified as NULL.

Problem Description:

• A specific column in the database contains null values.
• When querying the table using the R odbc package on Linux, null values are returned as unreadable characters (e.g., @oNjU) instead of NA.

The same query works correctly with:

• The Python pyodbc package on Linux.
• Both R odbc and Python pyodbc on Windows.

Reproducible Example:

library(tidyverse)
library(odbc)

Sys.setenv(CODEPAGE = "819") 
Sys.setenv(DB2CODEPAGE = "819")

connection <- odbc::dbConnect(odbc::odbc(),
 .connection_string = paste0('DRIVER=', "{IBM DB2 ODBC DRIVER}",
    ';UID=', Sys.getenv("USER"),
    ';PWD=', Sys.getenv("PASSWORD"),
    ';HOSTNAME=', "placeholder_hostname",
    ';DATABASE=', "placeholder_db",
    ';PORT=', "45000",
    ';COMMITONEOF=0;',
    ';NULLS=YES;'),
 encoding = "latin1")

df_test <- odbc::dbGetQuery(connection, "SELECT column_name FROM table_name WHERE column_name IS NULL FETCH FIRST 10 ROWS ONLY")

df_test %>% head()

Expected Behaviour:

Null values in the column should be represented as NA in the resulting R data frame.

Observed Behavior:

Null values are returned as unreadable characters, as shown below:

 column_name
1          @ONjU
2          @ONjU
3          @ONjU
...

Version info:

• R version: 4.3.2
• R ODBC version: 1.5.0
• ODBC driver: IBM Data Server Driver for ODBC and CLI (64-bit) 11.5.9