rcmb
asked on
Database connection works with SQL 2000 but not with SQL 7.0
I have a file that establishes my db connection. In SQL 2000 this works fine but when I install it on a server running SQL 7.0 it fails. What can I possibly be doing wrong here??
Here is the file that establishes the connection:
package mil.navy.snadis.db.api;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.MissingResourceE xception;
import java.util.ResourceBundle;
import java.util.StringTokenizer;
import java.util.Vector;
public class DBController {
static private DBController instance; // The single instance
static private int clients;
private Vector drivers = new Vector();
private PrintWriter log;
private Hashtable pools = new Hashtable();
private String strPoolName = null;
private String strUser = null;
private String strPassword = null;
private String strMaxConn = null;
private String strUrl = null;
private String strDriverClasses = null;
private Exception exception = null;
static synchronized public DBController getInstance() {
if (instance == null) {
instance = new DBController();
}
clients++;
return instance;
}
private DBController() {
init();
System.out.println("DBCont roller Started Successfully");
}
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}
public Connection getConnection(String name) {
try {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection();
}
else {
return null;
}
}
catch (Exception e) {
System.out.println("Error in getting connection from the Pool : " + e);
return null;
}
}
public Connection getConnection(String name, long time) {
try {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
else {
return null;
}
}
catch (Exception e) {
System.out.println("Error in getting connection from the Pool : " + e);
return null;
}
}
public synchronized void release() {
if (--clients != 0) {
return;
}
Enumeration allPools = pools.elements();
while (allPools.hasMoreElements( )) {
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElement s()) {
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDr iver(drive r);
System.out.println("Deregi stered JDBC driver " + driver.getClass().getName( ));
}
catch (SQLException e) {
System.out.println("Deregi stered JDBC driver " + driver.getClass().getName( ));
}
}
}
private void createPools() {
if (strUrl == null) {
System.out.println("No URL specified for " + strPoolName);
}
int intMax;
try {
intMax = Integer.valueOf(strMaxConn ).intValue ();
}
catch (NumberFormatException e) {
System.out.println("Invali d maxconn value " + strMaxConn + " for " + strPoolName+e);
intMax = 0;
}
DBConnectionPool pool = new DBConnectionPool(strPoolNa me, strUrl, strUser, strPassword, intMax);
pools.put(strPoolName, pool);
System.out.println("Initia lized pool " + strPoolName);
}
public Exception getException() {
return exception;
}
private void init() {
try {
//InputStream is = getClass().getResourceAsSt ream("db.p roperties" );
ResourceBundle rsBundle = ResourceBundle.getBundle(" db.api.db" );
strPoolName = rsBundle.getString("poolna me");
strUrl = rsBundle.getString("url");
strUser = rsBundle.getString("user") ;
strPassword = rsBundle.getString("passwo rd");
strMaxConn = rsBundle.getString("maxcon n");
strDriverClasses = rsBundle.getString("driver s");
//Properties dbProps = new Properties();
//dbProps.load(is);
loadDrivers();
createPools();
}
catch (MissingResourceException e) {
this.exception = new IOException("Properties file not found in dbconnection");
System.out.println("Can't read the properties file. Make sure db.properties is in the CLASSPATH"+e);
}
}
private void loadDrivers() {
StringTokenizer st = new StringTokenizer(strDriverC lasses);
while (st.hasMoreElements()) {
String driverClassName = st.nextToken().trim();
try {
Driver driver = (Driver)
Class.forName(driverClassN ame).newIn stance();
DriverManager.registerDriv er(driver) ;
drivers.addElement(driver) ;
System.out.println("Regist ered JDBC driver " + driverClassName);
}
catch (Exception e) {
System.out.println("Can't Registered JDBC driver " + driverClassName+e);
}
}
}
class DBConnectionPool {
private int checkedOut;
private Vector freeConnections = new Vector();
private int maxConn;
private String name;
private String password;
private String URL;
private String user;
/**
* Creates new connection pool.
*
* @param name The pool name
* @param URL The JDBC URL for the database
* @param user The database user, or null
* @param password The database user password, or null
* @param maxConn The maximal number of connections, or 0
* for no limit
*/
public DBConnectionPool(String name, String URL, String user, String password,
int maxConn) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
/**
* Checks in a connection to the pool. Notify other Threads that
* may be waiting for a connection.
*
* @param con The connection to check in
*/
public synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
freeConnections.addElement (con);
checkedOut--;
notifyAll();
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
*/
public synchronized Connection getConnection() {
Connection con = null;
if (freeConnections.size() > 0) {
// Pick the first Connection in the Vector
// to get round-robin usage
con = (Connection) freeConnections.firstEleme nt();
freeConnections.removeElem entAt(0);
try {
if (con.isClosed()) {
// Try again recursively
con = getConnection();
}
}
catch (SQLException e) {
con = getConnection();
}
}
else if (maxConn == 0 || checkedOut < maxConn) {
con = newConnection();
}
if (con != null) {
checkedOut++;
}
return con;
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
* <P>
* If no connection is available and the max number has been
* reached, this method waits the specified time for one to be
* checked in.
*
* @param timeout The timeout value in milliseconds
*/
public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con;
while ((con = getConnection()) == null) {
try {
wait(timeout);
}
catch (InterruptedException e) {
//write the status to a log file.
// AdminLogger.fatal("Interru pted while waiting for connection : " , e);
}
if ((new Date().getTime() - startTime) >= timeout) {
// Timeout has expired
return null;
}
}
return con;
}
/**
* Closes all available connections.
*/
public synchronized void release() {
Enumeration allConnections = freeConnections.elements() ;
while (allConnections.hasMoreEle ments()) {
Connection con = (Connection) allConnections.nextElement ();
try {
con.close();
}
catch (SQLException e) {
}
}
freeConnections.removeAllE lements();
}
/**
* Creates a new connection, using a userid and password
* if specified.
*/
private Connection newConnection() {
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnectio n(URL);
}
else {
con = DriverManager.getConnectio n(URL, user, password);
}
System.out.println("Create d a new connection in pool " + name);
}
catch (SQLException e) {
System.out.println("Can't create a new connection for " + URL +e);
return null;
}
return con;
}
}
public String getPoolName()
{
return strPoolName;
}
}
Here is the db.properties file:
#SQL SERVER
drivers=net.sourceforge.jt ds.jdbc.Dr iver
url=jdbc:jtds:sqlserver:// localhost/ snadis;TDS =7.0
maxconn=900
poolname=techunified
user=snadis
password=SNADIS
Thanks in advance,
RCMB
Here is the file that establishes the connection:
package mil.navy.snadis.db.api;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.MissingResourceE
import java.util.ResourceBundle;
import java.util.StringTokenizer;
import java.util.Vector;
public class DBController {
static private DBController instance; // The single instance
static private int clients;
private Vector drivers = new Vector();
private PrintWriter log;
private Hashtable pools = new Hashtable();
private String strPoolName = null;
private String strUser = null;
private String strPassword = null;
private String strMaxConn = null;
private String strUrl = null;
private String strDriverClasses = null;
private Exception exception = null;
static synchronized public DBController getInstance() {
if (instance == null) {
instance = new DBController();
}
clients++;
return instance;
}
private DBController() {
init();
System.out.println("DBCont
}
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}
public Connection getConnection(String name) {
try {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection();
}
else {
return null;
}
}
catch (Exception e) {
System.out.println("Error in getting connection from the Pool : " + e);
return null;
}
}
public Connection getConnection(String name, long time) {
try {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
else {
return null;
}
}
catch (Exception e) {
System.out.println("Error in getting connection from the Pool : " + e);
return null;
}
}
public synchronized void release() {
if (--clients != 0) {
return;
}
Enumeration allPools = pools.elements();
while (allPools.hasMoreElements(
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElement
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDr
System.out.println("Deregi
}
catch (SQLException e) {
System.out.println("Deregi
}
}
}
private void createPools() {
if (strUrl == null) {
System.out.println("No URL specified for " + strPoolName);
}
int intMax;
try {
intMax = Integer.valueOf(strMaxConn
}
catch (NumberFormatException e) {
System.out.println("Invali
intMax = 0;
}
DBConnectionPool pool = new DBConnectionPool(strPoolNa
pools.put(strPoolName, pool);
System.out.println("Initia
}
public Exception getException() {
return exception;
}
private void init() {
try {
//InputStream is = getClass().getResourceAsSt
ResourceBundle rsBundle = ResourceBundle.getBundle("
strPoolName = rsBundle.getString("poolna
strUrl = rsBundle.getString("url");
strUser = rsBundle.getString("user")
strPassword = rsBundle.getString("passwo
strMaxConn = rsBundle.getString("maxcon
strDriverClasses = rsBundle.getString("driver
//Properties dbProps = new Properties();
//dbProps.load(is);
loadDrivers();
createPools();
}
catch (MissingResourceException e) {
this.exception = new IOException("Properties file not found in dbconnection");
System.out.println("Can't read the properties file. Make sure db.properties is in the CLASSPATH"+e);
}
}
private void loadDrivers() {
StringTokenizer st = new StringTokenizer(strDriverC
while (st.hasMoreElements()) {
String driverClassName = st.nextToken().trim();
try {
Driver driver = (Driver)
Class.forName(driverClassN
DriverManager.registerDriv
drivers.addElement(driver)
System.out.println("Regist
}
catch (Exception e) {
System.out.println("Can't Registered JDBC driver " + driverClassName+e);
}
}
}
class DBConnectionPool {
private int checkedOut;
private Vector freeConnections = new Vector();
private int maxConn;
private String name;
private String password;
private String URL;
private String user;
/**
* Creates new connection pool.
*
* @param name The pool name
* @param URL The JDBC URL for the database
* @param user The database user, or null
* @param password The database user password, or null
* @param maxConn The maximal number of connections, or 0
* for no limit
*/
public DBConnectionPool(String name, String URL, String user, String password,
int maxConn) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
/**
* Checks in a connection to the pool. Notify other Threads that
* may be waiting for a connection.
*
* @param con The connection to check in
*/
public synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
freeConnections.addElement
checkedOut--;
notifyAll();
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
*/
public synchronized Connection getConnection() {
Connection con = null;
if (freeConnections.size() > 0) {
// Pick the first Connection in the Vector
// to get round-robin usage
con = (Connection) freeConnections.firstEleme
freeConnections.removeElem
try {
if (con.isClosed()) {
// Try again recursively
con = getConnection();
}
}
catch (SQLException e) {
con = getConnection();
}
}
else if (maxConn == 0 || checkedOut < maxConn) {
con = newConnection();
}
if (con != null) {
checkedOut++;
}
return con;
}
/**
* Checks out a connection from the pool. If no free connection
* is available, a new connection is created unless the max
* number of connections has been reached. If a free connection
* has been closed by the database, it's removed from the pool
* and this method is called again recursively.
* <P>
* If no connection is available and the max number has been
* reached, this method waits the specified time for one to be
* checked in.
*
* @param timeout The timeout value in milliseconds
*/
public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con;
while ((con = getConnection()) == null) {
try {
wait(timeout);
}
catch (InterruptedException e) {
//write the status to a log file.
// AdminLogger.fatal("Interru
}
if ((new Date().getTime() - startTime) >= timeout) {
// Timeout has expired
return null;
}
}
return con;
}
/**
* Closes all available connections.
*/
public synchronized void release() {
Enumeration allConnections = freeConnections.elements()
while (allConnections.hasMoreEle
Connection con = (Connection) allConnections.nextElement
try {
con.close();
}
catch (SQLException e) {
}
}
freeConnections.removeAllE
}
/**
* Creates a new connection, using a userid and password
* if specified.
*/
private Connection newConnection() {
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnectio
}
else {
con = DriverManager.getConnectio
}
System.out.println("Create
}
catch (SQLException e) {
System.out.println("Can't create a new connection for " + URL +e);
return null;
}
return con;
}
}
public String getPoolName()
{
return strPoolName;
}
}
Here is the db.properties file:
#SQL SERVER
drivers=net.sourceforge.jt
url=jdbc:jtds:sqlserver://
maxconn=900
poolname=techunified
user=snadis
password=SNADIS
Thanks in advance,
RCMB
What's the error/exception (if any)?
ASKER
The error I am seeing is: "Error in getting connection from the Pool"
Sorry for not posting that.
Sorry for not posting that.
Are are you connecting to the correct database and port (normally specified in the db URL string)?
You'd get more info with
System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
ASKER
I am going to run that and I will get back to you ASAP.
RCMB
RCMB
>> You'd get more info with
>> System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
you'd be better off with:
e.printStackTrace() ;
>> System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
you'd be better off with:
e.printStackTrace() ;
You might check your JDBC driver documentation from Microsoft. I am almost positive the MS JDBC driver does not support anything older than SQLServer 2000.
From URL, you didnt specify any port. Maybe you want to verify SQL 7.0 port and put it into the DB URL.
e.g.:
jdbc:jtds:sqlserver://loca lhost:1433 /snadis;TD S=7.0
e.g.:
jdbc:jtds:sqlserver://loca
ASKER
topher1120 -- I use this driver elsewhere and it works fine in SQL 7. (Is the 1120 significant in your screen name?)
limaideal -- added the port and no change
I am still working to get the true error message out as we are experiencing some server issues.
RCMB
limaideal -- added the port and no change
I am still working to get the true error message out as we are experiencing some server issues.
RCMB
ASKER
TimYates -- how do I add the printStackTrace? It will not let me add it in place of getMessage
RCMB
RCMB
Replace:
System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
with
System.err.println("Error in getting connection from the Pool : " );
e.printStackTrace() ;
System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
with
System.err.println("Error in getting connection from the Pool : " );
e.printStackTrace() ;
ASKER
The actual error is:
Can't read properties file. Make sure db.properties is in the classpathjava.util.Missing ResourceEx eption: Can't find bundle for base name db.api.db, locale en_US
Please help!
RCMB
Can't read properties file. Make sure db.properties is in the classpathjava.util.Missing
Please help!
RCMB
I assume db.properties is not on the classpath :-/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
well, if the db.properties is actually src/db/api/db.properties, you just need to make sure that file is in your classpath
ASKER
Tim -- Funny thing is - I have the exact same setup on two machines except one machine is running SQL 7 and the other is SQL 2K. All works fine on the SQL 2K machine but it dies on the SQL 7 machine.
limaideal -- Testing now.
RCMB
limaideal -- Testing now.
RCMB
ASKER
HOw do I get it in my classpath? I am missing something here :-)
rcmb,
tell us how do youstart your Java app( is there a shell script? ) and also where is you db.properties file exists.
tell us how do youstart your Java app( is there a shell script? ) and also where is you db.properties file exists.
ASKER
I am using JBoss and I start the server and then type in:
http://localhost:8080/Personnel
I am taken to my home page (index.jsp) and then I navigate to the page that I use to input data (a form that when submitted writes to the database). The problem is when I write to the database.
here is my actual problem:
Can't find bundle for base name db.api.db, locale en_US
Is there an issue with a jdk that could be causing this problem?
Anyone seen this before?
RCMB
http://localhost:8080/Personnel
I am taken to my home page (index.jsp) and then I navigate to the page that I use to input data (a form that when submitted writes to the database). The problem is when I write to the database.
here is my actual problem:
Can't find bundle for base name db.api.db, locale en_US
Is there an issue with a jdk that could be causing this problem?
Anyone seen this before?
RCMB
So you must deployed you app as war,ear or jar in JBoss. Can you verify if the db/api/db.properties is included in your package file?
ASKER
My entire problem seems to be centered around this code:
private void init() {
try {
//InputStream is = getClass().getResourceAsSt ream("db.p roperties" );
ResourceBundle rsBundle = ResourceBundle.getBundle(" db.api.db" );
strPoolName = rsBundle.getString("poolna me");
strUrl = rsBundle.getString("url");
strUser = rsBundle.getString("user") ;
strPassword = rsBundle.getString("passwo rd");
strMaxConn = rsBundle.getString("maxcon n");
strDriverClasses = rsBundle.getString("driver s");
//Properties dbProps = new Properties();
//dbProps.load(is);
loadDrivers();
createPools();
}
catch (MissingResourceException e) {
this.exception = new IOException("Properties file not found in dbconnection");
System.out.println("Can't read the properties file. Make sure db.properties is in the CLASSPATH"+e);
}
}
The system cannot find the properties file.
RCMB
private void init() {
try {
//InputStream is = getClass().getResourceAsSt
ResourceBundle rsBundle = ResourceBundle.getBundle("
strPoolName = rsBundle.getString("poolna
strUrl = rsBundle.getString("url");
strUser = rsBundle.getString("user")
strPassword = rsBundle.getString("passwo
strMaxConn = rsBundle.getString("maxcon
strDriverClasses = rsBundle.getString("driver
//Properties dbProps = new Properties();
//dbProps.load(is);
loadDrivers();
createPools();
}
catch (MissingResourceException e) {
this.exception = new IOException("Properties file not found in dbconnection");
System.out.println("Can't read the properties file. Make sure db.properties is in the CLASSPATH"+e);
}
}
The system cannot find the properties file.
RCMB
ASKER
db.properties is in the .war file located at
WEB-INF\classes\db\api
RCMB
WEB-INF\classes\db\api
RCMB
RCMB:
it might be odd, but cna you try copy db.properties to db_en_US.properties and restart JBoss and run it again?
I think I have encountered problem with ResourceBundles that cannot find the default properties file before. Let's just do this to narrow cause of the problem.
it might be odd, but cna you try copy db.properties to db_en_US.properties and restart JBoss and run it again?
I think I have encountered problem with ResourceBundles that cannot find the default properties file before. Let's just do this to narrow cause of the problem.
ASKER
Okay this is what I did.
I moved the db.properties file to the src directory and redelpoyed my war file. The db.properties is now in WEB-INF/classes and all works fine. The system can find the file and everyting gets saved.
Thanks for all your help.
Since no one really had the answer but everyone helped I will split the points.
RCMB
I moved the db.properties file to the src directory and redelpoyed my war file. The db.properties is now in WEB-INF/classes and all works fine. The system can find the file and everyting gets saved.
Thanks for all your help.
Since no one really had the answer but everyone helped I will split the points.
RCMB
ASKER
Actually let me correct my last.
limaideal actually had the correct answer.
Thanks
limaideal actually had the correct answer.
Thanks
RCMB, Thanks fo ryou rpoints :)
Now we all see the fun of programming. Some time a simple thing could drive a whole lots of long time gurus crazy :)
Now we all see the fun of programming. Some time a simple thing could drive a whole lots of long time gurus crazy :)