[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Database connection works with SQL 2000 but not with SQL 7.0

Posted on 2005-04-20
26
Medium Priority
?
360 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:rcmb
  • 12
  • 7
  • 3
  • +2
26 Comments
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13824592
What's the error/exception (if any)?
0
 
LVL 12

Author Comment

by:rcmb
ID: 13824630
The error I am seeing is: "Error in getting connection from the Pool"

Sorry for not posting that.
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13824644
Are are you connecting to the correct database and port (normally specified in the db URL string)?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:bloodredsun
ID: 13824660
You'd get more info with
System.out.println("Error in getting connection from the Pool : " + e.getMessage() );
0
 
LVL 12

Author Comment

by:rcmb
ID: 13824720
I am going to run that and I will get back to you ASAP.

RCMB
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13825414
>>  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() ;
0
 
LVL 2

Expert Comment

by:topher1120
ID: 13825763
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.
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13826067
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
0
 
LVL 12

Author Comment

by:rcmb
ID: 13826669
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
0
 
LVL 12

Author Comment

by:rcmb
ID: 13826681
TimYates -- how do I add the printStackTrace? It will not let me add it in place of getMessage

RCMB
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13826932
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() ;
0
 
LVL 12

Author Comment

by:rcmb
ID: 13827134
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
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13827186
I assume db.properties is not on the classpath :-/
0
 
LVL 2

Accepted Solution

by:
limaideal earned 2000 total points
ID: 13827195
:(

MAke sre db.properties is in src/.
Then change
ResourceBundle rsBundle = ResourceBundle.getBundle("db.api.db");
to
ResourceBundle rsBundle = ResourceBundle.getBundle("db");
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13827207
well, if the db.properties is actually src/db/api/db.properties, you just need to make sure that file is in your classpath
0
 
LVL 12

Author Comment

by:rcmb
ID: 13827226
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
0
 
LVL 12

Author Comment

by:rcmb
ID: 13827308
HOw do I get it in my classpath? I am missing something here :-)
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13827389
rcmb,

tell us how do youstart your Java app( is there a shell script? ) and also where is you db.properties file exists.
0
 
LVL 12

Author Comment

by:rcmb
ID: 13827581
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
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13827661
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?

0
 
LVL 12

Author Comment

by:rcmb
ID: 13827668
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
0
 
LVL 12

Author Comment

by:rcmb
ID: 13827759
db.properties is in the .war file located at

WEB-INF\classes\db\api

RCMB
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13828226
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.
0
 
LVL 12

Author Comment

by:rcmb
ID: 13835536
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
0
 
LVL 12

Author Comment

by:rcmb
ID: 13835547
Actually let me correct my last.

limaideal actually had the correct answer.

Thanks
0
 
LVL 2

Expert Comment

by:limaideal
ID: 13835683
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 :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Suggested Courses
Course of the Month19 days, 3 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question