Batch update problem with nested select sql

[Rehan Md.]

I have a problem with batch update.
This is the code
package provevarie;
import java.sql.*;
import javax.sql.*;
public class ProvaDB {

public static void main(String[] args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc racle:thin:XXXXXXXXXXXXXXXXXX");
conn.setAutoCommit(false);
PreparedStatement pstm2 = conn.prepareStatement("INSERT INTO FATTURE VALUES (?,(SELECT NAME FROM ANAGRAFICA WHERE ID =?),?)");
pstm2.clearBatch();
for(int i = 1; i<4; i++){
pstm2.setInt(1,i);
pstm2.setInt(2,i);
pstm2.setInt(3,1000*i);
pstm2.addBatch();
}
pstm2.executeBatch();
conn.commit();
pstm2.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}

There is 2 tables:
ANAGRAFICA
ID NUMBER (PK)
NAME VARCHAR2

FATTURE
ID NUMBER
NAME VARCHAR2
VALUE NUMBER
FATTURE is empty
ANAGRAFICA is filled with 3 fields
ID NAME
1 Nicola
2 Giacomo
3 Mario

If i run the program the table FATTURE is filled so:
ID NAME VALUE
1 Nicola 1000
2 Nicola 2000
3 Nicola 3000
The problem, as you can see, is that the internal select doesn't return the right value but continue to return the value with id=1.....
Any idea why its happenning and how can this be
overcome??

[Gandhi Raketla]

Hi,
Some times prepared statement caches the parameters we pass.try to use pst.clearParameters() after each iteration, so that cache will be removed.