PreparedStatement is not working

Collapse
X
 
  • Time
  • Show
Clear All
new posts

  • dmjpro
    replied
    Originally posted by dmjpro
    Does executeBatch clear the previous set of parameters ?
    What's this? Actually i wanted to know whether executeBatch does the job like clearBatch after query execution. Anyway if i did anything wrong then sorry! :(

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    Yeah i also think ;) Actually that's what i already said. Just i needed to confirm from the experts ;)
    You didn't say anything; you were just obfuscating the entire thing by executing a batch at every loop pass (and ignoring the tip I gave about it in reply #6). You also just printed the length of the counter array which only reflects the number of statements in the batch. Next you added a uniqueness constraint on your table and were flabbergasted that nothing (non-unique) was added to your table.

    Please read the replies from the 'experts' before you start to experiment in the wild.

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Yeah i also think ;) Actually that's what i already said. Just i needed to confirm from the experts ;)

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    I am getting counter 3 at each print. Then why clearBatch, where it comes in importance?
    You are just printing the array length; also print the value of each element. The clearBatch() method doesn't do much: it clears the entire batch, that's all; I suspect that a batch will be cleared after an executeBatch() has completed.

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Originally posted by JosAH
    Then, why are you using the batch facility? You only stick one statement in it and execute it immediately.

    kind regards,

    Jos
    lolz! I expected the answer exactly what you given :)
    Actually just i am just testing what happens. But my question is if i do like this then how does it work? I am not clearing batch then each time how it prints counter "one".

    Now i tested out this code.
    Code:
    for(int i=0; i<vector.size(); i++){       
                pstmt.setString(3, (String)vector.elementAt(i));
                pstmt.addBatch();
            }
            counter=pstmt.executeBatch();
            System.out.println("Counter: " + counter.length);
            for(int i=0; i<vector.size(); i++){       
                pstmt.setString(3, (String)vector.elementAt(i));
                pstmt.addBatch();
            }
            counter=pstmt.executeBatch();
            System.out.println("Counter: " + counter.length);
    I am getting counter 3 at each print. Then why clearBatch, where it comes in importance?

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    No,
    Then, why are you using the batch facility? You only stick one statement in it and execute it immediately.

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Originally posted by JosAH
    Sigh, did you move that executeBatch() call below the loop?

    kind regards,

    Jos

    No,
    Code:
    for(int i=0; i<vector.size(); i++){       
    pstmt.setString(3, (String)vector.elementAt(i));
    pstmt.addBatch();
    counter=pstmt.executeBatch();
    System.out.println("Counter: " + counter); //this prints 1
    }

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    See the code was same as it was, simply i removed the primary key.
    And got the counter as 1 on each loop pass.
    Sigh, did you move that executeBatch() call below the loop?

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Originally posted by JosAH
    Were three rows inserted in your database? Did you remove the executeBatch() call from your loop (and moved it after the end of the loop)? What is the content of the 'counter' array at each loop pass?

    You have to dissect your problem to be able to find what your problem actually is.

    kind regards,

    Jos
    See the code was same as it was, simply i removed the primary key.
    And got the counter as 1 on each loop pass.

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    I removed the Primary Key, but it shown me same result every time it inserted one row.
    Were three rows inserted in your database? Did you remove the executeBatch() call from your loop (and moved it after the end of the loop)? What is the content of the 'counter' array at each loop pass?

    You have to dissect your problem to be able to find what your problem actually is.

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Originally posted by JosAH
    That makes sense as well: you execute the batch everytime your program runs through your loop. Everything could've been found in the API documentation. You are trying to add the same row multiple times (check what you want to add each time through your loop).

    kind regards,

    Jos
    I removed the Primary Key, but it shown me same result every time it inserted one row.

    Leave a comment:


  • JosAH
    replied
    Originally posted by dmjpro
    Yeah I have the unique key set for the table, then what would be the explaination ;)
    And i printed counter.length, and i got each result "one".
    That makes sense as well: you execute the batch everytime your program runs through your loop. Everything could've been found in the API documentation. You are trying to add the same row multiple times (check what you want to add each time through your loop).

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    Yeah I have the unique key set for the table, then what would be the explaination ;)
    And i printed counter.length, and i got each result "one".

    Leave a comment:


  • JosAH
    replied
    Is there a unique key set for that table? If so, that would explain a lot. btw, Did you print out that 'counter' array at every loop pass?

    kind regards,

    Jos

    Leave a comment:


  • dmjpro
    replied
    First of all sorry, I couldn't see your "con.commit" statement ;)
    I ran your code it's running successfully.

    Here is my code ...
    Code:
    Vector vector = new Vector();
            vector.add("1");
            vector.add("2");
            vector.add("3");
            
            Connection con = null;
            try{
            DriverManager.registerDriver(new org.postgresql.Driver());
            con = DriverManager.getConnection("","","");
            con.setAutoCommit(false);
            String insertStr="INSERT INTO gadget.test_tab (col2, col3,col1) VALUES(?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(insertStr);
            pstmt.setString(1, "Debasis");
            pstmt.setString(2, "DMJPRO");
    
            int counter[]=null;
            for(int i=0; i<vector.size(); i++){       
                pstmt.setString(3, (String)vector.elementAt(i));
                pstmt.addBatch();
                counter=pstmt.executeBatch();
            }
            con.commit();
            pstmt.close();
            con.close();
    The code is running.

    Now my Question, when prepareStatemen t gets called, then a new set of parameters added to the preparedStateme nt object. Another call results the addition of another new set of parameters to preparedStateme nt object and so on ...
    Finally when i call executeBatch then it executes with all added parameters (added after the clearBatch).
    If i am not wrong then inside loop, first call excuteUpdate gets 1 set of parameters, second call 2 set of parameters and so on ..but still i am having only 3 new rows insertion .. ;)
    Does executeBatch clear the previous set of parameters ?

    Leave a comment:

Working...