[cfang]

I created a test app using JdbcItemWriter and upsert / merge sql statement:

jsr352/test-apps/upsertWriter at master · jberet/jsr352 · GitHub

The test uses the following db2 upsert / merge statement:

MERGE INTO MOVIES AS tab

USING (VALUES

        (?,?,?,?)

    ) AS merge (rank,tit,grs,opn)

    ON tab.rank = merge.rank

    WHEN MATCHED THEN

        UPDATE SET tab.tit = merge.tit,

                   tab.grs = merge.grs

    WHEN NOT MATCHED THEN

        INSERT (rank,tit,grs,opn)

        VALUES (merge.rank, merge.tit, merge.grs, merge.opn)

The test data is from https://raw.githubusercontent.com/jberet/jsr352/master/jberet-support/src/test/resources/movies-2012.csv

Before running the test, I already added 2 rows into the table.  And after running test:

1 row is updated / merged with batch data item property (#1);

1 row is not matched and so is untouched (#9999);

99 row is not matched and so are inserted as new records (#2 - #100).

The output after running test:

Records after running tests:

1    MARVEL'S THE AVENGERS    623357910.00    TBD

9999    TBD    TBD    TBD

2    THE DARK KNIGHT RISES    448139099.00    2012-07-20

3    THE HUNGER GAMES    408010692.00    2012-03-23

4    SKYFALL    304360277.00    2012-11-09

5    THE HOBBIT: AN UNEXPECTED JOURNEY    303003568.00    2012-12-14

6    THE TWILIGHT SAGA: BREAKING DAWN PART 2    292324737.00    2012-11-16

7    THE AMAZING SPIDER-MAN    262030663.00    2012-07-03

... ...

As you may have noticed, one diff between my sql and yours is, I keep ? markers inside the source table definition, and referencing the source tabel cols in both the matched and non-match sections.

[richardmoore]

I appreciate it. I reworked mine. I was till getting that strange generic message. I took the data and found that it was an insert and the source table had a few columns that were larger than my target table. I was only able to discover that when I ran it through SQL Developer. Is there a way to get the jdbcItemWriter to produce all of the messages from the batch execute so I get the clear message that a value is too large for the column <name>?

[cfang]

we should be able to extract more info from SQLException in JdbcItemWriter class.  It currently jsut throw any exception during sql execution out, but it looks like some sql-specific info is not included in the standard Throwable print.

[cfang]

1. JBERET-310

jdbcItemWriter should output more info from SQLException when jdbc batch update fails