Search code examples
rrstudiomultiple-databasesdsn

R: Connecting to multiple databases from R Studio

For the current project I am working, I need to pull data from 2 different databases - SQL Server and Teradata. I am using R-Studio for compilation of my queries and R-scripts

I am using the following packages: RODBC, RJDBC, rJava,

The problem I am facing is: The SQL Server is a 64 bit ODBC connection and Teradata is a 32 bit ODBC connection. If I install 32 bit version of R, I am not able to connect to SQL Server and if I install 64 bit version of R, RJDBC and rJava are not compatible and I am not able to connect to Teradata.

Any help on how I could connect to both the data bases simultaneously is greatly appreciated!!!

R version: 3.1.1 R-Studio version: 0.98.1074

Thanks!

EDIT

I am able to switch between architectures from Tools-> Global Options in R-Studio.

Global Options

But to take effect, it is asking me to restart R. By restarting R, I loosing all my instances and objects that were loaded previously in different architecture. Any workaround?

Solution

  • A quick and dirty method would be to start multiple R sessions of the regular R GUI one 64-bit and one 32-bit (or make RStudio one version and open the other in the regular R GUI), then pass objects and instances using save and load:

    Tested on 32 and 64 bit versions of R 3.1.1

    On 64-bit R

    > data(iris)
> foo.df <- iris[,1:3]
> save(list = ls(all = TRUE), file = ".RData64bit")

    On 32-bit R

    > local({
+    load(".RData64bit") # load the image
+    ls() # list the loaded objects
+ })
[1] "foo.df"           "iris"             "rLibraryLocation" "toInstall"       
[5] "wantedPackages"     
> load(".RData64bit") # execute the load in the R environment, objects now available
> summary(foo.df)
Sepal.Length    Sepal.Width     Petal.Length  
Min.   :4.300   Min.   :2.000   Min.   :1.000  
1st Qu.:5.100   1st Qu.:2.800   1st Qu.:1.600  
Median :5.800   Median :3.000   Median :4.350  
Mean   :5.843   Mean   :3.057   Mean   :3.758  
3rd Qu.:6.400   3rd Qu.:3.300   3rd Qu.:5.100  
Max.   :7.900   Max.   :4.400   Max.   :6.900