on 03-16-2006 12:03 PM
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
Hi,
Could you try without setAutoCommit(false) or commit the transaction before you close the connection and context.
Best regards,
Nikolai.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.