cannot execute insert statement two times in WSAD

[Jenny Kalinina]

Hi,
Looks like I have Java/WSAD related problem.
I need to insert into table with big number of columns (69). So I create sql string dynamically to have "insert tblname (columnsName, ...) values (?,?,?,?...)" and then use loop with prepStmt.Set... to set parameter values. It works only one time. When I try to call that business method to insert one more row, it gives the error "IndexOutOfBoundsException" and always on parameter #33, i.e it cannot set parameter #33. I've check the number of parameters is correct and equals to 69. Could somebody helps what the reason of the error is?
Thanks in advance
Output:
[6/2/03 9:39:48:914 EDT] cd190b8 SystemOut O setSQLParameter starts par=33 null
[6/2/03 9:39:48:914 EDT] cd190b8 SystemOut O if block className= java.lang.Stringindex= 33
[6/2/03 9:39:48:914 EDT] cd190b8 SystemOut O Exception in else block: java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
[6/2/03 9:39:48:914 EDT] cd190b8 SystemErr R java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
[6/2/03 9:39:48:914 EDT] cd190b8 SystemErr R at java.util.ArrayList.RangeCheck(ArrayList.java(Compiled Code))
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at java.util.ArrayList.set(ArrayList.java:343)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.countParameter(WSJdbcPreparedStatement.java:355)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.setString(WSJdbcPreparedStatement.java:1206)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at eclientprofile.session.AssignServiceBean.setSQLParm(AssignServiceBean.java:372)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at eclientprofile.session.AssignServiceBean.insertRow(AssignServiceBean.java:671)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at eclientprofile.session.AssignServiceBean.addAssgn(AssignServiceBean.java:558)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at eclientprofile.session.EJSRemoteStatelessAssignService_9ace9c43.addAssgn(EJSRemoteStatelessAssignService_9ace9c43.java:142)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at java.lang.reflect.Method.invoke(Native Method)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at com.ibm.rmi.corba.ServantObjectImpl$3.run(ServantObjectImpl.java:223)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at java.security.AccessController.doPrivileged(Native Method)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at com.ibm.rmi.corba.ServantObjectImpl.invoke(ServantObjectImpl.java:221)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at $Proxy1.addAssgn(Unknown Source)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at eclientprofile.session._AssignService_Stub.addAssgn(_AssignService_Stub.java:328)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at java.lang.reflect.Method.invoke(Native Method)
[6/2/03 9:39:48:924 EDT] cd190b8 SystemErr R at com.ibm.etools.utc.model.ReflectionMethodModel.invoke(ReflectionMethodModel.java:68)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.etools.utc.servlet.InvokeServlet.invoke(InvokeServlet.java:110)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.etools.utc.servlet.InvokeServlet.doPost(InvokeServlet.java:323)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
[6/2/03 9:39:48:934 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:872)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:114)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:187)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
[6/2/03 9:39:48:964 EDT] cd190b8 SystemErr R at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
[6/2/03 9:39:48:974 EDT] cd190b8 SystemErr R at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)

[Carl Trusiak]

A code example may help.

[Jenny Kalinina]

private void insertRow(Connection con ) throws SQLException, Exception  {
    
    PreparedStatement prepStmt = null;
    String insertStatement = null;
    boolean duplicatekey = false;
    //Check that pk is unique:
    try{
        prepStmt =con.prepareStatement("Select * from pre_assignment where wip_id = ?");
        prepStmt.setString(1, assgnmap.get("wip_id").toString());
        ResultSet rs = prepStmt.executeQuery();
        duplicatekey = rs.next();
                rs.close();
        prepStmt.close();
    }
    catch (SQLException e){
    //throw new java.rmi.RemoteException("ejb create error", e);
    }
    
    if (!duplicatekey) {
    
    try{
        insertStatement = getInsertString();
    }
    catch (Exception e){
    System.out.println("insertrow getInsertString() except");
    }
    prepStmt = con.prepareStatement(insertStatement);
    int i = 1;
    Object value = null;
    System.out.println("before while lenghth=" + assgnflds.length);
    while (i < assgnflds.length){
      Object key = assgnflds[i][0];
      String classname = assgnflds[0][i];
      System.out.println("key= " + i + key.toString());
      //System.out.println("class name= " + classname);
      value = assgnmap.get(key);
      System.out.println("value= " + value);
      setSQLParm(prepStmt, i, value, classname);
      i++;
    }
    prepStmt.executeUpdate();
    //System.out.println("metadata= " + (prepStmt.getMetaData()).getColumnCount());
    prepStmt.close();
    prepStmt = null;
    System.out.println("insertrow end");
    }
    else{
    System.out.println("dupkey= true in insertrow method");
    throw new Exception();
    }
}  
//**********************************************  
private String getInsertString() throws Exception{
System.out.println("getInsertString");
    String insert = "insert into pre_assignment (";
    int i = 1;
    
    Object value = null;
    String scolumns = null;
    String svalues = null;
    while (i < assgnflds.length){
      Object key = assgnflds[i][0];
      //System.out.println("key= " + key.toString());
      value = assgnmap.get(key);
      insert = insert + key ;
      if (i < (assgnflds.length-1)) insert = insert + ", ";
      else insert = insert + ") values (";
      i++;
    }
    i = 1;
    while (i < assgnflds.length){
  if (i < (assgnflds.length - 1)) insert = insert + "?,";
  else insert = insert + "?)";
  i++;

    }
    System.out.println("insert= " + insert); 
    System.out.println("getInsertString end");   
    return insert;
}
//***********************************************
private void setSQLParm (PreparedStatement pst, int ind, Object value, String classname) {
     System.out.println("setSQLParameter starts par=" + ind + " " + value);
     try{
         System.out.println("if block className= " + classname + "index= " + ind);
         if (classname == "java.lang.String")
            pst.setString(ind, (String) value);
         else if (classname == "java.sql.Timestamp"){
            System.out.println("else block before set TimeStamp= " + "index= " + ind );
            //System.out.println("colNumber= " + (pst.getMetaData()).getColumnCount());
            pst.setTimestamp(ind, (Timestamp)value);
            System.out.println("else block after set TimeStamp= " + "index= " + ind);
         }
         else if (classname == "java.math.BigDecimal"){
            //System.out.println("if block className BigDecimal");
            //System.out.println("if block pst = " + pst.toString());
            pst.setBigDecimal(ind, ((BigDecimal) value));
         }
         else{
            //System.out.println("others type= " + classname);
         }
     }
       
     catch (SQLException e) {
     System.out.println("SQLException in else block: " + e.toString()); 
     }
     catch (Exception e) {
     System.out.println("Exception in else block: " + e.toString()); 
         e.printStackTrace();
     }
 
     //System.out.println("setSQLParameter end");       
}

Thanks

(edited by Cindy to format code using [ code] and [ /code] tags without the spaces)
[ June 02, 2003: Message edited by: Cindy Glass ]

[Shoba Ramachandran]

You need to clear the parameters after inserting a row and then set the fresh set of parameters again.