3 Replies Latest reply on May 7, 2003 3:57 AM by Jon Barnett

    3.20 nested query warning about open connections

    aarti Newbie

       

      "aarti" wrote:
      Using XA JDBC drivers from datadirect and SQLServer
      In 3.04, I was getting a transaction exception with nested queries.

      David Jencks I followed your suggestion and moved to 3.2 and also added the <track-connection-by-tx>true</track-connection-by-tx> to the config file.

      In 3.2, the transaction works but I get messages about open connections.

      The nested query code looks like this:

      Method 1
      Open connection
      Execute query
      loop through resultset
      Call method 2
      close resultset
      close conn
      end of method 1

      Method 2
      Open conn
      execute query
      close conn
      End of method 2



        • 1. Re: 3.20 nested query warning about open connections
          David Jencks Master

           

          "davidjencks" wrote:
          Looks like it should work to me. Can you show the stack trace and more of your code? I assume the call to method2 is inside the loop? I also assume it is a direct method call rather than through the ejb interface?


          • 2. Re: 3.20 nested query warning about open connections
            aarti Newbie

             

            "aarti" wrote:
            This happens with any nested query:

            This is the warning I am getting
            18:14:55,080 WARN [jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping

            My source code looks like this:

            public void updateCheckOrderStatus(ScheduledOrderVO scheduledOrderVO)
            {
            Connection con = null;

            try
            {
            con = SQLDAOFactory.getConnection();

            Statement stmt = con.createStatement();
            MessageFormat mf = new MessageFormat(UPDATE_CHECK_ORDER);
            Object[] arguments =
            {
            new String("'" + StringUtils.replace(scheduledOrderVO.checkedBy.userId.trim(), "'", "''") + "'"),
            new String("'" + StringUtils.replace(scheduledOrderVO.patientKeyVO.patientId.trim(), "'", "''") + "'"),
            new String("'" + StringUtils.replace(scheduledOrderVO.orderId.trim(), "'", "''") + "'"),
            new Integer(scheduledOrderVO.orderAdminNum.trim())};
            stmt.executeUpdate(mf.format(arguments));

            insertVerifiedOrder(scheduledOrderVO);
            stmt.close();
            con.close();
            }
            catch (SQLException se)
            {
            throw new LoggableSystemException(se, log, Level.ERROR, null);
            }
            finally
            {
            try
            {
            if (con != null)
            {
            con.close();
            }
            }
            catch (SQLException e)
            {
            }
            }



            private void insertVerifiedOrder(ScheduledOrderVO scheduledOrderVO)
            {
            Connection con = null;

            try
            {
            con = SQLDAOFactory.getConnection();

            Statement stmt = con.createStatement();
            MessageFormat mf = null;
            // Get the solution information for order
            mf = new MessageFormat(SELECT_SOLUTION_BY_ORDER);
            Object[] solutionArgs =
            { new String("'" + scheduledOrderVO.patientKeyVO.patientId + "'"),
            new String("'" + scheduledOrderVO.orderId + "'")};
            ResultSet rs = stmt.executeQuery(mf.format(solutionArgs));
            if (rs != null && rs.next())
            {
            boolean isSolution = rs.getInt(1) == 1 ? true : false;
            if (isSolution)
            // For a solution order the dose_val_min and dose_units are copied to the volume fields leaving the dose min, max and units null.
            mf = new MessageFormat(INSERT_VERIFIED_ORDER_WITH_VOLUME);
            else
            //For a non-solution order the dose_val_min, max and units are copied to the same fields and the volume fields are null.
            mf = new MessageFormat(INSERT_VERIFIED_ORDER);

            Object[] args =
            {
            new String("'" + scheduledOrderVO.patientKeyVO.patientId + "'"),
            new String("'" + scheduledOrderVO.orderId + "'"),
            new Integer(scheduledOrderVO.orderAdminNum)};
            System.out.println("EXECUTING:" + mf.format(args));
            int rows = stmt.executeUpdate(mf.format(args));

            if (rows < 1)
            throw new EJBException(
            " Error inserting Secure Order for patient"
            + scheduledOrderVO.patientKeyVO.patientId
            + " and order "
            + scheduledOrderVO.orderId);
            // For a solution order add the solution information in the secure order solution table
            if (isSolution)
            {
            Statement stmt2 = con.createStatement();

            MessageFormat mf2 = new MessageFormat(INSERT_VERIFIED_ORDER_SOLUTION);
            System.out.println("EXECUTING:" + mf2.format(args));
            rows = stmt.executeUpdate(mf2.format(args));
            }
            }
            else
            throw new EJBException(" InsertVerifiedOrder: No Order found ");

            }
            catch (Exception e)
            {
            throw new LoggableSystemException(e, log, Level.ERROR, null);
            }
            finally
            {
            try
            {
            if (con != null)
            {
            con.close();
            }
            }
            catch (SQLException e)
            {
            }
            }
            }


            • 3. Re: 3.20 nested query warning about open connections
              Jon Barnett Master

               

              "jonlee" wrote:
              Actually, the problem isn't about releasing connections. JBoss is complaining that you don't close your statements before you release the connection. In the first method, you have a stmt.close() - you should make sure that you try to do this even if things go awry when you execute statements.

              In the second method, you create two statements but don't have any code to close either.