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 update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "batch update " Watch "batch update " New topic
Author

batch update

pravin karne
Ranch Hand

Joined: Jul 26, 2007
Posts: 31
hi...

i m using batch update with oracle 10 g database ,oracle Thin driver

batch update is working fine..
but problem is
if there is exception in batch update (i.e. BatchUpdateException) then there is no corresponding update in database.

for example

if suppose i m going to insert 5 records into database and there is exception at 3 rd records ...
so first 2 records should be insert into database. but it does not happen..?
in database there is no entry for any records if there is exception...what is reason for that ....

thanks in advance
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Are you doing the whole batch process in one transaction?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
pravin karne
Ranch Hand

Joined: Jul 26, 2007
Posts: 31
no,i m not doing batch update in single transaction
i.e. autocommit is set to true.

even if autocommit is set to false i.e. batch update is in single transaction there is same problem

but if i use just Statement object rather than PrepareStatement then there is no problem. entry for all records until there is no exception


so i think issue is with PreparedStatement.....

Reply


Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Can we see your code?
Medha Jhunjhunwala
Greenhorn

Joined: Sep 06, 2007
Posts: 9
Try to use getUpdateCounts() to debug

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts()
pravin karne
Ranch Hand

Joined: Jul 26, 2007
Posts: 31
ya i tried for that

i used getUpdateCounts() to debug but it return for all update is Statement.EXECUTE_FAILED even last update is failed

i think it should return Statement.EXECUTE_FAILED for only failed updated
and Statement.SUCCESS_NO_INFO for all successful update in single batch

but it did not happen


thanks for reply
pravin karne
Ranch Hand

Joined: Jul 26, 2007
Posts: 31
Originally posted by Paul Sturrock:
Can we see your code?


sure...
my code is some what like

Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbcracle:thin:@10.77.222.118:1521:StratosHearTest","sh_user","sh_user");
String query="insert into SH_Call_Logs (CALLERNUMBER,CALLEDNUMBER,ADCODESEXPOSED,DURATION,TIMEOFDAY,DTMF)values (?,?,?,?,to_date(?,'yyyy/mm/dd:hh:mi:ss am'),?)";
perstmtAnalytics=con.prepareStatement(query);

perstmtAnalytics.setInt(1,123445);
perstmtAnalytics.setInt(2,123445);
perstmtAnalytics.setInt(3,2);
perstmtAnalytics.setInt(4,10);
perstmtAnalytics.setString(5,"2007/08/12");
perstmtAnalytics.setInt(6,0);
perstmtAnalytics.addBatch();
perstmtAnalytics.setInt(1,2234456);
perstmtAnalytics.setInt(2,2234456);
perstmtAnalytics.setInt(3,3);
perstmtAnalytics.setInt(4,10);
perstmtAnalytics.setString(5,"2007/08/13");
perstmtAnalytics.setInt(6,0);
perstmtAnalytics.addBatch();
perstmtAnalytics.setInt(1,323447);
perstmtAnalytics.setInt(2,323447);
perstmtAnalytics.setInt(3,4);
perstmtAnalytics.setInt(4,10);
perstmtAnalytics.setString(5,"2007/08d/14"); // i put error condition with date manually
perstmtAnalytics.setInt(6,0);
perstmtAnalytics.addBatch();
int rows[]=perstmtAnalytics.executeBatch();
[ September 10, 2007: Message edited by: pravin karne ]
krishnamoorthy kitcha
Ranch Hand

Joined: Feb 04, 2006
Posts: 96
Hi

Ater that line

nt rows[]=perstmtAnalytics.executeBatch();

query=null;
perstmtAnalytics=null;

Then try and tell me.
pravin karne
Ranch Hand

Joined: Jul 26, 2007
Posts: 31
it will not insert anything into database

even after that change
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why are you using the to_date() function? Is there any reason why you don;t just bind a Date object?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: batch update
 
Similar Threads
Batch Update in Hibernate
read large CSV file and update the database
Batch update in EJB
Batch updation failure because of duplicate records
Batch Update question