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

[Kacee Saxena]

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 = "jdbc:oracle: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]

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]

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]

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]

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 = "jdbc:oracle: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]

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 = "jdbc:oracle: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]

What id your jdbc driver version?

[Kacee Saxena]

I realized that my system is not accepting

String connURL = "jdbc:oracle:thin:@host:1521:sid";
String connURL = "jdbc:oracle:thin:@sid";

format. Its only working for

String connURL = "jdbc:oracle: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]

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]

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]

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)