First argument is not an open RODBC channel
Solution 1
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()
.
Solution 2
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)
Victor Galuppo
Updated on June 07, 2022Comments
-
Victor Galuppo almost 2 years
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 ""