使用DBI和ODBC从FileMaker数据库取数据时的变音符号问题

问题背景 #

我正尝试从已弃用的RODBC切换至DBI和odbc包，以从配置为UTF-8编码的FileMaker数据库中获取数据。已配置unixodbc，使用isql可成功检索包含变音符号的数据：
执行isql -v mydb myuid mypwd后，运行select Contexte from Mobilier where ID_Objet = 1会返回正确的Dépôt（终端同样配置为UTF-8）。

DBI/odbc代码及异常结果 #

切换到R语言后出现编码问题，示例代码如下：

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(),
                   driver = "/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so",
                   database = "mydb", 
                   uid = "myuid", 
                   pwd = "mypwd",
                   host = "localhost")

val <- dbGetQuery(con, "select Contexte from Mobilier where ID_Objet = 1")

print(val)
# 输出：
#   Contexte
# 1 D\u008ep\u0099t

RODBC正常代码及结果 #

使用RODBC则一切正常：

library(RODBC)

con <- odbcConnect("mydb", uid = "myuid", pwd = "mypwd")

val <- sqlQuery(con, "select Contexte from Mobilier where ID_Objet = 1")

print(val)
# 输出：
#   Contexte
# 1    Dépôt

系统及R环境信息 #

sessionInfo()返回信息如下：

R version 4.3.3 (2024-02-29)
Platform: aarch64-apple-darwin20 (64-bit)
Running under: macOS Ventura 13.6.3

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib 
LAPACK: /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

time zone: Europe/Brussels
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RODBC_1.3-23

loaded via a namespace (and not attached):
[1] compiler_4.3.3    tools_4.3.3       rstudioapi_0.15.0

补充测试 #

根据建议，尝试用RODBC向数据库插入文本，再用DBI检索：

library(RODBC)
library(DBI)
library(odbc)

con1 <- RODBC::odbcConnect("mydb", uid = "myuid", pwd = "mypwd")
con2 <- DBI::dbConnect(odbc::odbc(),
                  driver = "/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so",
                  database = "mydb",
                  uid = "myuid",
                  pwd = "mypwd",
                  host = "localhost")

# 要通过RODBC插入的带变音符号的数据
insert <- data.frame("ID_Objet" = 5000,
                     "Contexte" = "Dépôt")

# 编码正确
Encoding(insert$Contexte)
# > [1] "UTF-8"

# 使用RODBC插入数据...
RODBC::sqlSave(con1, insert)

# ...并检索
x <- RODBC::sqlQuery(con1, "select * from insert")

# 变音符号正常但编码显示未知...
x$Contexte
# > [1] "Dépôt"
Encoding(x$Contexte)
# > [1] "unknown"

# 现在用DBI和odbc读取同一数据
y <- DBI::dbReadTable(con2, "insert")

# 变音符号丢失但检测到原编码
y$Contexte
# > [1] "D\u008ep\u0099t"
Encoding(y$Contexte)
# >[1] "UTF-8"

内容的提问来源于stack exchange，提问作者lodumont