Link to home
Start Free TrialLog in
Avatar of rcmb
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.MissingResourceException;
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("DBController 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.hasMoreElements()) {

            Driver driver = (Driver) allDrivers.nextElement();

            try {
                DriverManager.deregisterDriver(driver);

                        System.out.println("Deregistered JDBC driver " + driver.getClass().getName());
            }
            catch (SQLException e) {

                        System.out.println("Deregistered 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("Invalid maxconn value " + strMaxConn + " for " + strPoolName+e);

                  intMax = 0;
            }
            DBConnectionPool pool =      new DBConnectionPool(strPoolName, strUrl, strUser, strPassword, intMax);
            pools.put(strPoolName, pool);


            System.out.println("Initialized pool " + strPoolName);

    }

      public Exception getException() {
        return exception;
    }

    private void init() {
        try {
                     //InputStream is = getClass().getResourceAsStream("db.properties");
                  ResourceBundle rsBundle = ResourceBundle.getBundle("db.api.db");
                  strPoolName       = rsBundle.getString("poolname");
                  strUrl                  = rsBundle.getString("url");
                  strUser             = rsBundle.getString("user");
                  strPassword       = rsBundle.getString("password");
                  strMaxConn       = rsBundle.getString("maxconn");
                  strDriverClasses = rsBundle.getString("drivers");
            //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(strDriverClasses);
        while (st.hasMoreElements()) {
            String driverClassName = st.nextToken().trim();
            try {
                Driver driver = (Driver)
                Class.forName(driverClassName).newInstance();
                DriverManager.registerDriver(driver);
                drivers.addElement(driver);

                        System.out.println("Registered 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.firstElement();
                freeConnections.removeElementAt(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("Interrupted 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.hasMoreElements()) {
                Connection con = (Connection) allConnections.nextElement();
                try {
                    con.close();

                }
                catch (SQLException e) {

                }
            }
            freeConnections.removeAllElements();
        }

        /**
         * Creates a new connection, using a userid and password
         * if specified.
         */

        private Connection newConnection() {
            Connection con = null;
            try {
                if (user == null) {
                    con = DriverManager.getConnection(URL);
                }
                else {
                    con = DriverManager.getConnection(URL, user, password);
                }


                        System.out.println("Created 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.jtds.jdbc.Driver
url=jdbc:jtds:sqlserver://localhost/snadis;TDS=7.0
maxconn=900
poolname=techunified
user=snadis
password=SNADIS


Thanks in advance,
RCMB
Avatar of bloodredsun
bloodredsun
Flag of Australia image

What's the error/exception (if any)?
Avatar of rcmb
rcmb

ASKER

The error I am seeing is: "Error in getting connection from the Pool"

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() );
Avatar of rcmb

ASKER

I am going to run that and I will get back to you ASAP.

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() ;
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://localhost:1433/snadis;TDS=7.0
Avatar of rcmb

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
Avatar of rcmb

ASKER

TimYates -- how do I add the printStackTrace? It will not let me add it in place of getMessage

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() ;
Avatar of rcmb

ASKER

The actual error is:

Can't read properties file. Make sure db.properties is in the classpathjava.util.MissingResourceExeption: Can't find bundle for base name db.api.db, locale en_US

Please help!

RCMB
I assume db.properties is not on the classpath :-/
ASKER CERTIFIED SOLUTION
Avatar of limaideal
limaideal

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
well, if the db.properties is actually src/db/api/db.properties, you just need to make sure that file is in your classpath
Avatar of rcmb

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
Avatar of 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.
Avatar of rcmb

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
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?

Avatar of rcmb

ASKER

My entire problem seems to be centered around this code:

    private void init() {
        try {
                  //InputStream is = getClass().getResourceAsStream("db.properties");
               ResourceBundle rsBundle = ResourceBundle.getBundle("db.api.db");
               strPoolName      = rsBundle.getString("poolname");
               strUrl               = rsBundle.getString("url");
               strUser           = rsBundle.getString("user");
               strPassword      = rsBundle.getString("password");
               strMaxConn      = rsBundle.getString("maxconn");
               strDriverClasses = rsBundle.getString("drivers");
            //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
Avatar of rcmb

ASKER

db.properties is in the .war file located at

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.
Avatar of rcmb

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
Avatar of rcmb

ASKER

Actually let me correct my last.

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 :)