Hi All:

Can somebody help me?

I am using the following: Win2000Pro, MySQL 3.1.23, Java 1.3, Tomcat 4.0, Poolman as my development environment.

Unfortunately, when I run my application and track the results using mysqladmin I notice that the Threads are not being released after an object accesses the database. I have a class with methods that are set up like this:

----------------------------------------------------------------------------

----------------------------------------- public class ProductBODT { public ProductBODT() { } public Vector getSKUList(int param_ProductID){ String sql = "SELECT id FROM tbl_itemsku WHERE productid = ?"; SQLBean sB = new SQLBean(); //class included below ResultSet rS = null; Connection connt = null; PreparedStatement ps = null; Vector SKUListVector = new Vector(); try{ connt = sB.makeConnectionObj(); ps = connt.prepareStatement(sql); ps.setInt(1, param_ProductID); rS = ps.executeQuery(); while (rS.next()){ ItemSKU sku = new ItemSKU(); sku.getProfile(rS.getInt("id")); SKUListVector.add(sku); } // end while } catch (Exception e){ LogUtil.catProductBODT.error("getSKUList() error =>"+ e.getMessage()); } // end catch finally{ if (null != connt){try{connt.close();}catch(Exception e){}} // I'm closing the connection here. Why isn't it working? if (null != rS){try{rS.close();}catch(Exception e){}} if (null != ps){try{ps.close();}catch(Exception e){}} if (null != sB){try{sB = null;}catch(Exception e){}} } // end finally return SKUListVector; }// end method } // end class

----------------------------------------------------------------------------

----------------------------------------- The class ProductBODT uses another class called SQLBean to access the database, and it looks like:

----------------------------------------------------------------------------

-----------------------------------------

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Statement; import java.io.IOException; import java.util.*; import java.sql.*; import javax.naming.Context; import javax.naming.InitialContext; import com.bygo.LogUtil; import com.codestudio.util.*; import com.codestudio.sql.PoolMan; import javax.sql.DataSource;

public class SQLBean { public SQLBean() { } public Connection makeConnection(String dbURL){ try{ Class.forName(myDriver); myConn = DriverManager.getConnection("jdbc:mysql:///" + dbURL); } catch(Exception e){ LogUtil.catSQLBean.fatal("makeConnection(String dbURL) error : " + e.getMessage()); } return myConn; } // end makeConnection method

public Connection makeConnectionObj() throws ClassNotFoundException, SQLException { Connection con = null; try { // load the PoolMan JDBC Driver Class.forName("com.codestudio.sql.PoolMan").newInstance(); // make connection out of the poolman jdbc driver. MyDatabaseName is what's been defined in the <dbname> tag in // poolman.xml file. con = DriverManager.getConnection("jdbc:poolman://MyDatabaseName"); } // end try catch (SQLException sqle) { LogUtil.catSQLBean.error("makeConnectionObj errors: " + sqle.getMessage()); sqle.printStackTrace(); } // end catch catch (Exception e){ LogUtil.catSQLBean.error("makeConnectionObj error : " + e.getClass() + " " + e.getMessage()); e.printStackTrace(); } // end catch return con; } // end method

public void makeConnection(){ try{ Class.forName(myDriver); myConn = DriverManager.getConnection(myURL); } catch(Exception e){ LogUtil.catSQLBean.fatal("makeConnection() error : " + e.getMessage()); e.printStackTrace(); } }

//public abstract void cleanup() throws Exception; public void takeDown(){ //****** Should I be doing something in here??? ****** // ****** I close the connection in the class ProductBODT so I shouldn't need this method??? Please confirm. }

/** *executeSQL() for taking a SQL statement, and return *a result as type ResultSet *@param String SQL */ public ResultSet executeSQL(String SQL){ try{ this.stmt = myConn.createStatement(); rs = stmt.executeQuery(SQL); } catch(SQLException e){ LogUtil.catSQLBean.fatal("executeSQL Error" + e.getMessage() + "\nSQL = " + SQL); } return rs; } public void executeUpdate(String SQL) { try{ this.stmt = myConn.createStatement(); stmt.executeUpdate(SQL); } catch(Exception e){ LogUtil.catSQLBean.fatal("executeUpdate Error :" + e.getMessage() + " \nSQL = " + SQL); } // end catch } // end method

private String myDriver = "org.gjt.mm.mysql.Driver"; private String myURL = "jdbc:mysql:///MyDatabaseName"; private Connection myConn = null; private ResultSet rs ; private Statement stmt = null; } } // end class

----------------------------------------------------------------------------

----------------------------------------- MySQL Admin output looks like this:

----------------------------------------------------------------------------

----------------------------------------- At the start: Uptime: 10 Threads: 2 Questions 1000 Slow Queries: 0 Open Tables:0 Flush Tables: 1 Open Tables: 0 Queries per second: 0.1

At the end: Uptime: 1232 Threads: 99 Questions 2798 Slow Queries: 0 Open Tables:21 Flush Tables: 1 Open Tables: 0 Queries per second: 0.265

----------------------------------------------------------------------------

----------------------------------------- The Tomcat Error looks like pops up when connections reach 100:

----------------------------------------------------------------------------

-----------------------------------------

Java.SQL.SQLException: Communication failure during handshake. Is there a server running on localhost:3306

... there are a bunch of lines about Poolman, codestudio, then... com.bygo.SQLBean.makeConnectionObj(SQLBean.java 124)

----------------------------------------------------------------------------

----------------------------------------- mysqladmin -u -processlist -- some entries

----------------------------------------------------------------------------

-----------------------------------------

binlogcache size: 32468 connect_timout 5 delayed_queue_size 1000 flush OFF flush_time 1800 interactive_timeout 28800 max_connections 100 max_connect_errors 10 max_user_connections 0 thread_cache_size 0 wait_timeout 28800

----------------------------------------------------------------------------

----------------------------------------- Hopefully, I've included all of the relevant code/output required for help. Please let me know if you require a bit more information.

Thank you for your help,

-----------------------------