7 Replies Latest reply on Mar 2, 2018 12:27 PM by Richard Moore

    jdbcItemWriter

    Richard Moore Master

      I am having a issue inserting anything into one of my tables and have spent about 6 hours trying to figure it out. I have included the writer jsl and data, and the straight java I used that works. For the life of me I can't figure out what is different. This is the first time we have tried to insert something other than String values.

       

      I am getting -

      JBERET000007: Failed to run job AIS_Daily_ScmAllowanceIfaceLoad, loadInterfaceTable, org.jberet.job.model.Chunk@2c1f4842

      com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.16.53] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.

      Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null

       

      My map for the insert is -

      {

      EFFECTIVE_DATE=2018-03-19 00:00:00,

      INT_DEAL_NBR=,

      ACTION_CODE=A,

      REVERT_DATE=2018-04-16 00:00:00,

      ITEM_CD=0010110,

      FACL=00,

      EXT_GRP_ID=K300,

      TIME_STAMP=0001-01-01 00:00:00,

      WHSE=01,

      INT_ARB_NBR=,

      VEN_CD=0003730,

      AMOUNT=28.8000,

      EXT_ARB_ID=,

      USR_DEAL_NUMBER=1127350,

      CLOSED_DATE=,

      STORE_CD=00020

      }

       

      My jsl for the writer -

       

      <writer ref="jdbcItemWriter">

      <properties>

      <property name="dataSourceLookup" value="#{jobProperties['interfaceDataSource']}" />

      <property name="sql" value="

      insert into SCCI_DEAL_EXPENSE (

      CPY_CD

      , ALLNC_REBATE_TYPE

      , PERCENT

      , PCENT_BASIS

      , APPLY_ARB

      , ROW_SOURCE

      , SUB_TYPE

      , ACCOUNT

      , ORDER_TYPE

      , DESCRIPTION

      , PROMO_ID

      , CONTROL_NO

      , ACL_CD

      , DATE_ADDED

      , TIME_ADDED

      , FUNDS_TYPE

      , TIME_STAMP

      , INT_ARB_NBR

      , INT_DEAL_NBR

      , CLOSED_DATE

      , FACL

      , WHSE

      , ITM_CD

      , USR_DEAL_NUMBER

      , EFFECTIVE_DATE

      , REVERT_DATE

      , AMOUNT

      , STORE_CD

      , EXT_GROUP_ID

      , VEN_CD

      , EXT_ARB_ID

      , ACTION_CODE

      ) values (

      'AWG'

      , 'A'

      , 0

      , ' '

      , 'Y'

      , 'AWGTPB'

      , 'RO'

      , ' '

      , ' '

      , 'Promo'

      , 'ADM'

      , '00989898'

      , ' '

      , current date

      , current time

      , 'E'

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      )

      " />

      <property name="parameterNames" value="

      TIME_STAMP

      , INT_ARB_NBR

      , INT_DEAL_NBR

      , CLOSED_DATE

      , FACL

      , WHSE

      , ITM_CD

      , USR_DEAL_NUMBER

      , EFFECTIVE_DATE

      , REVERT_DATE

      , AMOUNT

      , STORE_CD

      , EXT_GROUP_ID

      , VEN_CD

      , EXT_ARB_ID

      , ACTION_CODE

      " />

      <property name="parameterTypes" value="

      String

      , String

      , String

      , String

      , String

      , String

      , String

      , String

      , String

      , String

      , BigDecimal

      , String

      , String

      , String

      , String

      , String

      " />

      <property name="beanType" value="java.util.Map" />

      </properties>

      </writer>

       

      ================================================================================================================

       

      If I run this from a just java it works using the following -

       

      My map data for the parameters -

      record1.put("TIME_STAMP", "0001-01-01 00:00:00");

      record1.put("INT_ARB_NBR", "");

      record1.put("INT_DEAL_NBR", "");

      record1.put("CLOSED_DATE", "");

      record1.put("FACL", "00");

      record1.put("WHSE", "01");

      record1.put("ITM_CD", "0010110");

      record1.put("USR_DEAL_NUMBER", "1127350");

      record1.put("EFFECTIVE_DATE", "2018-03-19 00:00:00");

      record1.put("REVERT_DATE", "2018-04-16 00:00:00");

      record1.put("AMOUNT", new BigDecimal("28.8000"));

      record1.put("STORE_CD", "00020");

      record1.put("EXT_GROUP_ID", "K300");

      record1.put("VEN_CD", "0003730");

      record1.put("EXT_ARB_ID", "");

      record1.put("ACTION_CODE", "A");

       

       

      Based on the type of object in the map (above) I use either -

      stmt.setBigDecimal(position, value);

      stmt.setString(position, value);

       

       

      insert into SCCI_DEAL_EXPENSE (

      CPY_CD

      , ALLNC_REBATE_TYPE

      , PERCENT

      , PCENT_BASIS

      , APPLY_ARB

      , ROW_SOURCE

      , SUB_TYPE

      , ACCOUNT

      , ORDER_TYPE

      , DESCRIPTION

      , PROMO_ID

      , CONTROL_NO

      , ACL_CD

      , DATE_ADDED

      , TIME_ADDED

      , FUNDS_TYPE

      , TIME_STAMP

      , INT_ARB_NBR

      , INT_DEAL_NBR

      , CLOSED_DATE

      , FACL

      , WHSE

      , ITM_CD

      , USR_DEAL_NUMBER

      , EFFECTIVE_DATE

      , REVERT_DATE

      , AMOUNT

      , STORE_CD

      , EXT_GROUP_ID

      , VEN_CD

      , EXT_ARB_ID

      , ACTION_CODE

      ) values (

      'AWG'

      , 'A'

      , 0

      , ' '

      , 'Y'

      , 'AWGTPB'

      , 'RO'

      , ' '

      , ' '

      , 'Promo'

      , 'ADM'

      , '00989898'

      , ' '

      , current date

      , current time

      , 'E'

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , to_date(?, 'YYYY-MM-DD HH24:MI:SS')

      , ?

      , ?

      , ?

      , ?

      , ?

      , ?

      )