Need HELP!!! Are beans dropping my connection?

Its Microsoft SQLServer 2000 Driver for JDBC, I can't see anyway to find more information about it than that.

Our set up is that there are 3 class files that have to be present and compiled to access the database.  Connection, ConnectionPool and PooledConnection.  My class file (ones that work directly with a specified program) import these.  

public void execute( HttpServletRequest request,HttpServletResponse response, ServletContext context ) throws Exception
{
      // Get a database connection from connection pool -- OGA_POOL
      ConnectionPool pool = ( ConnectionPool )context.getAttribute( "OGA_POOL" );
      Connection con = pool.getConnection();

      // create HTTP session
      HttpSession session = request.getSession();

      try
      {
            if ( con != null )
            {
                                                                  String mode = ( String )request.getParameter( "updatemode" );
                                                                  if ( mode == null )                                          {
                                                                        mode = "empty";
                  }

Then I test the mode and do the stuff i need to add/update delete whatever.  We use stored procedures but sometimes I put in sql directly myself until we make a sp to replace it.  I dont' want to put more code up because the boss mentioned about privacy and us not taking code to use elsewhere (in a round about way).

One of the areas where I got this error was
else if ( mode.equals( "searchlastname" ) )
{
      System.err.println( "Get last names from initial" );
      String Letter = ( String )request.getParameter( "NameInitial" );
      CallableStatement cstmt = con.prepareCall( "{? = call  
                             spSSCGetPersonalInfoWithALetter(?)}" );
      cstmt.registerOutParameter( 1, java.sql.Types.INTEGER );
      cstmt.setString( 2, Letter );
      ResultSet rs = cstmt.executeQuery();
      Object[] search_results = parseResultSet( rs );
      returnVal = cstmt.getInt( 1 );
      request.setAttribute( "search_results", search_results );
      rs.close();
}

He has mentioned to us that we need to manually close off the statement and the con manually if we do a direct connection.  There is a problem with stuff not closing properly.  The guy working with me is using direct connections for everything and he has no problems.  He also had no problems running my program.  The first time we demoed the program took so long to run we came to my machine to test it because it would be faster.  I am not sure if the problem is his computer or mine or the server.  

But I want to rule out the beans.  They just seem to be so useful:

Pros I have found:
1. Cuts down on the stored procedures and the number of times I have to access the database
2. I don't have to use functions to convert special characters to pass them between pages, to the database and through URL's (we had a lot of problems with this in the past).
3. URL length isn't a problem when passing a textarea that could be 7000 characters long.
4. Bean is same datatype as field in database so no need to convert/cast types.
5. Persistant in memory; I can access my beans on any page in my program (like for my logged in user)
6. Forms are so much easier.
7. I can access the bean in my class where I am doing all the database work.
8. Client has 30 properties!  Try passing that through a url or hidden fields!

I know that its tricky making sure you load the bean and clear it in the right spots.  I love it tho!  

Even if you can't help with my socket error (because that is his problem, he's the webmaster and more experienced plus the server is on his machine so I can't help him even if I tried) can anyone let me know if there are issues with beans that I should switch to passing them as variables or sessions?  

btw. Tim you have been helpful so far.  I just need to know if this is an issue with my coding, the beans, the class or its the server or driver.  Would I be getting these errors if I never put the beans in?  If I grabbed everything using session.getAttribute and request.getParameter?  Would it be safer just to take out all the beans?  Are beans UNRELIABLE?

It sounds to me like you just need to configure your connection pool to use less connections or the database to accept more connections that would depend mostly on how busy the site will be and how much system resources  you have to dedicate to the database server.

My javabeans do no accessing to the database.  I just use it to save properties for my users and clients.  So am I correct in believing that its not the javabeans?  The class files we use are 3 to create the connection, we have ones directly used in porjects that access the connection ones and I'm using javabeans.  Are javabeans a reliable means of storing information in memory?

My javabeans are set to session scope, should they be set to application scope?  I tried that but when I tried to do ClientBean newBean = ( ClientBean )session.getAttribute( "clientbean" ); I would get an error.  Should it be application.getAttribute?  I think I tried that and it didnt' work either.  

On this page:

public void execute( HttpServletRequest request,HttpServletResponse response, ServletContext context ) throws Exception
{
     // Get a database connection from connection pool -- OGA_POOL
     ConnectionPool pool = ( ConnectionPool )context.getAttribute( "OGA_POOL" );
     Connection con = pool.getConnection();

     // create HTTP session
     HttpSession session = request.getSession();

     try
     {
          if ( con != null )
          {
                                                       String mode = ( String )request.getParameter( "updatemode" );
                                                       if ( mode == null )                                   {
                                                            mode = "empty";
               }

which is every java file we make for a project (like for my project I have clients, counsellors and visits and a java for each) at the end of the try after all the catches in the finallly statement it releases the connection.

A user on another forum where I asked for comparison of session variables and beans (for stability) said that beans are complex sessions.  This made perfect sense when I heard it because I import the bean from my JSP pages into the java files using session.getAttribute.  When I told my boss this it seemed to reassure him.

Being a student we often take code from previous projects and alter it to the one we are currently assigned.  I don't like lots of code in my files I don't understand so I try to look it over and figure it out.  We use stored procedures and I've seen that in none of our projects do we close off the callable statements.
CallableStatement cstmt = con.prepareCall( "{? = call spStoredProcedure(?)}" );

So now I am making sure in my project that I close off the cstmt.  Our supervisor came to us months ago complaining that connections were not properly closing and we need to make sure we close off all ResultSet (rs) and Statement (rs) we open.  I think maybe the cstmt might not be closing off as well.

I absolutely love beans!  We had to make ugly nasty functions to replace special characters we passed to the database (they were being saved as a ?) and another one if we passed information with special characters through URLs.  The bean passed special characters to the database without any hassle and, using a generic page we made to display everything in a table for varification, the special characters display with no problems.  That and many other reasons made me very reluctant to give up my beans.

Sorry I haven't posted lately.  The project has been pushed into high gear and I've been so busy I've had dreams where I'm coding :(

I've earned 75 points lately so I'm bumping this up to 300 value and splitting the points.

Thanks for all the help!!

Increasing.

Trying to increase again.

Ok that did it :)

Thanks again guys!

Thanks for the points :)

-Pat