Batch queries execution issue

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..
rajeevy69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
You need to handle BatchUpdateException properly - how are you doing that currently?
0
rajeevy69Author Commented:

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

try
{
stmt.BatchExecute();
}
catch(SQLException e)
{
    e.printStackTrace();
}
0
CEHJCommented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

rajeevy69Author Commented:
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
CEHJCommented:
>>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
CEHJCommented:
In any case, you should probably be calling commit in the exception handler to ensure that successful updates are in fact committed
0
CEHJCommented:
.. or you could do that in a finally block
0
rajeevy69Author Commented:
i m waiting for transaction to complete. i will let u knw in a while
0
rajeevy69Author Commented:
hey it rolleback alll statements. there zero rows in db after completion
0
CEHJCommented:
See my last comment
0
Mick BarryJava DeveloperCommented:
you need to disable auto commit otherwise they will all get rolled back.
And catch BatchUpdateException and handle your logging in there
0
rajeevy69Author Commented:
i did commit but now only 3800 records are getting commited. Earlier count was 7000. Total number of records to update are 99000.
0
CEHJCommented:
Sounds like your driver might not continue on exception. What query are you executing?
0
rajeevy69Author Commented:
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
Mick BarryJava DeveloperCommented:
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
CEHJCommented:
You might attach a compressed copy of your log file - it could be useful
0
Mick BarryJava DeveloperCommented:
> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rajeevy69Author Commented:
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
Mick BarryJava DeveloperCommented:
> 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
rajeevy69Author Commented:
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
Mick BarryJava DeveloperCommented:
depends on the queries you are using

have you turned off auto commit as I suggested earlier?
0
rajeevy69Author Commented:
Yes i have switched off auto commit. Its not working for me.
0
CEHJCommented:
What are you doing now: executing a file import or doing a PreparedStatement insert?
0
rajeevy69Author Commented:
i am doing statement insert. I could not understand file import. Can you pls send code sample for file import too.
0
CEHJCommented:
>>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
rajeevy69Author Commented:
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
Mick BarryJava DeveloperCommented:
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
CEHJCommented:
You need to see the comment i made at http:#24776270 - you're not doing that - in fact you're doing the opposite
      
0
Mick BarryJava DeveloperCommented:
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
rajeevy69Author Commented:
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
rajeevy69Author Commented:
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
Mick BarryJava DeveloperCommented:
have you tried breaking the batch down into smaller batchs. shouldn't need to but worth testing if thats the problem.
0
CEHJCommented:
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
rajeevy69Author Commented:
Above things still does not resolve issue. I cant say that it is database issue. So please suggest something
0
rajeevy69Author Commented:
No final solution
0
rajeevy69Author Commented:
NA
0
rajeevy69Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.