Re: Errors using JDBC batchUpdate with plpgsql function

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(dot)klaver(at)aklaver(dot)com