aspose file tools*
The moose likes JDBC and the fly likes Batch update problem with nested select sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Batch update problem with nested select sql" Watch "Batch update problem with nested select sql" New topic
Author

Batch update problem with nested select sql

Rehan Md.
Greenhorn

Joined: Nov 14, 2002
Posts: 1
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

Joined: Nov 11, 2002
Posts: 1
Hi,
Some times prepared statement caches the parameters we pass.try to use pst.clearParameters() after each iteration, so that cache will be removed.
 
 
subject: Batch update problem with nested select sql