Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Batch update problem with nested select sql  RSS feed

 
Rehan Md.
Greenhorn
Posts: 1
  • 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
  • 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.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!