Errors using JDBC batchUpdate with plpgsql function
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.
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:
20150503154455679 ERROR [org.springframework.batch.core.step.AbstractStep]
- Encountered an error executing the step
org.springframework.retry.ExhaustedRetryException: Retry exhausted after
last attempt in recovery path, but exception is not skippable.; nested
exception is org.springframework.dao.DataIntegrityViolationException:
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.
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$5.recover(FaultTolerantChunkProcessor.java:406)
at
org.springframework.retry.support.RetryTemplate.handleRetryExhausted(RetryTemplate.java:435)
at
org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:304)
at
org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:188)
at
org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:217)
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.write(FaultTolerantChunkProcessor.java:423)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
at
org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
at
org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
at
org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at
org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
at
org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
at
org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
at
org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
at
org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
at
org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)
at
org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
at
org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:137)
at
org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
at
org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
at
org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152)
at
org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:131)
at
org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
at
org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
at
org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
at
org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
at
org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127)
at
org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:351)
at
org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:577)
Caused by: org.springframework.dao.DataIntegrityViolationException:
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.
at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
at
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:883)
at dao.writer.TestUserDbItemWriter.write(TestUserDbItemWriter.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at
org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
at
org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy1.write(Unknown Source)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
at
org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
at
org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$3.doWithRetry(FaultTolerantChunkProcessor.java:330)
at
org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:255)
... 26 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 select
test_user_result_insert_func(3, '333333333', 0) was aborted. Call
getNextException to see the cause.
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2692)
at
org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImpl.java:439)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1876)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:898)
at
org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 45 more
Thanks
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, May 3, 2015 at 2:33 PM, Nanker Phelge <n.phelge01@gmail.com> wrote:
inner ex 2 =A result was returned when none was expected.
I don't know what is or is not allowed by JDBC but it is reasonable to
assume that you cannot create batches of SELECT statements. The intent of
batching is to repeatedly execute the same INSERT statement multiple times
while using different values for parameters.
I would suggest probably writing INSERT onto a temporary table and then
writing a plpgsql function that would then process said temporary table.
David J.
Hi,
String sql = "select test_user_result_insert_func(?, ?, ?);";
You can't call functions via JDBC like that. You need to use:
CallableStatement cs = connection.prepareCall("{call func(?,?,?)}");
// Loop starts...
cs.clearParameters();
cs.setString(1, "foo");
cs.setString(2, "bar");
cs.setString(3, "baz");
cs.addBatch();
// Loop ends
cs.executeBatch();
See also: https://jdbc.postgresql.org/documentation/94/callproc.html
Best regards,
-hannes
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hannes Erven schrieb am 04.05.2015 um 12:31:
Hi,
String sql = "select test_user_result_insert_func(?, ?, ?);";
You can't call functions via JDBC like that.
That's perfectly valid - unless the function uses out parameters or ref cursors.
I am however unsure about batched statements here. So trying CallableStatement is definitely worth a shot.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The logs from the server would be useful
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 4 May 2015 at 07:05, Thomas Kellerer <spam_eater@gmx.net> wrote:
Show quoted text
Hannes Erven schrieb am 04.05.2015 um 12:31:
Hi,
String sql = "select test_user_result_insert_func(?, ?, ?);";
You can't call functions via JDBC like that.
That's perfectly valid - unless the function uses out parameters or ref
cursors.I am however unsure about batched statements here. So trying
CallableStatement is definitely worth a shot.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The database function does not use out parameters or a ref cursor, which
was why I was confused. The Java sample I provided is a simplification of
the built-in default logic of the Spring Batch ItemWriter - I put it into
my own implementation class to help with debugging. The root cause seems to
be the JDBC driver thinking that there should not be results because it is
an update and the 'select' influencing that decision. I tried switching
this ItemWriter implementation to use a CallableStatement, and that did
work, but I wanted to understand why the approach I listed in the original
post didn't work.
Thanks
On Mon, May 4, 2015 at 6:05 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Show quoted text
Hannes Erven schrieb am 04.05.2015 um 12:31:
Hi,
String sql = "select test_user_result_insert_func(?, ?, ?);";
You can't call functions via JDBC like that.
That's perfectly valid - unless the function uses out parameters or ref
cursors.I am however unsure about batched statements here. So trying
CallableStatement is definitely worth a shot.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
So we can consider this matter closed ? CallableStatements are necessary
because postgres has to use select to call a function.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 4 May 2015 at 18:06, Nanker Phelge <n.phelge01@gmail.com> wrote:
Show quoted text
The database function does not use out parameters or a ref cursor, which
was why I was confused. The Java sample I provided is a simplification of
the built-in default logic of the Spring Batch ItemWriter - I put it into
my own implementation class to help with debugging. The root cause seems to
be the JDBC driver thinking that there should not be results because it is
an update and the 'select' influencing that decision. I tried switching
this ItemWriter implementation to use a CallableStatement, and that did
work, but I wanted to understand why the approach I listed in the original
post didn't work.Thanks
On Mon, May 4, 2015 at 6:05 AM, Thomas Kellerer <spam_eater@gmx.net>
wrote:Hannes Erven schrieb am 04.05.2015 um 12:31:
Hi,
String sql = "select test_user_result_insert_func(?, ?, ?);";
You can't call functions via JDBC like that.
That's perfectly valid - unless the function uses out parameters or ref
cursors.I am however unsure about batched statements here. So trying
CallableStatement is definitely worth a shot.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general