wood burning stoves 2.0*
The moose likes JDBC and the fly likes Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution)" Watch "Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution)" New topic
Author

Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution)

Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
Dear Friends,
I want to write a java code that connects with Oracle database using host,pid,address information from tnsnames.ora file using jdbc thin driver. I dont want to hard code any of these values accept SID.

As per my findings,JDBC thin driver doesnt reads these values from tnsnames.ora file.The usual

Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbcracle:thin:@host:port:SID";
Connection conn = DriverManager.getConnection(url, userName, pwd);

doesnt work for me since I have to get host,port values at runtime.

Also as per my understanding OCI driver should be used if tnsnames.ora file needs to be referred by program. I dont want to use OCI since it needs extra installation stuff. I want to make connection with JDBC thin driver only.

So I thought of using OracleDatasource contained in package : oracle.jdbc.pool

I want to know is OracleDatasource is the best way to make connection with Oracle database when tnsnames.ora file needs to be used in my case?

Please reply ASAP as its very urgent for me

Thanks in advance
Kiran Joshi
Ranch Hand

Joined: Sep 04, 2005
Posts: 54
is it mandatory for you to get the database properties from tnsnames.ora only?
how about configuring the db details in a properties files & continuing with thin driver only?
Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
Ya..thats the requirement i am having. Earlier code was reading these values from a property file and that property file was required to update manually everytime there were some changes in host values. To get rid off of that manual updation, I decided to use tnsnames.ora file directly.

So it is mandatory for me to use tnsnames.ora file only.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

This can solve your issue, without having to parse the tnsnames.ora yourself:
As of the 10.2.x JDBC driver we now allow you to connect to your database using a tnsnames.ora entry from JDBC THIN.

OCUP UML fundamental and ITIL foundation
youtube channel
Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
For a given entry like below in tnsnames.ora file,

MYSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd-qqq)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = abch-uuu)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = abcs-ppp)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = abc.prod.com)
)
)

and code :


private Connection getConnection(Properties properties) throws IOException, ClassNotFoundException, SQLException
{
Connection conn = null ;
try
{
System.setProperty("oracle.net.tns_admin", "/opt/oracle/sol-9.2.0/network/admin");
Class.forName("oracle.jdbc.OracleDriver");
System.out.println("after for name");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
System.out.println("Reg driver");
String connURL = "jdbcracle:thin:@mysid";
String userName = "abc";
conn = DriverManager.getConnection(connURL,userName,userName);
System.out.println("connection successful");
return conn;
}finally
{
System.out.println("failed in csilookup");
}
}


Its resulting in

after for name
Reg driver
failed in csilookup
java.sql.SQLException: Io exception: SO Exception was generated

Does DriverManager.getConnection() is able to resolve connURL formed above ?
Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
For a given entry like below in tnsnames.ora file,

MYSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd-qqq)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = abch-uuu)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = abcs-ppp)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = abc.prod.com)
)
)

and code :


private Connection getConnection(Properties properties) throws IOException, ClassNotFoundException, SQLException
{
Connection conn = null ;
try
{
System.setProperty("oracle.net.tns_admin", "/opt/oracle/sol-9.2.0/network/admin");
Class.forName("oracle.jdbc.OracleDriver");
System.out.println("after for name");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
System.out.println("Reg driver");
String connURL = "jdbcracle:thin:@mysid";
String userName = "abc";
conn = DriverManager.getConnection(connURL,userName,userName);
System.out.println("connection successful");
return conn;
}finally
{
System.out.println("failed in getConn");
}
}


Its resulting in

after for name
Reg driver
failed in csilookup
java.sql.SQLException: Io exception: SO Exception was generated

Does DriverManager.getConnection() is able to resolve connURL formed above ?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

What id your jdbc driver version?
Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
I realized that my system is not accepting

String connURL = "jdbcracle:thin:@host:1521:sid";
String connURL = "jdbcracle:thin:@sid";

format. Its only working for

String connURL = "jdbcracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Host_name)(PORT=1521)))(CONNECT_DATA=(SID=service_name)(SERVER=DEDICATED)))", "username", password");

and in above format, host , pid values have to be made available.(cant read at runtime)

So I thought of dropping the option of jdbc thin driver. I am using OCI driver instead. I was able to establish database connection using OCI reading values form tnsnames.ora.

Thanks for all the help
Kacee Saxena
Ranch Hand

Joined: Mar 04, 2008
Posts: 54
For time being, my problem is solved using OCI driver. But before closing this thread, I would like to know if this was the best option I have implemented.

Can we conclude that JDBC thin driver can not read from tnsnames.ora file when none of the connURL format mentioned above works ?
Senthil Kumar Somasundaram
Greenhorn

Joined: Jan 10, 2007
Posts: 2
Found an article about "JDBC Thin connection using tnsnames.ora entry" which worked for me.

http://theblasfrompas.blogspot.com/2008/04/jdbc-thin-connection-using-tnsnamesora.html

Thanks,
Senthil
Naveen Sharma
Ranch Hand

Joined: Mar 23, 2001
Posts: 65
I tried as explained in url http://theblasfrompas.blogspot.com/2008/04/jdbc-thin-connection-using-tnsnamesora.html

Still facing the Exception , I am surprised how come you overcome this, May be I have to switch to some other driver too

Exception in thread "main" java.sql.SQLException: Io exception: SO Exception was generate
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:404)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution)
 
Similar Threads
Multi datasource in websphere
read from tnsnames.ora file
jdbc driver
JDBC code for oracle RAC
Want to read values from tnsnames.ora file with JDBC thin driver (Oracle Datasource is solution)