代码之家 › 专栏 › 技术社区 › Willem

如何更改数据库连接区域设置?

r-dbi locale odbc r

1

Willem · 技术社区 · 8 年前

在R中使用DBI包时,我在更改“locale”时遇到问题。更具体地说,我的数据库包含诸如“,¨,…”之类的字符DBI无法正确读取它们。

这应该是一个可复制的示例:

sql <- "select * from myDatabase.dbo.myTable"
# Where myTable contains any of the difficult characters

# Try with DBI
library(odbc)
library(DBI)

conDBI <- dbConnect(
  odbc::odbc(),
  dsn   = "myDsn",
  UID   = myLogin,
  PWD   = myPassword,
  Port  = 1433
)

table_DBI <- dbGetQuery(conDBI, sql)

# Try with RODBC
library(RODBC)

conRODBC <- odbcConnect(
  "myDsn",
  uid = myLogin,
  pwd = myPassword
)

table_RODBC <- sqlQuery(conRODBC, sql)

如果与此相关,以下是我的会话信息:

> sessionInfo()
R version 3.4.0 Patched (2017-06-02 r72765)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=Dutch_Belgium.1252  LC_CTYPE=Dutch_Belgium.1252    LC_MONETARY=Dutch_Belgium.1252 LC_NUMERIC=C                  
[5] LC_TIME=Dutch_Belgium.1252    

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

other attached packages:
[1] RODBC_1.3-15 DBI_0.6-1    odbc_1.1.1  

loaded via a namespace (and not attached):
[1] bit_1.1-12     compiler_3.4.0 hms_0.3        tibble_1.3.3   Rcpp_0.12.11   bit64_0.9-7    blob_1.1.0     rlang_0.1.1

1 回复 | 直到 8 年前

1

0

James Thomas Durant 8 年前

您是否尝试过在dbConnect函数中使用编码参数?它应该是数据库上使用的文本编码。如果数据库与设置为“”的本地编码相同,请参阅iconvlist()以获取系统上可用编码的完整列表。注意,字符串始终返回UTF-8编码

您可以尝试:

conDBI <- dbConnect( 
odbc::odbc(), 
dsn = "myDsn", 
UID = myLogin, 
PWD = myPassword, 
Port = 1433, 
encoding = "latin1" )