There seem to be plenty of similar issues and answers to them, yet so far haven't found a solution that would work for me.
Issue in short: special characters (scandic letters, ohm-symbol, Celcius degree-symbol, etc.) get all scrambled up when application is run on Linux Shiny server. Data shown on Shiny Dashboard is queried from Oracle database. The column name in these examples is "NAME" and type is VARCHAR2. When I run similar code on Linux server in R or on my local Windows RStudio, all characters look fine.
What I've tried so far: Characters started to look fine in Linux R after placing NLS_LANG to NLS_LANG=AMERICAN_AMERICA.AL32UTF8 in /etc/environment. I figured these are correct NLS_LANG settings by running SELECT * FROM V$NLS_PARAMETERS and SELECT * from NLS_SESSION_PARAMETERS in Linux's R. Though this didn't fix the issue on the Shiny Server side.
Also I've played around with the dbConnect encoding-parameter, with no luck.
Somewhat reproducible example: (sorry I can't gain access to my Oracle server ;-) )
library(ROracle)
ORAdrv <- dbDriver("Oracle", unicode_as_utf8 = TRUE, ora.attributes = TRUE) #doesn't matter if I have these two latter attributes or not
ORAconnect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=xxxx))",
"(CONNECT_DATA=(SID=...)))", sep = ""
)
query2 <- ("select NAME, DATA_FIELD from TABLE where DATA_FIELD in ('ID7018789', 'ID7025838', 'ID7021380')")
ORAcon <- dbConnect(ORAdrv, username = "...", password = "...", dbname = ORAconnect.string, encoding = "UTF-8") #doesn't matter if encoding is defined or not
res <- dbSendQuery(ORAcon, query2, 'set character set "utf8"') #doesn't matter if the last attribute is defined or not
df <- fetch(res)
dbDisconnect(ORAcon)
print(df)
What the end result looks like:
If I run the code in Linux R, the result is what is expected (Ohm, Celcius symbols and scandic characters look good):
If I run the same code and render the dataframe as datatable on ShinyServer app, the result is like this: (Ohm and Celcius symbols are replaced with question mark, scandic characters äö -> ao)
Any help on getting the encoding correctly on Shiny Server application side is highly appreciated =)
I was able to solve it finally. If someone else is struggling with this, cast the column to nvarchar already in the query.
in my case,
to_nchar(NAME)did the trick.https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions187.htm