Thursday, November 6, 2008

Check JDBC Connection String using java

Sometimes, I have faced a situation wherein the tnsping to the database is working but the JDBC connection fails. So, in order to check the JDBC connection string, one has to use a java program, which unfortunately isn't my forte.

Thankfully, my friend, Padmaraj, who is a SME on java gave me a small piece of code which works wonderfully well.

Note: Save the below java program as ReadCharacter.java, else the program won't work.


// Program to check the JDBC Connection string.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ReadCharacter {
public static void main(String[] args) {
Connection dbConnection;
Statement stmt;
String sqlStmt;

// For Oracle 9.2
String thin = "jdbc:oracle:thin:@[db_host]:[db_port]:[oracle_sid]";
String name = "[username]";
String pass = "[password]";
String table = "V$INSTANCE";
try{
pt("Getting Connection....");
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConnection = DriverManager.getConnection(thin, name, pass);
pl(" Got");
pt("Creating statment....");
sqlStmt = "SELECT instance_name, host_name from " + table;
stmt = dbConnection.createStatement();
pl(" Got");
pt("Excuting statment (" + sqlStmt + ")....");
ResultSet rs = stmt.executeQuery(sqlStmt);
pl(" Excuted");
while(rs.next()) {
String n = rs.getString("instance_name");
String a = rs.getString(2);
pl("Instance_Name = " + n + ", Host_Name = " + a );
}
pt("Closing Result set...");
rs.close();
pl(" Closed");
pt("Closed statment....");
stmt.close();
pl(" Closed");
pt("Closing Connection....");
dbConnection.close();
pl(" Closed");
} catch (Exception e) {
e.printStackTrace();
}
}
static void pt(String s) {
System.out.print(s);
}
static void pl(String s) {
System.out.println(s);

}
}


No comments: