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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
