The RSQLServer Package Is Officially Retired

RSQLServer was officially archived on April 10, 2018. The author recommends using r-dbi/odbc as a replacement.

This package is archived as there is now an excellent, much better supported package odbc.

Switching to the odbc Package

As a developer who has been lurking on GitHub for years, I noticed the odbc package quite early on. Indeed, odbc is faster and lighter (no Java installation required), and is under active development. RSQLServer, on the other hand, had been somewhat lacking in maintenance. On top of that, it introduced some dplyr features that occasionally caused conflicts, so I had been itching to make the switch. Although I ran some comparisons and tests and didn’t find obvious issues, I was secretly worried about running into unexpected problems.

But since the switch was inevitable sooner or later, better sooner than later. I recall a classic statement from “The Pragmatic Programmer”:

Refactor Early, Refactor Often.

The Pitfall

Sure enough, it wasn’t long before I noticed some scripts producing odd results. After half a day of debugging, I finally found the problem: odbc does not pre-convert the SQL string encoding to match the database’s encoding, unlike RODBC or RJDBC. What made it especially frustrating was that I rarely use Chinese characters in SQL. The few Chinese characters that were there got converted to garbled text but didn’t throw an error – they just returned different results 😡.

That morning I filed a report on GitHub r-dbi/odbc#179. I thought forcibly converting the SQL to the database’s encoding should work (that’s what RODBC::odbcQuery() does), but given that I’m really not a database expert, I decided not to submit a PR and embarrass myself. I’ll leave it to the author Jim Hester to solve…

Wasted Time

In the world of computing, character encoding is truly a nightmare, especially on Windows (oh, and let’s not forget that other nightmare: timezones). Looking back on my years as a developer, I’ve wasted countless precious hours on these two issues. Here are some of the more memorable ones:

• Accidentally opened a GB2312-encoded file with UTF-8 encoding, edited it, and saved it – back when I didn’t know how to use version control;

• Reading a date column from a data.frame fetched from a database, then using as.Date() which immediately gives you a date that’s one day off – because the tz parameter in as.Date() defaults to "UTC";

• Getting a bunch of garbled text when reading from an Oracle database on Linux – you need to set Oracle’s language environment variable;

• Data stored on Windows turning into garbled text on Linux (the stored strings were in GB2312 encoding, while Linux can only display UTF-8 correctly);

• Writing Chinese in LaTeX, including getting Chinese bookmarks to display correctly in the PDF viewer – only XeLaTeX can save you;

• Displaying Chinese in ggplot2 plots within a PDF – you must set the relevant fonts in the plot to fonts available in the PDF, and the warnings seemingly never go away;

• I vaguely recall that gsub() from the base package (or maybe some other function) didn’t support UTF-8 encoded input (seems to be fixed now);

• roxygen2 and devtools support for Chinese on Windows – r-lib/roxygen2#532, r-lib/devtools#1378

• A bug introduced in data.table version 1.9.7 – see my previous post, which tormented me on and off for nearly two years;

• A bug introduced in R version 3.3.0, causing the match() function to return inconsistent results for different encodings – my very first blog post was about this;

• Database connections in R: some return UTF-8 encoded strings, others return strings in the native encoding – you can only find out by trying;

• Our company’s MS SQL Server stores data in GB2312. Certain data originating from UTF-8 sources turns into garbled text once stored;

• R generates timestamps without a timezone attribute by default (e.g., attr(Sys.time(), "tzone") returns NULL). When such a timestamp is saved and read on a different machine, you may run into problems if the two machines are in different timezones;

• Various data storage formats and their encoding support, e.g., wesm/feather#335, fstpackage/fst#114;

• Various htmlwidgets and their support for Chinese – these issues seem to be getting rarer nowadays;

• The ROracle package requires SQL to be in UTF-8 encoding;

• …

I imagine everyone has been tormented by these issues to some extent. But after all the struggling, how much of it was truly meaningful? The saddest part is that no matter how many pitfalls you’ve fallen into before, there are always more waiting ahead 😂.

(A little bragging about PRs I submitted to fix these issues)

• r-lib/roxygen2#532: roxygen2 uses the Encoding field in DESCRIPTION, but previously always used native encoding, which caused many problems on Windows

• rstudio/rmarkdown#841: Long ago, yaml didn’t handle UTF-8 very well, so rmarkdown had to make some internal changes to yaml reading. But they forgot that in addition to the final content, list names could also be UTF-8 encoded

• Rdatatable/data.table#2566, Rdatatable/data.table#3451, Rdatatable/data.table#3849: Fixed sorting, querying, and crash issues in data.table when dealing with non-ASCII characters – I spent quite a while digging into these, and data.table’s C code is pretty complex. I’m genuinely proud of getting these fixed, haha

• rstudio/plumber#312, rstudio/plumber#314: Enabled plumber to read UTF-8 source files and handle JSON containing UTF-8 content

• Rblp/Rblpapi#278: Rblpapi calls the Bloomberg API, but didn’t mark the returned results as UTF-8, leading to garbled text

• r-dbi/RSQLite#276: RSQLite didn’t mark returned column headers as UTF-8 encoded, resulting in garbled display

• openanalytics/containerproxy#15: ShinyProxy didn’t convert encoding from Java’s default UTF-16 to UTF-8 when sending user information to InfluxDB, causing garbled text when Chinese characters were involved

• Rdatatable/data.table#3850: data.table::setnames() couldn’t correctly rename columns with Chinese headers

• r-dbi/odbc#294: The odbc package forced the returned timezone to UTC, which caused confusion and inconvenience for users

• r-dbi/odbc#295: The odbc package returned date values that were one day less than what the database stored

• rstudio/rstudioapi#158: On Windows, rstudioapi displayed garbled text after selecting a file with Chinese characters in its path

• rstudio/htmltools#157: On Windows, rmarkdown failed to display subsequent htmlwidgets objects after encountering a string with emoji characters

• Rdatatable/data.table#4785: data.table::fwrite() can now write UTF-8 encoded CSV files by setting the encoding parameter

• ycphs/openxlsx#118: openxlsx is a powerful and user-friendly package for creating/modifying/reading Excel files. However, it would throw errors when comments or sheet names contained Chinese characters. I read through all the relevant R and C++ code and changed everything to use UTF-8 for input and output (since the xlsx format is UTF-8 encoded by nature). This should mostly solve the Chinese support issues once and for all (though there might still be a few edge cases I missed).

The Future

According to these two articles by Tomas Kalibera, starting from Windows 10 (versions after November 2019), applications are allowed to use UTF-8 encoding to interact with the operating system. Therefore, after switching R and related packages’ build tools to UCRT, most encoding issues on Windows could potentially be resolved at the root. The articles don’t explicitly lay out R’s future roadmap, but at least we can see a glimmer of hope.

• Windows/UTF-8 Build of R and CRAN Packages, Tomas Kalibera

• UTF-8 Support on Windows, Tomas Kalibera

• R-dev/WindowsBuilds/winutf8