DBI connect using sid, host, port

I have been using DBI package to connect to an oracle database (instant client 12.2) using the following

con <- DBI::dbConnect(odbc::odbc(), dsn="name",uid="user",pass="pass"))

my odbc.ini file has an entry like:

[ODBC 32 bit Data Sources]
serverName=Oracle in instantclient_12_2 (32 bit)
[serverName]
Driver32=c:\instantclient_12_2\SQORA32.dll

Everything is great! However now i want to try connecting a different way using the sid, host, port, something like:

channel <- DBI::dbConnect(odbc::odbc(),
                          driver="Oracle in instantclient_12_2",
                          Host=server.name,
                          Port=portNum,
                          SVC=sid,
                          uid="user",
                          pwd="pass")

However i get an error
Error: nanodbc/nanodbc.cpp:1021: IM006: [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

I have read rstudio help on oracle and connecting to a database and the DBI::dbConnect help but i can't seem to figure out what i am doing wrong.

Any advice welcomed

Hi, it looks like instantclient_12_2 may have different names for each entry, such as serverName instead of Host. If that's the case, I'd suggest to setup the connection using the Windows ODBC GUI and then just calling that new DSN from your code.

Thanks Edgar. Appreciate the response.

Yes, i have my machine set up this way, and everything works fine, but that really isn't the problem/question.

i want to connect using the syntax above, for reasons i won't trouble you with (mostly related to not having permissions to my machine) so i want to understand what i need to change in the dbConnect function to make this work.

Oh ok, my guess would be to find out what the driver uses as the parameter names for the connection. My suspicion is that it uses alternative names for Host, SVC, uid, pwd, etc. I'd recommend a search of generic connections, as in outside of R, using the instantclient driver, and see if anyone has posted their connection string that tells you what each parameter is called

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.