1

I've got multiple SQL-Queries (and R-calculations) distributed over several files, which should be executed using sqlQuery() from the RODBC package. Each file opens its own connection thoughodbcConnect(). The scripts are delivering the desired results as long as the queries are called within their according R-script. However, once I try to execute the scripts from another meta-file via source()-functions I get an error (invalid character) from the queries containing non-UTF-8 characters.

Now I tried to specify different encodings using

odbcConnect(DBMSencoding = "")
odbcConnect(DBMSencoding = "UTF-8")
odbcConnect(DBMSencoding = "UTF8")
odbcConnect(DBMSencoding = "ASCII")
odbcConnect(DBMSencoding = "ANSI")

which doesn't change the results.

Using the following command tells me that the enconding of the database (NLS_CHARACTERSET) is UTF8 (Oracle database).

SELECT *
FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET');

However, I can't make the queries containing non-UTF-8 characters work as I want them to.

Is the encoding the actual problem when trying to execute SQL-Queries in R-Scripts using source()? And are there any suggestions how to make it work?

Thank you in advance!

[EDIT] Here's a short reproducable example of my problem:

df <- sqlQuery(channel, paste(
  "
SELECT NR AS TÄST
FROM TEST.DATA
  ",
sep = ""))

This works within the R-script, but it gives me the following error when calling the script using source((paste(path, "script.R", sep=""), print.eval=TRUE, echo=TRUE):

"HY000 911 [Oracle][ODBC][Ora]ORA-00911: Invalid character\n"

Changing "TÄST" to "TEST" or even "TÜST" works, since "TÜST" is converted to "TÃŒST". Thus, I'm assuming that there's a problem with the encoding.

alex_555
  • 1,092
  • 1
  • 14
  • 27
  • how are you calling the sql query exactly (can you post the line(s) of code you have? Also, have you been able to get other queries to work through RODBC? If the answer is no, please try putting a `"` at the very beginning of the sql query and also one at the very end, then try it – stevec Jul 25 '18 at 13:44
  • Unfortunately, each query consists of nested queries making them quite large (~150 lines). I'll include a short reproducible example. Only executing the inner parts of a query (no non-UTF-8 characters) via `source()` works fine. As I said, the queries work when executed within their R-script or using `source()` as long as they don't contain any non-UTF-8 characters. There are `""` covering the SQL-statements, this isn't the problem - otherwise I wouldn't be able to execute the queries anyway. – alex_555 Jul 25 '18 at 14:05
  • *This works within the R-script* ... running inside RStudio? Try running a script at command line with `Rscript` which I suspect will fail as well since RStudio has encoding defaults set. – Parfait Jul 25 '18 at 14:33
  • Thank you @Parfait! I had a look at this post before and thought my issue was different, overlooking the solution due to its complexity. Now, I could solve my problem using the suggestion from the question `source(...,encoding="utf-8")`. Thanks! – alex_555 Jul 26 '18 at 14:08

0 Answers0