Skip to Content
0
Former Member
Mar 16, 2006 at 11:59 AM

BIG problem with database sessions

22 Views

Dear all,

we have big problem with part of application.

Scenario should look like this: we get excel files from partners, load it, parse it and update tables with the content. For xls import apache/poi api is used, for database manipulation SQLJ (static statements) and JDBC (dynamic statements) . Tables are created as standard dictionary project. Also, everything is done through NWDI.

Parser is going through every single row of the Excel table and doing lookup in the table where it compares material ID from excel with material ID from DB table. Excel tables can be extremly big, e.g. 15.000 rows, so that means 15.000 lookups per excel table.

Problem is that it seems that system isn't closing connections / sessions to database so J2EE system freeze after 30-40 rows. Number of connections in J2EE engine has been set to 50.

Here is code snippet, which is called for performing lookup function:

public Vector getList(String where)
	{
		MaterialIt itr = null;
		Vector vec = new Vector();
		
		String sql = "SELECT GEC_MATERIAL.ID, GEC_MATERIAL.MATERIAL_GROUP_ID, GEC_MATERIAL.NAME FROM GEC_MATERIAL ";

		if(where != null && !"".equals(where))
			sql += "WHERE "+where;
		
		try
		{
			
			java.sql.Connection conn;
			ConnEcat context;
			context = new ConnEcat();
			context.getConnection().setAutoCommit(false);
			
			conn = context.getConnection();
			java.sql.Statement stmt = conn.createStatement();
			
			try {
			
			java.sql.ResultSet rs = stmt.executeQuery(sql);	
			#sql itr = { CAST :rs };
			
			try{
			while(itr.next())
			{
				Material matOut = new Material();
			
				matOut.ACTIVE = itr.ACTIVE();
				matOut.CODE = itr.CODE();
				matOut.ID = itr.ID();
				matOut.MATERIAL_GROUP_ID = itr.MATERIAL_GROUP_ID();
				matOut.NAME = itr.NAME();
				
				vec.add(matOut);
			}
			}
			finally {
				itr.close();
			}			
		}
		catch (Exception e) {
			System.err.println(e);
		}
		finally {
			context.close();
		
		}
		}
finally{
	return vec;
}

Btw., it's been tested on MaxDB and Oracle, with the same result.

Problem is kind of critical and quite urgent so I'll appreciate fast response and promise to assign points (and buy a beer in Zagreb 😊 for help.

Best regards,

Ivan