0

I have some issues while trying to combine my R script with PowerBI. I have some code that connects R with an Informix database via the RODBC package and I get data as dataframe with sqlquery.

The same code in PowerBI is not working unfortunately. A simple dataframe (e.g. iris) is loaded into BI, so the setup seems to be correct.

The R Code example is

library(tidyverse)
library(lubridate)
library(RODBC)
library(dplyr)
library(odbc)
library(DBI)
library(data.table)
 
conn <- odbcConnect("Server", uid = "xxx", pwd = "xxx")
labor <- sqlQuery(conn, paste("SELECT * FROM database"))

The BI error is as follows. Unfortunately in German but the codes should be the same I guess?

Details: "ADO.NET: R-Skriptfehler.
-- Attaching packages ------------------------------------------------------------------------------- tidyverse 1.3.0 --
v ggplot2 3.3.3     v purrr   0.3.4
v tibble  3.0.6     v dplyr   1.0.4
v tidyr   1.1.2     v stringr 1.4.0
v readr   1.4.0     v forcats 0.5.1
-- Conflicts ---------------------------------------------------------------------------------- tidyverse_conflicts() --
x dplyr::filter() masks stats::filter()
x dplyr::lag()    masks stats::lag()
 
Attache Paket: 'lubridate'

The following objects are masked from 'package:base':

date, intersect, setdiff, union

Attache Paket: 'data.table'

The following objects are masked from 'package:lubridate':

hour, isoweek, mday, minute, month, quarter, second, wday, week,
yday, year

The following objects are masked from 'package:dplyr':

between, first, last

The following object is masked from 'package:purrr':

    transpose
 
Warnmeldungen:
1: In RODBC::odbcDriverConnect("DSN=Xxx;UID=Xxx;PWD=Xxx") :
  [RODBC] FEHLER: Status IM014, Code 0, Nachricht [Microsoft][ODBC Driver Manager] Der angegebene DSN weist eine nicht übereinstimmende Architektur von Treiber und Anwendung auf.
2: In RODBC::odbcDriverConnect("DSN=Xxx;UID=Xxx;PWD=Xxx") :
  ODBC-Verbindung fehlgeschlagen
Fehler in sqlTables(conn) : erstes Argument ist kein offener RODBC-Kanal
Ausführung angehalten
"

I tried odbcDriverConnect with the the driver name as well, but also without success. Should I go further into the direction with odbcDriverConnect and try something else there? If anybody has an idea, I would be glad.

Thank you very much and best regards!

Robin Sage
  • 969
  • 1
  • 8
  • 24
mahlgut
  • 1
  • as this seems to be a connection problem, perhaps try to remove unnecessary packages from your example : `tidyverse`, `dplyr`, `lubridate`,`data.table` : the associated warnings probably don't give useful information to help solve the problem – Waldi Feb 23 '22 at 20:04
  • I need the packages later on, so I thought I just let them stay in. However, only with the RODBC package, I receive the error from the last part (RODBC odbcDriverConnect) – mahlgut Feb 24 '22 at 07:25
  • I just found some information that this might to have something to do with 32/64 Bit Software. My PowerBI runs on 32 bit like the odbc driver. R is on 64 though. Could this be the issue? The code runs on R without problem. – mahlgut Feb 24 '22 at 07:35
  • This could be linked because ODBC drivers on Windows can be installed in 32/64 bits version. Looks like `RODBC` doesn't find the Data Source Name (DSN) for the ODBC connection you try to use. Perhaps you defined it in 64 bits and it looks for 32 bits. – Waldi Feb 24 '22 at 07:38
  • Regarding unnecessary packages, I was thinking about [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) which is the most efficient way to get an answer – Waldi Feb 24 '22 at 07:41

0 Answers0