Cannot update multiple times using for loop

Question

I have a form that borrows stocks from branchA to branchB. The user can input multiple products and I do this by having a text area split it with (","), and place in it an array. After submitting my form, I insert it in a table and then update the stocks from another table. Here is my code (which I actually placed in a JSP file instead of a normal Java class):

   try {

      for (int iCtr=0; iCtr<idsplit.length; iCtr++) {

          sInsertQuery = "INSERT INTO PULLOUT_REPORTS (control_number, dateofpullout, item_des, item_size, item_qty, pullout_from, pullout_to) values ('"+cn+"','"+po+"','"+idsplit[iCtr]+"','"+issplit[iCtr]+"','"+qtsplit[iCtr]+"','"+fr+"','"+to+"')";
          pInsertPullout = conn.prepareStatement(sInsertQuery);
          pInsertPullout.executeUpdate();

            if (fr.equals("Antipolo") && to.equals("Binangonan")) {
                      sUpdateRecord = "UPDATE maintable SET antip_qty = antip_qty - ? WHERE item_code = ? AND item_size = ?";
                      pUpdateFrom = conn.prepareStatement(sUpdateRecord);
                      pUpdateFrom.setString(1,qtsplit[iCtr]);
                      pUpdateFrom.setString(2,idsplit[iCtr]);
                      pUpdateFrom.setString(3,issplit[iCtr]);
                      pUpdateFrom.addBatch();

                      pUpdateFrom.executeBatch();

                      sUpdateRecord1 = "UPDATE maintable SET binang_qty = binang_qty + ? WHERE item_code = ? AND item_size = ?";
                      pUpdateTo = conn.prepareStatement(sUpdateRecord1);
                      pUpdateTo.setString(1,qtsplit[iCtr]);
                      pUpdateTo.setString(2,idsplit[iCtr]);
                      pUpdateTo.setString(3,issplit[iCtr]);
                      pUpdateTo.addBatch();

                      pUpdateTo.executeBatch();
                  } }
  }

  catch (Exception e) {
     response.sendRedirect("error.jsp");
   }

The first query successfully inserts multiple rows in the table but my second query only updates the first index from the array. I don't know what I could be doing wrong since they're both inside the same loop.

Any help please?

UPDATE: I did this .addBatch and .executeBatch and it work a while, now it doesn't work again. It only updates the first index.

On the "best practices" side, doing this in a JSP is a very bad idea. — Santosh, May 16 '15 at 06:26
Can you print the result of pUpdateFrom.executeUpdate call? It should the number of impacted line by the update — RPresle, May 16 '15 at 06:38
Could you please paste the code in its entirety? This appears to be a partial code snippet. — Santosh, May 16 '15 at 06:38
http://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times — Afsun Khammadli, May 16 '15 at 07:55
I think your `executeBatch()` method must be outside of `for` loop. — Afsun Khammadli, May 16 '15 at 08:10
i'll try this out thanks. @AfsunKhammadli I am really really getting frustrated now — user3211403, May 16 '15 at 08:12
@AfsunKhammadli It does not work. It doesn't perform the update when I put the .executeBatch() outside of for loop. — user3211403, May 16 '15 at 08:17
All this is bad : your first statement is asking for SQL injection, you do batch insert of update for one single value, you have no error logging, and you say this is inside a JSP. But besides of that, if input values are correct, it could work. Do it *by hand* splitting it down : 1/ just output the values for idsplit, qtsplit and issplit 2/ use this values manually in mysql console 3/ use this values in a small java program with hardcoded input and heavy traces. The third step would be a nice MVCE to post here. — Serge Ballesta, May 16 '15 at 08:42

score 0 · Answer 1 · answered May 31 '15 at 16:17

Try something like this snippet (i might ve messed up with types of your variables, because I guessed them.

    try {
     sInsertQuery = "INSERT INTO PULLOUT_REPORTS " +
                    "(control_number, dateofpullout, item_des, item_size, item_qty, pullout_from, pullout_to)" +
                    " values " +
                    "(?,?,?,?,?,?,?)";
      pInsertPullout = conn.prepareStatement(sInsertQuery);
      sUpdateRecord = "UPDATE maintable SET antip_qty = antip_qty - ? WHERE item_code = ? AND item_size = ?";
      pUpdateFrom = conn.prepareStatement(sUpdateRecord);
      sUpdateRecord1 = "UPDATE maintable SET binang_qty = binang_qty + ? WHERE item_code = ? AND item_size = ?";
      pUpdateTo = conn.prepareStatement(sUpdateRecord1);

      for (int iCtr=0; iCtr<idsplit.length; iCtr++) {
          pInsertPullout.setLong(1, cn);
          pInsertPullout.setLong(2, po);
          pInsertPullout.setString(3, idsplit[iCtr]);
          pInsertPullout.setString(4, issplit[iCtr]);
          pInsertPullout.setString(5, qtsplit[iCtr]);
          pInsertPullout.setString(6, fr);
          pInsertPullout.setString(7, to);

          pInsertPullout.executeUpdate();
          pInsertPullout.clearParameters();

            if (fr.equals("Antipolo") && to.equals("Binangonan")) {
                      pUpdateFrom.setString(1,qtsplit[iCtr]);
                      pUpdateFrom.setString(2,idsplit[iCtr]);
                      pUpdateFrom.setString(3,issplit[iCtr]);
                      pUpdateFrom.addBatch();


                      pUpdateTo.setString(1,qtsplit[iCtr]);
                      pUpdateTo.setString(2,idsplit[iCtr]);
                      pUpdateTo.setString(3,issplit[iCtr]);
                      pUpdateTo.addBatch();

                  } 
      }
      pUpdateFrom.executeBatch();
      pUpdateTo.executeBatch();
  }

  catch (Exception e) {
     response.sendRedirect("error.jsp");
  }

and don't forget to cleanup resources afterwards. Close statements and connection in finally{} clause.

Cannot update multiple times using for loop

1 Answers1