Solved

Batch queries execution issue

Posted on 2009-07-03
38
256 Views
Last Modified: 2012-05-07
Hi

I am tryning to batch execute a query set of 99000. But when i execute these as batch , it throws an exception after 4000 queries saying comma not found and dont execute rest of the queries.

I want that my rest of the queries should be executed. How to do it. All the falied queries should be listed in my logger but  these should not stop from executing other queries..
0
Comment
Question by:rajeevy69
  • 17
  • 12
  • 8
38 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776120
You need to handle BatchUpdateException properly - how are you doing that currently?
0
 

Author Comment

by:rajeevy69
ID: 24776133

for(i=0;i< queries.lenght;i++)
                 stmt.addBatch(queries[i])

try
{
stmt.BatchExecute();
}
catch(SQLException e)
{
    e.printStackTrace();
}
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776141
Well, that's not really handling it at all ;-) You need to commit the ones that were successful. Something like

http://www.exampledepot.com/egs/java.sql/BatchUpdate.html?l=rel
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:rajeevy69
ID: 24776198
From the code , it seems like it is either trying commit batch execute if all statements executed fine else rollback all ststements.

But i want to execute all statements in batch. Only thos that were not executed should be rolledbacked and inform me.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776243
>>Only thos that were not executed should be rolledbacked

You don't need to roll back queries that don't execute - only ones that do.

Does your driver/db continue to process after a BatchUpdateException?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776270
In any case, you should probably be calling commit in the exception handler to ensure that successful updates are in fact committed
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776276
.. or you could do that in a finally block
0
 

Author Comment

by:rajeevy69
ID: 24776281
i m waiting for transaction to complete. i will let u knw in a while
0
 

Author Comment

by:rajeevy69
ID: 24776289
hey it rolleback alll statements. there zero rows in db after completion
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776294
See my last comment
0
 
LVL 92

Expert Comment

by:objects
ID: 24776321
you need to disable auto commit otherwise they will all get rolled back.
And catch BatchUpdateException and handle your logging in there
0
 

Author Comment

by:rajeevy69
ID: 24776400
i did commit but now only 3800 records are getting commited. Earlier count was 7000. Total number of records to update are 99000.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776419
Sounds like your driver might not continue on exception. What query are you executing?
0
 

Author Comment

by:rajeevy69
ID: 24776433
insert into table(column1, column2, column3) values('val1','val2','val3')

all queries are insert queries

Let me brief about history.

I have to write a utility to migrate data from logs to database table. Values in logs are comma separated. I m reading them and creating list of queries.

and than i am trying to insert them in database using batch update.

but it seems some of the values from logs were not read well and therefore creating sytax error.
But expect those queries, i want to insert all othr in database.
0
 
LVL 92

Expert Comment

by:objects
ID: 24776437
use the update count array to determine which are failing

you may be better off not doing the updates via jdbc btw, and instead generte a script and execute that
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24776440
You might attach a compressed copy of your log file - it could be useful
0
 
LVL 92

Accepted Solution

by:
objects earned 84 total points
ID: 24776442
> insert into table(column1, column2, column3) values('val1','val2','val3')

you're using a prepared statement to execute it aren't you, and placeholders for the values
0
 

Author Comment

by:rajeevy69
ID: 24777579
i m not using prepared statement.

also can you pls explain how to create and execute script instead of jdbc.

CRHJ,

Sorry,I cant attach log bcs these are prod logs. but i can give u sample format

==================================================
12/31.2009, http://google.com, 123, ,
12/31.2009, http://google.com, 123, , 515
12/31.2009, http://google.com, 123, ,
12/31.2009, http://google.com, 123, , 515







===================================================
0
 
LVL 92

Expert Comment

by:objects
ID: 24778357
> i m not using prepared statement.

you should be, at least for performance reasons.
are all the inserts going into same table?

> also can you pls explain how to create and execute script instead of jdbc.

output the inserts to a file, and execute it as an sql script
0
 

Author Comment

by:rajeevy69
ID: 24782867
Can you pls give me some demo code for script.

Also please provide some way to execute all queries from batch execution even if some fails.
0
 
LVL 92

Expert Comment

by:objects
ID: 24782888
depends on the queries you are using

have you turned off auto commit as I suggested earlier?
0
 

Author Comment

by:rajeevy69
ID: 24783235
Yes i have switched off auto commit. Its not working for me.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24783261
What are you doing now: executing a file import or doing a PreparedStatement insert?
0
 

Author Comment

by:rajeevy69
ID: 24791755
i am doing statement insert. I could not understand file import. Can you pls send code sample for file import too.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24792467
>>Can you pls send code sample for file import too.

That's db specific and not really to do with Java.

Please post your current PreparedStatement code
0
 

Author Comment

by:rajeevy69
ID: 24792549
public void performBatch(Connection conn, List queries) throws SQLException
      {
            String query;
            Statement stmt;
      
            
            try {
                  conn.setAutoCommit(false);
                      stmt = conn.createStatement();
        
            for (int i = 0; i < queries.size(); i++)
            {
                  stmt.addBatch((String)queries.get(i));
      
            }
          
              // Execute the batch
//              int [] updateCounts = stmt.executeBatch();
          
          
              // Since there were no errors, commit
              conn.commit();
          }
            catch (BatchUpdateException e)
            {
              // Not all of the statements were successfully executed
              int[] updateCounts = e.getUpdateCounts();
          
              // Some databases will continue to execute after one fails.
              // If so, updateCounts.length will equal the number of batched statements.
              // If not, updateCounts.length will equal the number of successfully executed statements
              processUpdateCounts(updateCounts);
          
              // Either commit the successfully executed statements or rollback the entire batch
              conn.rollback();
          }
            catch (SQLException e)
            {
                  System.out.println("Query Falied to execute:" + (String)queries.get(i));
          }
          finally
          {
                conn.commit();
          }
      
      }
0
 
LVL 92

Expert Comment

by:objects
ID: 24792567
you rollback the entire batch by the looks, you want to commit the successful ones
and you shouldn't be committing in finally lock, its already been handled
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24792603
You need to see the comment i made at http:#24776270 - you're not doing that - in fact you're doing the opposite
      
0
 
LVL 92

Expert Comment

by:objects
ID: 24792614
though if you you always want commit() all the successful, then you would do the commit() in your finally block, but get rid of the other commits and rollbacks.
0
 

Author Comment

by:rajeevy69
ID: 24792704
I removed rollback from catch and added commit in place of it than also same problem is occuring.

Also i removed commit from everywhere and tried keeping only in finally block, still same issue.

0
 

Author Comment

by:rajeevy69
ID: 24792713
But if when i am executing queries individually instead of batch, most of the queries are getting executed successfully. But it is taking to much of time.
0
 
LVL 92

Assisted Solution

by:objects
objects earned 84 total points
ID: 24792724
have you tried breaking the batch down into smaller batchs. shouldn't need to but worth testing if thats the problem.
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 41 total points
ID: 24792805
If you have a driver/db that doesn't continue on error, there's not much you can do. You'll probably find that a non-batched PreparedStatement insert gets you there faster in the end
0
 

Author Comment

by:rajeevy69
ID: 24998191
Above things still does not resolve issue. I cant say that it is database issue. So please suggest something
0
 

Author Comment

by:rajeevy69
ID: 25502522
No final solution
0
 

Author Comment

by:rajeevy69
ID: 25502524
NA
0
 

Author Closing Comment

by:rajeevy69
ID: 31599727
Did not found solution of the issue. Only came to know the limitation but I still think that there should be some way to solve this issue
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
map related example 6 55
Configure a Bean in an XML file 4 42
Selenium docs api java index 3 48
Need Help! Getting a syntax error and don't understand why 3 31
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question