Thread: 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: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;
}
}
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;
:
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.
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
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
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
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.
The logs from the server would be useful
On 4 May 2015 at 07:05, 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
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.
ThanksOn 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
So we can consider this matter closed ? CallableStatements are necessary because postgres has to use select to call a function.
On 4 May 2015 at 18:06, Nanker Phelge <n.phelge01@gmail.com> wrote:
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.ThanksOn 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