PreparedStatement is not working

[kokababu]

Hi

I have to insert multiple rows into a Table. I am inserting rows
using addBatch and executeBatch.
Each row has 5 columns. Four columns of each row contain
same value except last column.
So I am using for loop to set the last column value.

I am using JDK1.5, DB2 and WAS6.1. My connection is OK and setAutoCommit sets as false.

 
String insertStr="INSERT INTO DB.TBLNAME (COL1, COL2, COL3,COL4,COL5) VALUES(?,?,?,?,?)";
 
PreparedStatement pstmt = con.prepareStatement(insertStr);
pstmt.setString(1, COL1);     
pstmt.setString(2, COL2);
pstmt.setString(3, COL3);                              
pstmt.setString(4, COL4);                              
 
int counter[]=null;
for(int i=0; i<vector.size(); i++){                  
    pstmt.setString(5, (String)vector.elementAt(i));
        pstmt.addBatch();
 
        counter=pstmt.executeBatch();                    
}
 
 con.commit();
 pstmt.close();              
 
 

I am not getting any error message or exception but Nothing is inserted in
the table. I am not sure why data is not inserted in the table.

I would be glad if anyone kindly help me to solve this issue.

Thank you

[dmjpro]

@kokababu
You are checking it in another session.
Whenever you open a connection then a database session opens up, if the setAutoComit(false) is done then the modified data reflected to that session only where you did the operations; otherwise the modified data reflected across all database session. If you check in same session then you must get the modified data.

[kokababu]

Hi,
Thank you
I set setAutoComit true and removed commit() from my code.
But still, no data is inserted into the table. I am very confused.

[JosAH]

What do you see when you print the 'counter' array obtained in line #15?

kind regards,

Jos

[kokababu]

my counter value is 1.

[JosAH]

@kokababu
Move that executeBatch() call out of your loop, i.e. swap lines #15 and #16.

kind regards,

Jos

[kokababu]

Thank you,

Now, I am very surprised that not all data is inserted into the table.
I wanted to insert 5 rows data, only last rows has been inserted into table.

[dmjpro]

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

Here is my 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 prepareStatement gets called, then a new set of parameters added to the preparedStatement object. Another call results the addition of another new set of parameters to preparedStatement 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 ?

[JosAH]

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

[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".

[JosAH]

@dmjpro
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

[dmjpro]

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

[JosAH]

@dmjpro
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

[dmjpro]

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

[JosAH]

@dmjpro
Sigh, did you move that executeBatch() call below the loop?

kind regards,

Jos

[dmjpro]

@JosAH

No,

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
}
 

[JosAH]

@dmjpro
Then, why are you using the batch facility? You only stick one statement in it and execute it immediately.

kind regards,

Jos

[dmjpro]

@JosAH
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.

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?

[JosAH]

@dmjpro
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

[dmjpro]

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

[JosAH]

@dmjpro
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

[dmjpro]

@dmjpro
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! :(