• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Kacee Saxena
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kacee Saxena
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What id your jdbc driver version?
 
Kacee Saxena
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic