How to change sid in program

[adeeb alexander]

Hi all.
I want to change the sid in the URL i.e(conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");. My requirement is that the user must be asked for the SID and the SID should be set. I can do that if i have one or two URL's. The problem is that i have used it many times the URL in different classes. Now what can i do to achieve my requirement.



Thanks and Regards
alexander

[Scott Selikoff]

If you're trying to change the underlying connection properties of an existing connection, you may need to establish a new one on the fly by building a new connection string.

[Jan Cumps]

adeeb alexander wrote:....The problem is that i have used it many times the URL in different classes. Now what can i do to achieve my requirement...

Fix that first.
Then, if your requirement is that the user has to select the database when your program starts, it should not be difficult to implement.

[adeeb alexander]

hi.
Sorry for late reply.

i have tried creating a different class for connection to database. I get few errors and couldn't solve. Little help required. The code is below

Conection.java

    
import java.sql.*;
public class Conection
{
public Connection conn;

public Conection()throws ClassNotFoundException,SQLException
{
	try
	{
Class.forName("oracle.jdbc.driver.OracleDriver");
Statement stmt = conn.createStatement();
System.out.println("OK :/");

conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE","scott","tiger");
}

catch(Exception ce)
{
	System.out.println(ce);
}
}

} 

exp.java

    
import java.sql.*;
	
//	Statement stmt;
public	exp()
{
try
{
	
Conection con = new Conection();
Statement stmt = con.createStatement();

System.out.println("ok");
ResultSet rset=stmt.executeQuery("select sysdate from dual");

while(rset.next())
{
System.out.println(rset.getString(1));
}
}
catch(Exception ce)
{
	System.out.println(ce);
}

}
public static void main(String[] args)throws ClassNotFoundException,SQLException
{

exp ex = new exp();

}
}

i am getting error at line "Statement stmt = con.createStatement();" the error is " cannot find symbol method con.createStatement". Please point at my mistake.


Thanks and Regards
alexander

[Paul Sturrock]

Your "Conection" class does not include a method called createStatement.

[adeeb alexander]

how could i do it can you say me please.

[Paul Sturrock]

Get rid of Conection and use Connection directly?

[adeeb alexander]

you mean that i should use the Connection class directly. The problem is in my real application i needed to establish connection many times so to get rid of that i created a new connection class. The advantage is that if the database changes also, it will be easy to change the SID and all the crap. So i thought it will be flexible o use a different connection class.
Hope you got me.

[Scott Selikoff]

You created your own connection class? More likely you wrapped a connection class, as creating a 'true' JDBC connection class would be... difficult.

[Paul Sturrock]

adeeb alexander wrote:The advantage is that if the database changes also, it will be easy to change the SID and all the crap.

I have to echo Scott's comment, writting the code to handle all the database nuts and bolts yourself in a custom connection class sounds way harder than just using the one the driver provides.

Why is the SID so important?

[adeeb alexander]

I am concerned about SID becuase i have to run the application on other system where the database already installed. The SID of that db would be different. What shall i do in that case.



Thanks.

[Paul Sturrock]

How about providing an extrernal configuration file that includes the database details?

[adeeb alexander]

The !dea is good. How can i implement it. Need a small example. Thanks for your patience and support Paul.


Thanks

[Paul Sturrock]

Well, you could just use a properties file and the Properties class. Or you could go further an use a DataSource.

[adeeb alexander]

Hi all.
Please see the code below and suggest me. Please tell me what i have done is good or not.

DBConnection.java

    
import java.sql.*;
public class DBConnection
{
public Connection getCon()throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:AHCET","scott","tiger");
return con;
}

exp.java

    
    
import java.sql.*;
	
//	Statement stmt;
public	exp()
{
try
{
	
DBConnection con = new DBConnection();
Statement stmt = con.getCon().createStatement();

System.out.println("ok");
ResultSet rset=stmt.executeQuery("select sysdate from dual");

while(rset.next())
{
System.out.println(rset.getString(1));
}
con.getCon().close();
}
catch(Exception ce)
{
	System.out.println(ce);
}

}
public static void main(String[] args)throws ClassNotFoundException,SQLException
{

exp ex = new exp();

}
}

I get the output. Please tell me is there any drawback in doing like that or will be there any problem in future if i try to do something different.


Thanks and Regards
alexander

[Jan Cumps]

Not good. You create two connections. One in line 11 and 1 in line 21.
You use the one from line 11.
You get a new one in line 21, and close it immediately.
You don't close the one from line 11.

[adeeb alexander]

Hi Cumps.
Thanks for your reply. There is a advantage if i use the above method, because where ever there is a change in connection parameters i have to change at one place only. Can you tell me how can i overcome the above drawback.

Thanks.

[Rene Larsen]

You don't need a DBConnection object, but a Connection object...

With this Connection object, you can do what ever you will do on the DB - and when you are finish with it you close it in a finally block (together with the Statement and ResultSet objects).

[adeeb alexander]

What you said is wright Rene. Now suppose 10 classes are there each class has code related to transaction with the database, then i have to use the Connection object 10 times. I should also give all the parameters i.e SID username and password. I don't want to do that. Instead do something where i have to give all those at only one place. Hope you got me.


Thanks.

[Rene Larsen]

Are you using the Connection(s) from a standalone Application or from a Web Server??

If it is from a standalone Application, then you need to code a Connection Pool class collection, where YOU control how connections are started and closed - and how they are re-newed or closed after a number of time used (or what you decide).
When your application start up ALL connections will be generated as you have decided they should be (how many initial, how many allowed idle - etc.)

If you are using a Web Server, you need to set up the connection to use on the server - then they will be handled by the web container.

Maybe you can use some of these Open Source DB Pools: http://java-source.net/open-source/connection-pools