title: Spring Batch ItemWriter error with PostgreSQL function
body: <p>I am attempting to setup a Spring Batch ItemWriter to call a function in PostgreSQL to insert the provided objects. I initially tried using a JdbcBatchItemWriter and specifying the SQL in the configuration, but when that failed, I switched to a custom ItemWriter class to attempt to debug further, but I get the same error.  The following are the components of my setup for my ItemWriter (I'm going to ask that folks assume that the ItemReader works properly, since I have no problems with it, so I will not be providing the details of that implementation):</p>
  
  <p>-Source database table and data:</p>
  
  <pre><code>CREATE TABLE test_user
  (
      test_user_sys_id numeric NOT NULL,
      ssn character varying(9) NOT NULL,
      create_user_id character varying(30) NOT NULL,
      create_ts timestamp(6) without time zone NOT NULL DEFAULT now()
  )
  WITH (
  OIDS=FALSE
  )
  DISTRIBUTED BY (ssn);
  
  INSERT INTO test_user (test_user_sys_id, ssn, create_user_id)  VALUES (1,'111111111','DataAdmin');
  INSERT INTO test_user (test_user_sys_id, ssn, create_user_id)  VALUES (2,'222222222','DataAdmin');
  INSERT INTO test_user (test_user_sys_id, ssn, create_user_id)  VALUES (3,'333333333','DataAdmin');
  </code></pre>
  
  <p>-Target database table:</p>
  
  <pre><code>CREATE TABLE test_user_result
  (
    test_user_result_sys_id numeric NOT NULL,
    ssn character varying(9) NOT NULL,
    job_id numeric NOT NULL
  )
  WITH (
    OIDS=FALSE
  )
  DISTRIBUTED BY (ssn);
  </code></pre>
  
  <p>-Database function to save the results:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION test_user_result_insert_func(
  p_id NUMERIC,
  p_ssn CHARACTER VARYING(9),
  p_job_id NUMERIC
  )
    RETURNS VOID AS
  $BODY$
  
  BEGIN
  INSERT INTO test_user_result (test_user_result_sys_id,ssn,job_id)
  VALUES (p_id,p_ssn,p_job_id);
  END;
  $BODY$
    LANGUAGE plpgsql VOLATILE;
  </code></pre>
  
  <p>-localLaunchContext.xml (envirnoment-specific info):</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xsi:schemaLocation="
                 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"&gt;
  
      &lt;!-- Import the Spring Batch config files --&gt;
      &lt;import resource="springBatchConfig.xml" /&gt;
  
      &lt;!-- Define the PostgreSQL source --&gt;
      &lt;bean id="postgresql_dataSource"
          class="org.apache.commons.dbcp.BasicDataSource"&gt;
          &lt;property name="driverClassName" value="org.postgresql.Driver"/&gt;
          &lt;property name="url" value="jdbc:postgresql://host:port/schema"/&gt;
          &lt;property name="username" value="user"/&gt;
          &lt;property name="password" value="password"/&gt;
          &lt;property name="defaultAutoCommit" value="false"/&gt;
      &lt;/bean&gt;
  
      &lt;!-- Define a resourceless transaction manager for the in-memory job repository --&gt;
      &lt;bean id="repositoryTransactionManager"
      class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" /&gt;
  
      &lt;!-- Define a transaction manager for the data source --&gt;
      &lt;bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;
          &lt;property name="dataSource" ref="postgresql_dataSource"/&gt; 
      &lt;/bean&gt;
  
      &lt;!--  Define the writer JDBC template --&gt;
        &lt;bean id="outputJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"&gt;    
            &lt;property name="dataSource" ref="postgresql_dataSource"/&gt; 
         &lt;/bean&gt;
  
      &lt;!-- Define in-memory job repository  --&gt;
      &lt;bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"&gt;
          &lt;property name="transactionManager" ref="repositoryTransactionManager"/&gt;
      &lt;/bean&gt;
  
      &lt;!-- Define the synchronous job launcher --&gt;
      &lt;bean id="syncJobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher"&gt;
          &lt;property name="jobRepository" ref="jobRepository" /&gt;
      &lt;/bean&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p>-springBatchConfig.xml (Job info):</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt; 
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:batch="http://www.springframework.org/schema/batch" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:util="http://www.springframework.org/schema/util"
      xsi:schemaLocation="http://www.springframework.org/schema/beans 
                      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
                      http://www.springframework.org/schema/batch 
                      http://www.springframework.org/schema/batch/spring-batch-2.1.xsd
                      http://www.springframework.org/schema/util 
                      http://www.springframework.org/schema/util/spring-util-3.0.xsd"&gt;
  
      &lt;!-- ================================================== --&gt;
      &lt;!-- Components for TestUser Job           --&gt;
      &lt;!-- ================================================== --&gt;
  
      &lt;!-- Test User Stored Procedure ItemReader --&gt;
      &lt;bean id="testUserItemReader"
          class="org.springframework.batch.item.database.StoredProcedureItemReader"
          scope="step"&gt;
          &lt;property name="dataSource" ref="postgresql_dataSource" /&gt;
          &lt;property name="procedureName" value="get_user_func_no_arg" /&gt;
          &lt;property name="parameters"&gt;
              &lt;list&gt;
                  &lt;bean class="org.springframework.jdbc.core.SqlParameter"&gt;
                      &lt;constructor-arg index="0" value="p_id_min" /&gt;
                      &lt;constructor-arg index="1"&gt;
                          &lt;util:constant static-field="java.sql.Types.NUMERIC" /&gt;
                      &lt;/constructor-arg&gt;
                  &lt;/bean&gt;
                  &lt;bean class="org.springframework.jdbc.core.SqlOutParameter"&gt;
                      &lt;constructor-arg index="0" value="resultCursor" /&gt;
                      &lt;constructor-arg index="1"&gt;
                          &lt;util:constant static-field="java.sql.Types.OTHER" /&gt;
                      &lt;/constructor-arg&gt;
                  &lt;/bean&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="refCursorPosition" value="2" /&gt;
          &lt;property name="rowMapper"&gt;
              &lt;bean class="dao.mapper.TestUserRowMapper" /&gt;
          &lt;/property&gt;
          &lt;property name="preparedStatementSetter" ref="preparedStatementSetter" /&gt;
      &lt;/bean&gt;
  
      &lt;!-- ItemProcessor is not needed, since the stored procedure provides the results --&gt;
  
      &lt;!-- TestUser ItemWriter --&gt;
        &lt;bean id="testUserItemWriter" class="dao.writer.TestUserDbItemWriter" scope="step"&gt;
          &lt;property name="jdbcTemplate" ref="outputJdbcTemplate"/&gt;
          &lt;property name="jobId" value="#{stepExecution.jobExecution.id}"/&gt;
      &lt;/bean&gt;
  
      &lt;!-- TestUser Job definition --&gt;
      &lt;batch:job id="TestUserJob" incrementer="jobParametersIncrementer"&gt;
          &lt;batch:step id="TestUser_step1"&gt;
              &lt;batch:tasklet&gt;
                  &lt;batch:chunk reader="testUserItemReader" reader-transactional-queue="true"
                      writer="testUserItemWriter" commit-interval="1" /&gt;
              &lt;/batch:tasklet&gt;
          &lt;/batch:step&gt;
      &lt;/batch:job&gt;
  
      &lt;!-- ================================================== --&gt;
      &lt;!-- Common Beans that are used in multiple scenarios --&gt;
      &lt;!-- ================================================== --&gt;
  
      &lt;!-- Increments the Job ID --&gt;
      &lt;bean id="jobParametersIncrementer" class="org.springframework.batch.core.launch.support.RunIdIncrementer" /&gt;
  
      &lt;!-- Prepared statement setter to provide Tax Year for the stored procedures. --&gt;
      &lt;bean id="preparedStatementSetter" class="dao.setter.TestUserPreparedStatementSetter"
          scope="step"&gt;
          &lt;property name="minId"&gt;
              &lt;value&gt;#{jobParameters['minId']}&lt;/value&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  &lt;/beans&gt;
  </code></pre>
  
  <p>-TestUser.java (domain data object):</p>
  
  <pre><code>package domain;
  
  // Data object to support a user
  public class TestUser {
  
      private int id;
      private String ssn;
      public int getId() {
          return id;
      }
      public void setId(int id) {
          this.id = id;
      }
      public String getSsn() {
          return ssn;
      }
      public void setSsn(String ssn) {
          this.ssn = ssn;
      }
  
      @Override
      public String toString() {
          return "TestUser [id=" + id + ", ssn=" + ssn + "]";
      }
  }
  </code></pre>
  
  <p>-TestUserDbItemWriter.java (custom ItemWriter implementation):</p>
  
  <pre><code>package dao.writer;
  
  import java.util.List;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  
  import org.springframework.batch.item.ItemWriter;   
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.BatchPreparedStatementSetter;
  
  import domain.TestUser;
  
  public class TestUserDbItemWriter  implements ItemWriter&lt;TestUser&gt; 
  {
      private JdbcTemplate jdbcTemplate;
      private int jobId;
  
      @Override
      public void write(final List&lt;? extends TestUser&gt; chunk) throws Exception {
  
          String sql = "select test_user_result_insert_func(?, ?, ?);";
          try
          {
              getJdbcTemplate().setSkipResultsProcessing(true);
              getJdbcTemplate().setSkipUndeclaredResults(true);
              getJdbcTemplate().batchUpdate(sql, 
                  new BatchPreparedStatementSetter() { 
                      @Override
                      public void setValues(PreparedStatement ps, int i) throws SQLException {
                          TestUser testUser = chunk.get(i);
                          ps.setInt(1, testUser.getId());
                          ps.setString(2, testUser.getSsn());
                          ps.setInt(3, getJobId());
                      }
                      @Override
                      public int getBatchSize() {
                          return chunk.size();
                      }
                  });
          }
          catch(org.springframework.dao.DataIntegrityViolationException  ex)
          {
              System.out.println("data integrity ex="+ex.getMessage());
              Throwable innerex = ex.getMostSpecificCause();
              if(innerex instanceof java.sql.BatchUpdateException)
              {
                  java.sql.BatchUpdateException batchex = (java.sql.BatchUpdateException) innerex ;
                  SQLException current = batchex;
                  int count=1;
                     do {
  
                         System.out.println("inner ex " + count + " =" + current.getMessage());
                         count++;
  
                     } while ((current = current.getNextException()) != null);
              }
  
              throw ex;
          }
          catch(Exception  ex)
          {
              System.out.println("ex="+ex.getMessage());
              throw ex;
          }
      }
  
      public JdbcTemplate getJdbcTemplate() {
          return jdbcTemplate;
      }
  
      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
          this.jdbcTemplate = jdbcTemplate;
      }
  
      public int getJobId() {
          return jobId;
      }
  
      public void setJobId(int jobId) {
          this.jobId = jobId;
      }
  }
  </code></pre>
  
  <p>-Invocation command:</p>
  
  <pre><code>java -classpath ".;lib\*;bin" org.springframework.batch.core.launch.support.CommandLineJobRunner localLaunchContext.xml TestUserJob minId=1
  </code></pre>
  
  <p>When I run this command, I get the following results from the println statements in my custom ItemWriter:</p>
  
  <pre><code>data integrity ex=PreparedStatementCallback; SQL [select test_user_result_insert_func(?, ?, ?);]; Batch entry 0 select test_user_result_insert_func(3, '333333333', 0) was aborted.  Call getNextException to see the cause.; nested exception is java.sql.BatchUpdateException: Batch entry 0 select test_user_result_insert_func(3, '333333333', 0) was aborted.  Call getNextException to see the cause.
  inner ex 1 =Batch entry 0 select test_user_result_insert_func(3, '333333333', 0) was aborted.  Call getNextException to see the cause.
  inner ex 2 =A result was returned when none was expected.
  data integrity ex=PreparedStatementCallback; SQL [select test_user_result_insert_func(?, ?, ?);]; Batch entry 0 select test_user_result_insert_func(2, '222222222', 0) was aborted.  Call getNextException to see the cause.; nested exception is java.sql.BatchUpdateException: Batch entry 0 select test_user_result_insert_func(2, '222222222', 0) was aborted.  Call getNextException to see the cause.
  inner ex 1 =Batch entry 0 select test_user_result_insert_func(2, '222222222', 0) was aborted.  Call getNextException to see the cause.
  inner ex 2 =A result was returned when none was expected.
  </code></pre>
  
  <p>This is occuring using JDK 1.7, PostgreSQL JDBC driver JAR postgresql-9.3-1102.jdbc4, and PostgreSQL 8.2.15 (under Greenplum 4.2.8.1 build 2)</p>
  
  <p>I have done a lot investigation and searching online, and I've found the following so far:</p>
  
  <ul>
  <li>Indication that this is due to something in the batchUpdate() chain not liking that 'select' is being used, since it isn't expecting a result. PostgreSQL doesn't have both stored procedures and functions (as Oracle and other databases do), so a function is the only option of which I'm aware</li>
  <li>As the current TestUserDbItemWriter.write() method stands, I've experimented with settings on the JdbcTemplate to skip results and undeclared results, but I see no change in the behavior.</li>
  <li>I've found suggestions to change the SQL string used by the ItemWriter to use 'call' instead of 'select'.  Whenever I attempt that, I get an SQL syntax error.</li>
  </ul>
  
  <p>Although this is a simple example where a function might be considered overkill versus just using the SQL to do the insert, my real application is going to have more complicated data to save across multiple tables, and I would like to be able to use a function to abstract those details from the Java code.  Any suggestions would be appreciated.</p>
  
  <p>Thanks in advance</p>