This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Batch update problem with nested select sql
 
Similar Threads
Table not mapped.
Exception "javax.persistence.EntityNotFoundException: Unable to find ParkingSpace with 3
StoredProcedure outParameters oracle
batch update in SQL
incrementing the number