cancel
Showing results for 
Search instead for 
Did you mean: 

BIG problem with database connections / sessions

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

nikolai_tankov
Explorer
0 Kudos

Hi,

Could you try without setAutoCommit(false) or commit the transaction before you close the connection and context.

Best regards,

Nikolai.

Former Member
0 Kudos

Hello Nikolai,

yes, we tried without setAutoCommit(false), but nothing happened. Problem is reported to OSS.

Thanks anyway.

BR, Ivan

Former Member
0 Kudos

Hi,

Could the problem be the first line here

			context.getConnection().setAutoCommit(false);
			
conn = context.getConnection();

As far as I can see the first line retrieves a connection which is never closed, while the second retrieves a new one (which autocommit is not set to false)

Shouldn't this be

		
conn = context.getConnection();
conn.setAutoCommit(false)

Regards

Dagfinn

angel_dichev
Active Participant
0 Kudos

Correct,

Just see you get 2 connections on every round trip, but at the end you close 1 only.

Regards, Angel

Former Member
0 Kudos

Hi,

thanks for you answer, but unfortunately, it's doesn't help, same thing happens.

Best regards,

Ivan

Former Member
0 Kudos

After few suggestions, I changed code a bit.

Here is the complete code, which doesn't work. It seems that no connection is closed which is quite strange.


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_GROUP.NAME AS MATERIAL_GROUP_NAME, ";
		sql += "GEC_MATERIAL.CODE, GEC_MATERIAL.NAME, GEC_MATERIAL.SUPPLIER_ID, GEC_SUPPLIER.NAME AS SUPPLIER_NAME, ";
		sql += "GEC_MATERIAL.SUPPLIER_MCODE, GEC_MATERIAL.SUPPLIER_MGROUP_ID, GEC_SUPP_MAT_GROUP.NAME AS SUPPLIER_MGROUP_NAME, "; 
		sql += "GEC_MATERIAL.PRODUCER_ID, GEC_PRODUCER.NAME AS PRODUCER_NAME, GEC_MATERIAL.UNIT, GEC_MATERIAL.DESCRIPTION, ";
		sql += "GEC_MATERIAL.UNIT_COST, GEC_MATERIAL.CURRENCY, GEC_MATERIAL.EMAIL_SN, GEC_MATERIAL.ACTIVE, ";
		sql += "GEC_MATERIAL.MAT_CATALOG_ID, GEC_MATERIAL.UNIT_COST_LPRICE, GEC_MATERIAL.LINK, GEC_MATERIAL.NSTD_DEL_DATE ";
		sql += "FROM GEC_MATERIAL ";
		sql += "LEFT JOIN GEC_PRODUCER ON GEC_MATERIAL.PRODUCER_ID = GEC_PRODUCER.ID ";
		sql += "LEFT JOIN GEC_MATERIAL_GROUP ON GEC_MATERIAL.MATERIAL_GROUP_ID = GEC_MATERIAL_GROUP.ID ";
		sql += "LEFT JOIN GEC_SUPPLIER ON GEC_MATERIAL.SUPPLIER_ID = GEC_SUPPLIER.ID ";
		sql += "LEFT JOIN GEC_SUPP_MAT_GROUP ON GEC_MATERIAL.SUPPLIER_MGROUP_ID = GEC_SUPP_MAT_GROUP.ID ";
		
		if(where != null && !"".equals(where))
			sql += "WHERE "+where;
		
		try
		{
			java.sql.Connection conn;
			ConnEcat context;
			context = new ConnEcat();
			conn = context.getConnection();
			conn.setAutoCommit(false);

			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.CURRENCY = itr.CURRENCY();
				matOut.DESCRIPTION = itr.DESCRIPTION();
				matOut.EMAIL_SN = itr.EMAIL_SN();
				matOut.ID = itr.ID();
				matOut.MATERIAL_GROUP_ID = itr.MATERIAL_GROUP_ID();
				matOut.MATERIAL_GROUP_NAME = itr.MATERIAL_GROUP_NAME();
				matOut.NAME = itr.NAME();
				matOut.PRODUCER_ID = itr.PRODUCER_ID();
				matOut.PRODUCER_NAME = itr.PRODUCER_NAME();
				matOut.SUPPLIER_ID = itr.SUPPLIER_ID();
				matOut.SUPPLIER_MCODE = itr.SUPPLIER_MCODE();
				matOut.SUPPLIER_MGROUP_NAME = itr.SUPPLIER_MGROUP_NAME();
				matOut.SUPPLIER_NAME = itr.SUPPLIER_NAME();
				matOut.UNIT = itr.UNIT();
				matOut.UNIT_COST = itr.UNIT_COST();
				matOut.NSTD_DEL_DATE = itr.NSTD_DEL_DATE();
				
				vec.add(matOut);
				
			}
			}
			finally {
				stmt.close();
				itr.close();
				conn.close();
			}
		}
		catch (Exception e) {
			System.err.println(e);
		}
		finally {
			context.close();
		}
		}
finally{
	return vec;
}

Gurus?