5

I am connecting to an Oracle database via DNS (set up the connection on the tnsnames.ora with the name "database").

I am able to succesfully run the following code:

con <- DBI::dbConnect(odbc::odbc(), 
                  "database",
                  UID = "user",
                  PWD = "password",
                  trusted_connection = TRUE)

I am also able to succesfully list all the tables in the database via:

list <- dbListTables(con)
View(list)

However, when I run:

results <- sqlQuery(channel = con, query = "select * from myschemaname.table")

I get the error: 

Error in sqlQuery(channel = con, query = "select * from myschemaname.table") : 
          first argument is not an open RODBC channel

I have owner privileges and I am also able to update Excel Spreadsheets connected to this database via ODBC. If it's of any use, here's the output of str(con)

str(con)
Formal class 'Oracle' [package ".GlobalEnv"] with 4 slots
  ..@ ptr     :<externalptr> 
  ..@ quote   : chr "\""
  ..@ info    :List of 13
  .. ..$ dbname               : chr ""
  .. ..$ dbms.name            : chr "Oracle"
  .. ..$ db.version           : chr "11.02.0040"
  .. ..$ username             : chr "user"
  .. ..$ host                 : chr ""
  .. ..$ port                 : chr ""
  .. ..$ sourcename           : chr "database"
  .. ..$ servername           : chr "database"
  .. ..$ drivername           : chr "SQORA32.DLL"
  .. ..$ odbc.version         : chr "03.80.0000"
  .. ..$ driver.version       : chr "11.02.0001"
  .. ..$ odbcdriver.version   : chr "03.52"
  .. ..$ supports.transactions: logi TRUE
  .. ..- attr(*, "class")= chr [1:3] "Oracle" "driver_info" "list"
  ..@ encoding: chr ""
Victor Galuppo
  • 187
  • 2
  • 2
  • 8
  • 2
    `sqlQuery` is from the `RODBC` package. Maybe you need the `ODBC::`-equivalent of `RODBC::sqlQuery()` to use your `DBI::dbConnect()` connection? – piptoma Oct 09 '18 at 13:09
  • 1
    That's it, thank you so much! I'm now successfully using con <- odbcConnect("database", uid = "user", pwd = "password") to use RODBC functions. I'm new to stackoverflow, so do I have to write a final answer, or is it up to you? =) – Victor Galuppo Oct 09 '18 at 13:59
  • I'll formulate an answer as soon I have time :). – piptoma Oct 10 '18 at 07:16

3 Answers3

9

You are using RODBC::sqlQuery() for a connection created with DBI::dbConnect(). Either use DBI::dbGetQuery() with DBI::dbConnect() or create a connection with RODBC::odbcConnect(), and use RODBC::sqlQuery().

piptoma
  • 754
  • 1
  • 8
  • 19
1

Another context, but the same error: If the table of your DB consists of numerics, you first of all have to load an object (nameofyourtable) of class xts:

    >library(RODBC)
    >library(DMwR)
    >library(xts)
    >data(nameofyourtable)
Christian
  • 11
  • 1
0

So I had something like this connecting to a SQL Server database. In the end I had to change the Trusted_Connection=TRUE to Trusted_Connection=Yes...

Jelson
  • 1
  • 1