0

I have 64 bit R, version 3.6.0, installed on my windows 10 machine.

I use the following code to establish a connection to the local MySQL database on my machine

library(DBI)
con <- dbConnect( odbc::odbc(),"my_dsn")

where my_dsn is the 64-bit System DSN to the MySQL database defined within the ODBC Data Source Administrator.

Executing this code within a fresh R Studio or R GUI session works and provides the expected connection object. So I save the code to an R script at C:/some_folder_location/my_script.R.

Now I wish to call the script from a batch file. The batch file contains the following code:

cd /d C:\some_folder\
"C:\Program Files\R\R-3.6.0\bin\i386\Rscript.exe"  my_script.R
pause

When I execute the batch file I get the following error:

Error: nanodbc/nanodbc.cpp:950: IM014: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Execution halted

So my question is, why does this code suddenly not work when executed by a batch file? The error message seems to be saying there's an issue with the DSN, but we know that isn't the case based on the fact that the script runs without issue from the console directly.

Rookatu
  • 1,487
  • 3
  • 21
  • 50

1 Answers1

0

Usually that error occurs with mismatch of a 32/64-bit driver and 32/64-bit platform. You may have two versions of R installed (i.e., two different Rscript.exe) by platform type (32-bit vs 64-bit) further complicated by versions (3.2 vs 3.5). For example:

  • Your Rscript.exe indicates a 32-bit R version with subfolder i386 which is not compatible with a 64-bit DSN.
  • Your RStudio and RGui may be running a 64-bit R version compatible with your DSN. Check with R.home(). Is this the same as batch file folder? If not, use Rscript.exe in bin of this directory.

To check available data sources in either R GUI or batch tool, call the following and compare results:

library(odbc) 

odbcListDataSources()    # SEE IF DSN SHOWS
odbcListDrivers()        # SEE IF DSN USES AVAILABLE DRIVER
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks for the response! So `R.home()` returned "C:/PROGRA~1/R/R-36~1.0" from both the RStudio console and the batch file output (when added to the called script), but the DSN list was different (too long to paste here), with the targeted DSN missing. One question I have is: if the Rscript.exe I'm calling indicates a 32-bit version, and this version is the same as what is returned by `R.home()` in the RStudio console, *and* the code works in RStudio console, what is allowing it to work in that environment? Based on your helpful hints I would expect it to fail in RStudio... – Rookatu Nov 21 '19 at 17:53
  • Inside the bin folder of `R.home` are there multiple subfolder other ithan `i386`? Also, does DSN show up in RStudio results? You mention different lists. Check what platform you run with: `Sys.info()[["machine"]] `. – Parfait Nov 21 '19 at 23:31