5 Replies Latest reply on Dec 5, 2001 9:18 PM by tinnes

    Getting a database connection from a helper class

    tinnes

      We are attempting to write a generic JDBC error translator. We have created a helper class, that maps dbms specific errors to application error codes. The translation is done using database tables. This class is being instantiated inside a BMP Entity bean. To get a database connection in the helper class we are:

      // Do a JNDI lookup for a new connection to the database
      try {
      javax.naming.Context context = new javax.naming.InitialContext();
      dataSource = (DataSource) context.lookup("java:comp/env/jdbc/DefaultDS");
      }
      catch(Exception e) {
      System.out.println( "ERROR: DBErrorTranslatorFactory: getParseMethod" );
      System.out.println( "ERROR: CLASSNAME = " + e.getClass().toString() );
      }

      Our SQL is executed as follows:

      PreparedStatement statement = null;
      try {
      newConnection = dataSource.getConnection();
      String Strtest = newConnection.getMetaData().getDatabaseProductVersion();
      System.out.println( "DatabaseVersion is " + Strtest);
      statement = newConnection.prepareStatement("select 1 as num_recs");

      System.out.println( "Num Rec Count");
      ResultSet resultSet = statement.executeQuery();

      System.out.println( "Num Rec Count 2");
      resultSet.next();

      blah, blah exception handling etc.

      For this connection a getMetaData function returns the proper database version. So it would seem we are connected. However, regardless of what the query is an SQLException of no datafound is thrown. Any ideas on what the problem may be??????

      Questions:
      1. Could we have a connection pool issue?
      2. Can Helper classes use JNDI for EJB's?


      Tom

        • 1. Re: Getting a database connection from a helper class
          hstech

          Tom,

          You don't say where in your code an exception is being thrown, so I'll assume it's at the line with the executeQuery() method call.

          Your SQL statement of "select 1 as rec_num" does not make a lot of sense. Is this actually the SQL you are executing? I would imagine this would cause problems without a FROM clause. Certainly, that's what Oracle gives me when I try your SQL.

          Helper classes can access DataSources via JNDI, just as you have in your first code snippet.

          Also, not sure how much of your code you have included, but you should be wrapping your code with try..finally blocks to close the Connection, PreparedStatement and ResultSet objects.

          Hope this helps,
          Aaron.

          • 2. Re: Getting a database connection from a helper class
            tinnes

            The code snippets are from the error translator jar. The original exception occurs in the BMP entity bean and is caught and then we instantiate the error translator factory. Our problem is the exception that occurs in the error translator jar regardless of what the sql is. The sql code snippet is just a contrived example and regardless of what it is a no datafound exception is thrown.

            Here is the code snippet from the BMP Entity Bean.

            catch(SQLException e) {

            try {
            DBErrorTranslatorFactory theFactory = new DBErrorTranslatorFactory( connection );
            DBErrorTranslator theErrorTranslator = theFactory.create();
            }
            catch ( Exception theEx ) {
            System.out.println( "Create: ERROR");
            System.out.println( theEx.toString());
            }

            blah blah blah.....

            The connection that is passed is just the current connection. The factory just uses it to determine from the meta data, what product and driver is being used. The factory then tries to create a connection to its own error database to transform the error.


            Do you have any examples of using JNDI lookup within a helper class?

            Tom

            • 3. Re: Getting a database connection from a helper class
              marc.fleury

              the global JNDI tree is *global* meaning you see inside your VM from EJBs/POJOs/Beans/MBeans whatever any java class.

              • 4. Re: Getting a database connection from a helper class
                danch1

                Your using a database to translate database error messages? What translates the translators database error messages? What if the error is that the database went away?

                • 5. Re: Getting a database connection from a helper class
                  tinnes

                  Upon further analysis we have discovered that the problem only happens when the translation method is called within the catch of a SQLException. When the translation method is outside the catch block everything works as expected. We have tried closing the current connection and removed all references to the metadata and yet our new connection using a different datasource returns a SQL exception of no data found.

                  Does anybody have any ideas??????

                  Tom