Win a copy of Soft Skills: The software developer's life manual this week in the Jobs Discussion forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Batch update problem with nested select sql

 
Rehan Md.
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Some times prepared statement caches the parameters we pass.try to use pst.clearParameters() after each iteration, so that cache will be removed.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic