6

I want to connect to Oracle (11g) using unixODBC. I want the connection string ONLY to specify all the database/data-source information, and the ODBC INI files (/etc/odbc.ini and /etc/odbcinst.ini) to contain only generic driver info like where to find the '.so' etc. i.e. I want them to be static. I want to do this so that I can get the connection string from the user externally, and connect to the datasource without having to modify any of the INI files or anything other on-disk configuration.

All the examples I saw make me define the server information in /etc/odbc.ini like this:

[myDSNname]
Driver = OracleODBC-11g
DSN = OracleODBC-11g
ServerName = //xxx.xxx.xxx.xxx:1521/SID_NAME
UserID = my_user
Password = my_pass

Now, I figured out how to get the UserID and Password out into the connection string by adding "UID=my_user;PWD=my_pass" to the connection string, and I removed them from odbc.ini. My joy quickly passed when I couldn't find the way to get ServerName out of odbc.ini and into the connection string as well. So how do I do this? And is there a way to get rid of the DSN in odbc.ini altogether, and have just the Driver from odbcinst.ini in the connection string, like we are doing for MySQL?

Edit: Trying any of the connection strings from www.connectionstrings.com that don't seem to require TNS or a DSN, incliuding EZCONNECT variants, all give me:

[HY000] [unixODBC][Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified
    (12162) (SQLDriverConnectW)
e.dan
  • 7,275
  • 1
  • 26
  • 29
  • http://www.connectionstrings.com/oracle#p17 – Brian Jun 19 '12 at 09:41
  • @Brian - nope, I can't get any of those to work. I always get: `[HY000] [unixODBC][Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified` – e.dan Jun 19 '12 at 09:51
  • Don't use a tns names variant and/or make sure you have an entry in your tns names file. – Brian Jun 19 '12 at 09:53
  • @Brian: I am sure it was obvious but I'm not an expert in oracle or TNS or any of this stuff. I assume you mean the variants that have a bajillion parentheses in them, which seemed to me to be the ones that don't rely on some other TNS or other configuration file to contain the actual server name/IP etc? I tried those (using `Data Source` or `Server` or `CONNECTSTRING` in the connection string) to no avail. I always get the error I pasted above... is there any way to debug the connection strings and see what the driver is doing with it? Or list all the directives it understands? – e.dan Jun 19 '12 at 10:03
  • @Brian -and I was hoping the "Easy connect" variants would work too but they also give me the same thing. Could that be due to a problem on the server (I need to enable EZCONNECT maybe?) or would I get a different error, and not one that doesn't look so much like a client-side problem... ? – e.dan Jun 19 '12 at 10:08
  • Are you able to install an Oracle client? There is a small download from Oracle called "instant client". Or do already have an oracle client installed? If yes there is a folder within the oracle home named 'network' that has the tnsnames.ora text file. – Brian Jun 19 '12 at 10:31
  • http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html – Brian Jun 19 '12 at 10:33
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12736/discussion-between-e-dan-and-brian) – e.dan Jun 19 '12 at 11:13

1 Answers1

7

just use Dbq=address:1521/orcl ,and you will get what you want

jack
  • 71
  • 1
  • 2