Load environment using source R

Question

decided to divide my code into different scripts and run them through a "main" script using source.

One of the scripts is a "Settings.R" environment containing database connection details and some other settings.

When I try to load the script using source('Settings.R') it finds the environment and I can access the variables in the console. But when I try to establish a connection with the database it fails.

If I however run the Settings.R script manually and then try to establish a connection in the other script it works.

# Settings script:
settings <- as.environment(list())
settings$dbConnection <- 'Driver={SQL Server};Server=SERVER INFO;Database=DATABASE;Uid=USERNAME;Pwd=PASSWORD';

# The calling script
source('Settings.R')
dbConnection <- odbcDriverConnect(settings$dbConnection)
# DO STUFF
close(dbConnection);

Might have something to do with with the settings environment not being globally available.

I tried changing the "local" variable to TRUE/FALSE and that did not help

local: TRUE, FALSE or an environment, determining where the parsed expressions are evaluated. FALSE (the default) corresponds to the user's workspace (the global environment) and TRUE to the environment from which source is called.

I get the following error message (p.s a little swedish text)

Warning messages: 1: In odbcDriverConnect(settings$dbConnection) :

[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]Antingen finns inte SQL Server eller så nekas åtkomst. 2: In odbcDriverConnect(settings$dbConnection) :

[RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). 3: In odbcDriverConnect(settings$dbConnection) : ODBC connection failed >

Any ideas?

Try putting source('Settings.R') in your Rprofile.site. It will be the first thing R opens and will be in the environment. — Pierre Lapointe
@PLapointe that did not help :/ If it would have worked and I wanted to run the script on a another machine, wouldn't I have to configure that it in the same way before I can run the script? I'll edit the post with some more code. — Khenrix
@Khenrix It should work, by default, the source() will evaluate all the things in the global environment and it should be the same to writing all the code in same file. Does the problem still exist if you clean up the environment, restart R session and set the working directory to source file location? What is the error message? — Consistency
@Consistency Yes, I tried cleaning the environment. Did not help. I edited the post with th error message. I tried creating a string with the connection details and in the calling script and that works. When I check the parent of the settings environment I get : parent.env(settings) <environment: R_EmptyEnv> This might be the problem? The settings file was autogenerated for me, so I thought it would be correct. — Khenrix

CSJCampbell CSJCampbell · Accepted Answer · 2018-08-22T00:04:36

Apologies that this response is not timely! But I have come across a similar issue, and so for posterity I have a likely solution. You are running this code on Linux I suspect. The dollar $ symbol is a special character and needs to be escaped in command line strings.

For a script 'env1.R':

e1 <- new.env()
e1$x <- 1

Interactively from R:

source('env1.R');e1$x
# [1] 1

From Rscript:

Rscript -e "source('env1.R');e1$x"
# 
# <environment: 0x33ca1b0>
Rscript -e "source('env1.R');e1\$x"
# [1] 1

The error from the database connection was caused by passing an environment, rather than a connection string. Hopefully the Swedish message supports this theory!

Load environment using source R

1 Answers