在 R 中从 SQL Server 2016 中获取带有日语文本Microsoft nvarchar 列将返回"

Question

在 R 中获取nvarcharMicrosoft SQL Server 2016 中的日语文本列返回"????"。

事件试图在数据库连接中传递编码 = "UTF-8"，但没有运气。

任何指示将不胜感激，谢谢

require(odbc)
# This function takes a string vector and turns it into a list of raw UTF-16LE bytes. 
# These will be needed to load into SQL Server
convertToUTF16 <- function(s){
lapply(s, function(x) unlist(iconv(x,from="UTF-8",to="UTF- 
16LE",toRaw=TRUE)))
}

# our example data
inputData <- data.frame(ID = c(1,2,3), Char = c("My", "ウェブサイトはダウンしている、もしくはフリーズしています。" ,"Data"))
inputData
# we adjust the column with the UTF-8 strings to instead 
# be a list column of UTF-16LE bytes
inputData$Char <- convertToUTF16(inputData$Char)
inputData
#creat a connection to a sql table
con <- dbConnect(odbc(),
Driver = "SQL Server",
Server = "MSSQL20161",
Database = "MYSCHEMA",
UID = "USER",
PWD = "PASSWORD")
# mention field.types as nvarchar and create/write the table to the database 
dbWriteTable(conn = con,
name = "Example",
value = inputData,
append=T,
field.types = c(Char = "NVARCHAR(MAX)")
)
#reading data from mssql table again
readquery="SELECT * FROM Example"
outputdata=sqldf(readquery,conn = con)
outputdata
ID                          Char
1  1                            My
2  2 ?????????????????????????????
3  3                          Data

为了完整起见，这是我的会话信息：

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1
Matrix products: default
locale:
LC_COLLATE=English_United States.1252
LC_CTYPE=English_United States.1252 
LC_MONETARY=English_United States.1252
LC_NUMERIC=C 
LC_TIME=English_United States.1252    
attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     
other attached packages:
[1] odbc_1.1.6    sqldf_0.4-11  RSQLite_2.1.1 gsubfn_0.7    proto_1.0.0  
loaded via a namespace (and not attached):
[1] Rcpp_0.12.17    digest_0.6.15   chron_2.3-52    DBI_1.0.0       rlang_0.2.1     blob_1.1.1      tools_3.5.0     bit64_0.9-7    
[9] bit_1.1-14      hms_0.4.2       compiler_3.5.0  pkgconfig_2.0.1 tcltk_3.5.0     memoise_1.1.0 

Answer 1

能够通过将数据类型从NVARCHAR(MAX(更改为NVARCHAR(4000(来解决问题