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]

My connection is OK and setAutoCommit sets as false.

You are checking it in another session.
Whenever you open a connection then a database session opens up, if the setAutoComit(fa lse) 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]

my counter value is 1.

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 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 ?

[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]

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

[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

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

[JosAH]

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

[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

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]

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