CODEWITHSUNDEEP
rdatabaseoracle-databaserodbc
aaronmams
aaronmams

Reputation: 151

Trouble connecting to Oracle database using RODBC

I recently upgraded from Windows 7 to Windows 10 and had to reset some remote database connections. I had previously been connecting quite successfully to an Oracle database using the Oracle 11g client and RODBC.

    library(RODBC)
    channel<- 
    odbcConnect(dsn="myoracleDB",
    uid='myusername',
    pw='mypassword',
    believeNRows=FALSE)
    result<- sqlQuery(channel,"select * from schema_name.table_name")
    close(channel)

Since the Windows 10 upgrade, the above connection protocol no longer works. Specifically, I get the following error:

    channel<- 
    odbcConnect(dsn="myoracleDB",
    uid='myusername',
    pw='mypassword',
    believeNRows=FALSE)
    Warning messages:
    1: In RODBC::odbcDriverConnect("DSN=myoracleDB;UID=myusername;
    PWD=mypassword",:
    [RODBC] ERROR: state HY000, code 12170, message [Oracle][ODBC] 
    [Ora]ORA-12170: TNS:Connect timeout occurred
    2: In RODBC::odbcDriverConnect("DSN=myoracleDB;UID=myusername;
    PWD=mypassword",:ODBC connection failed

Two additional observations are relevant here:

  1. I use the Windows command line to execute tnsping myoracleDB which returns a successful connection to the database

  2. I can also use Oracle's SQL Developer Application to successfully connect to and query from the database.

So I feel confident that the Oracle Client and the ODBC Data Sources are set up correctly.

Interestingly, I AM able to connect to my database using the RODBC library if I use the following code:

    mycon = odbcDriverConnect("Driver={Oracle in OraClient11g_home1}; 
    Dbq=myoracleDB; Uid=myusername; Pwd=mypassword;",
    believeNRows=FALSE)

My question for the community is:

  1. This new connection protocol works (which I'm happy about). However, since I don't really understand why it works when the approach that worked before no longer works, I fear I may be ignoring some underlying problem that could really hurt me down the road.

I have found the following SO threads to be helpful, though neither really addresses my issue exactly:

Failure to connect to odbc database in R

Connect to ORACLE via R, using the info in sql developer

UPDATE: I have accessed the Windows ODBC 64 bit menu and verified that I do have a DSN called "myoracleDB" which is assigned to the "Oracle in OraClient11g_home1" driver. I have tested this connection and find that it works fine. I have also used the RODBC line:

    odbcDataSources()

in RStudio and found that the data source "myoracleDB" is recognized. However, when I try to execute:

    channel<- 
    odbcConnect(dsn="myoracleDB",
    uid='myusername',
    pw='mypassword',
    believeNRows=FALSE)

I still get the error:

"TNS: Connect timeout occurred ODBC connection failed"

Upvotes: 1

Views: 1714

Answers (1)

kfinity
kfinity

Reputation: 9091

If you check out the docs, DSN=myoracleDB tells RODBC to connect to the Windows DSN "myoracleDB", while Dbq=myoracleDB tells RODBC to connect to the TNSNAMES entry "myoracleDB". They're two different ways of resolving database names. tnsping and SQL Developer also both use TNSNAMES to resolve databases.

So I think your DSN probably got deleted when you reset things. You can test it by going to Control Panel > Administrative Tools > Data Sources (ODBC). If your database is there, you should be able to Configure it and click Test Connection to make sure it's working. Otherwise you can add it there, and your original configuration should work again.

Upvotes: 1

Related Questions