I followed mike's advise but still encounter hangs. My
jsp send info to the servlet and it stops there without closing connection and my codes clearly closes connections in finally. In short my servlet just stop working when the amount of records inserted is too big (>300 rec). below is my updated codes. Please help me someone. As for stored procedure, it is not feasible for my project and maybe will face problems since it is db2 database. Thanks
String prodcode = req.getParameter("prodcode");
String firstno = req.getParameter("firstno");
String lastno = req.getParameter("lastno");
String xeffdate = req.getParameter("effdate");
String s1=xeffdate.substring(0,2);
String s2=xeffdate.substring(3,5);
String s3=xeffdate.substring(6,10);
String effdate=s2+"/"+s1+"/"+s3;
Connection cnt=null;
PreparedStatement pstmt1=null, pstmt2=null, pstmtselcv=null;
Statement statgetdate;
ResultSet rst0=null, rstdate=null;
String strsql=null, strsql2=null, sqlselcv=null, sqlgetdate=null;
int noduplicate = 0;
int ok1=0, ok2=1, okconf=0;
try{
String url = "jdbc

b2

L" + compcode;
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
cnt = DriverManager.getConnection(url, "db2inst1", "063inst1");
//compare effective date
sqlgetdate = "SELECT cseffectivedate FROM cnseries where cseffectivedate='" + effdate + "' AND cscompcode='" + compcode + "'";
statgetdate = cnt.createStatement();
rstdate = statgetdate.executeQuery(sqlgetdate);
while(rstdate.next()){
noduplicate = 2;//got same effdate
}//while
Calendar calendars = Calendar.getInstance();
java.util.Date currentdate = calendars.getTime();
long currentyeartime = currentdate.getTime();
Timestamp currenttime = new Timestamp(currentyeartime);;
if(noduplicate==0) {
int intfirstno = Integer.parseInt(firstno);
int intlastno = Integer.parseInt(lastno);
cnt.setAutoCommit(false);
strsql = "INSERT INTO covernote (cncvnoteno,cncompcode,cneffectivedate,cnprodcode,cnstatus,cninsertdate,cnagentcode) "+
"VALUES(?,?,?,?,?,?,?)";
pstmt2 = cnt.prepareStatement(strsql);
for(int count=intfirstno; count<=intlastno; count++){
String strnum = String.valueOf(count);
String covernoteno = prefixno+strnum;
//validate for same cnno
strsql = "SELECT cncvnoteno FROM covernote WHERE cncvnoteno=?";
pstmtselcv = cnt.prepareStatement(strsql);
pstmtselcv.setString(1,covernoteno);
rst0 = pstmtselcv.executeQuery();
//pstmtselcv.setString(1,compcode);
if(rst0.next()){
//String covernotex = rst0.getString("cncvnoteno");
//if(covernotex !=null)
//covernotex = covernotex.trim();
//if(covernotex.equals(covernoteno)) {
noduplicate = 1;//got same
}//while
if(noduplicate==0) {
pstmt2.setString(1,covernoteno);
pstmt2.setString(2,compcode);
pstmt2.setString(3,effdate);
pstmt2.setString(4,prodcode);
pstmt2.setString(5,"0");
pstmt2.setTimestamp(6,currenttime);
pstmt2.setString(7,"Public");
okconf = pstmt2.executeUpdate();
if(okconf==0)
ok2=0;
}
}//for
} //endif
if( noduplicate==0){
strsql = "INSERT INTO cnseries (cscompcode,csprodcode,csprefix,csfirstno,cscreatedate,cslastno,cseffectivedate) "+
"VALUES(?,?,?,?,?,?,?)";
pstmt1 = cnt.prepareStatement(strsql);
pstmt1.setString(1,compcode);
pstmt1.setString(2,prodcode);
pstmt1.setString(3,prefixno);
pstmt1.setString(4,firstno);
pstmt1.setTimestamp(5,currenttime);
pstmt1.setString(6,lastno);
pstmt1.setString(7,effdate);
ok1 = pstmt1.executeUpdate();
if(ok1==1 && ok2==1){
cnt.commit();
}
else{
cnt.rollback();
}
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=success");
}
else if(noduplicate==1) {
cnt.commit();
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=fail");
}
else if(noduplicate==2) {
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=gotdate");
}
}catch(Exception e){ System.out.println("consub" + e.toString());}
finally{
if(cnt!=null){
try{
cnt.close();
}catch(Exception e){}
}
}
}//if submit
mikec: I put code tags around the code, but that turned out lookin' worse.
[ November 26, 2003: Message edited by: Mike Curwen ]