Re: Errors using JDBC batchUpdate with plpgsql function - Mailing list pgsql-general

On 05/03/2015 02:33 PM, Nanker Phelge wrote:
> I am attempting to setup a Spring Batch ItemWriter to call a function in
> PostgreSQL to insert the provided objects.  I posted the details to
> stackoverflow a month ago
> (http://stackoverflow.com/questions/28971220/spring-batch-itemwriter-error-with-postgresql-function)
> with no answers, and I just found this mailing list.  The Java code for
> the ItemWriter is:
>
> public class TestUserDbItemWriter  implements ItemWriter<TestUser>
> {
>      private JdbcTemplate jdbcTemplate;
>      private int jobId;
>
>      @Override
>      public void write(final List<? extends TestUser> 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;
>          }
>      }
>
>
>
>
> And the database function is a simple insert:
>
>
>
>
> 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;
>
>
>
> Running this code gives the following errors from my println() statements
> :
>
> 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.

I am not a Java programmer, so a lot of the above is beyond me. Still, I
see this:

"inner ex 2 =A result was returned when none was expected."

and wonder if you have more then one test_user_result_insert_func() and
are inadvertently calling the wrong one? Say one that returns a result
not VOID?

>
>
> If I change the sql string in the Java code to just be the INSERT
> statement contained within the function, it executes successfully. So,
> the error seems to be due to how some part of JDBC is interpreting the
> SELECT statement used to call the function. In this case the INSERT
> statement is simple, but this is just an example - in my actual code,
> the INSERT statements will be more complicated, and I would rather have
> that detail within a database function. Is there something I'm missing
> with how batchUpdate() can be used with a database function?  This is
> occurring using JDK 1.7, PostgreSQL JDBC driver JAR
> postgresql-9.3-1102.jdbc4,  PostgreSQL 8.2.15 (under Greenplum 4.2.8.1
> build 2), Spring Batch 2.2.1, and Spring Framework 3.0.5.  Here is the
> full stack trace, if that would be helpful:
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com