0

I have a java program that reads thousands of rows of data from a text file into a database. It uses statement batching to insert a couple thousand rows at a time.

If rows any rows fail to update, I continue attempting the rest until the end of the list. After, I go back and check which rows failed, then print the info from those rows to a log file to check later.

I'm wondering if there's a way to skip printing the failed row if it failed because of a duplicate entry. I'm fine with duplicates failing, but if the insert failed for some other reason I need the row to be output to my log file.

The way I currently do this is:

int[] updateCounts;

try {
    // Do my batch updates by iterating through a vector of data
} catch (BatchUpdateException buE) {
    updateCounts = buE.getUpdateCounts();
}

for (int c = 0; c < updateCounts.length; ++c) {
    if (updateCounts[c] == Statement.EXECUTE_FAILED) {
        // Print data from the original data vector at index c 
    }   
}

Instead of printing every row where updateCounts is EXECUTE_FAILED, I only want to print the ones that AREN'T because of a unique key.

Between Google, StackOverflow, and the documentation for BatchUpdateException, I'm guessing there's no way to do this. But it'd be nice if there is and I just couldn't find it.

user1538516
  • 49
  • 1
  • 1
  • 10

1 Answers1

0

There may be two reasons;

  1. There may be a syntax mistake in the SQL Query.
  2. There may be duplicate entries for the primary key.

As you are reading the data from a text file, I suggest you to use the Load Data Local InFile Query in SQL. This would resolve both of the above issues and all your genuine entries would be uploaded into the database.

If this doesn't help, kindly add the queries to your question and the stack trace would be helpful to answer.

halfer
  • 19,824
  • 17
  • 99
  • 186
Arpit Porwal
  • 273
  • 3
  • 14
  • Maybe I wasn't clear in the question: right now the program functionally works how I want it to, but I'm logging more than I'd like.Currently even if one entry fails the ones after it get inserted. It's just that right now I'm logging EVERY failed row, and I'd like to figure out which ones failed because they're duplicates and NOT log those ones since those are fine with me. But you say the only other reason for failure is a syntax error. Since it's pretty unlikely one row had a syntax error, I think I can safely stop logging any of the failed rows and assume they're all duplicates. – user1538516 Oct 09 '16 at 20:29
  • can you please add the stacktrace over here? – Arpit Porwal Oct 09 '16 at 20:37
  • I'm not sure how the stack trace will help... It's a BatchUpdateException and I know from which line it comes. The getMessage() method of BatchUpdateException tells me a row railed due to the unique key, but that message only applies to one (I believe the last) failure. – user1538516 Oct 09 '16 at 20:43