File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Batch Processing of Queries with PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Batch Processing of Queries with PreparedStatement" Watch "Batch Processing of Queries with PreparedStatement" New topic
Author

Batch Processing of Queries with PreparedStatement

Shashank Hiwarkar
Ranch Hand

Joined: Feb 17, 2002
Posts: 32
Hello Everybody,
I am using the batch processing with Statement which is working fine.
But when I try to use the PreparedStatement instead of Statement, rows are not getting affected, can anybody explain me why?
I am attaching my code snippet.

PreparedStatement pstmt=con.prepareStatement("update shashank_experiment set column2='abc2' where column1=?");
for(int i=0;i<3;i++){
pstmt.clearParameters();
pstmt.setInt(1,i);
pstmt.addBatch();
}
int[] updated_cnt=pstmt.executeBatch();


Shashank Hiwarkar<br />SCJP, SCWCD
Shashank Hiwarkar
Ranch Hand

Joined: Feb 17, 2002
Posts: 32
Can anybody help me out in this regards
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

is an exception being thrown? Your code should be updating all rows where column1 IN ( 0, 1, 2 ) and column2 = 'abc2' barring any exceptions being thrown, or you not committing the updates. Also, using the clearParameters() method is redundant since it is called in the setXXX() method if it has been previously set.
Shashank Hiwarkar
Ranch Hand

Joined: Feb 17, 2002
Posts: 32
is an exception being thrown? Your code should be updating all rows where column1 IN ( 0, 1, 2 ) and column2 = 'abc2' barring any exceptions being thrown, or you not committing the updates.
Actually when we use update query, if at all there are zero rows updation then also it will not throw the exception. Above program does not give the exception.... And I am using the
conn.autoCommit(true)... no question of not committing.......
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

"Actually when we use update query, if at all there are zero rows updation then also it will not throw the exception."
that seems odd, are you sure you are not just catching it and doing nothing with it?
"And I am using the
conn.autoCommit(true)... no question of not committing......."
This seems odd too. Usually, autocommit should be off.
From the Oracle Docs:
"Notes[on batching]:
Do not mix standard update batching syntax with Oracle update batching syntax in the same application. The Oracle JDBC driver will throw exceptions when these syntaxes are mixed.
Disable auto-commit mode if you use either update batching model. In case an error occurs while you are executing a batch, this allows you the option of committing or rolling back the operations that executed successfully prior to the error."
You may not be using Oracle, but your DB driver probably recommends the same.
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Batch Processing of Queries with PreparedStatement
 
Similar Threads
maximum open cursors exceeded
Batch Update Exception
PreparedStatement Batch Queries
In-Memory to On-Disk Database
Issue: OutofMemory error with simple application