Problems with Eclipselink batching statements [message #1057898] |
Thu, 09 May 2013 15:43 |
John Dugo Messages: 13 Registered: October 2011 |
Junior Member |
|
|
In my code, I'm making consecutive calls to a stored procedure (with different params each time) in a single transaction.
Ex:
@Transactional
void callProcedure(String... params){
for (String s : params){
callProcedure(s);
}
}
void callProcedure(String s){
StoredProcedureCall sp = new StoredProcedureCall();
sp.setProcedureName("SomeProcedure");
sp.addNamedArgument("paramName",s);
Query dq = JpaHelper.createQuery(new DataModifyQuery(sp), m_entityManager);
dq.executeUpdate();
}
So, I would think that the following code:
callProcedure("firstParam","secondParam");
Would make 2 calls to the stored procedure (one for each param) when the transaction is committed.
However, it seems like somewhere down the line, those 2 calls are getting merged into a single call to the procedure, with multiple parameters. Inspecting the statement in org.eclipse.persistence.platform.database.oracle.Oracle10Platform.executeBatch(Statement, boolean) shows a single call with multiple bind params. This causes failures since my procedure is only expecting a single parameter.
A workaround i found is to do a different call after each procedure call in the loop. This forces the stored procedure to be executed multiple times instead of being consolidated into a single call.
Ex:
void callProcedure(String... params){
for (String s : params){
callProcedure(s);
m_entityManager.find(SomeClass.class, 1);
}
}
Is this expected behavior? Is there another way to get past this (other than doing a bogus EntityManager call)?
Using eclipselink 2.3.1.v20111018-r10243 with Spring Transactions (org.springframework.orm.jpa.JpaTransactionManager) and a shared entity manager.
|
|
|
Re: Problems with Eclipselink batching statements [message #1058584 is a reply to message #1057898] |
Tue, 14 May 2013 13:57 |
|
Looks like you have batch writing enabled. Batch writing allow for multiple executions of the same SQL to be executed as a batch with a set of parameters.
Are you getting and error, or just don't understand the log. It could be that your JDBC driver does not support stored procedures with batch writing, in which case you will need to disable batch writing for the call.
In EclipseLink 2.5 there is a query hint to disable batch writing, "eclipselink.jdbc.batch-writing"="false".
James : Wiki : Book : Blog : Twitter
|
|
|
|
|
Re: Problems with Eclipselink batching statements [message #1058677 is a reply to message #1058638] |
Tue, 14 May 2013 16:44 |
John Dugo Messages: 13 Registered: October 2011 |
Junior Member |
|
|
So I was incorrect in my previous response. When disabling batch completely, the consecutive calls work fine (was a bug in my code). However below is the output when batch mode is turned on. We can certainly use the query hint as a workaround going forward (when we are able to move 2.5), but this behavior doesn't seem right.
Here is the log from the s/p calls:
[EL Finer]: 2013-05-14 12:29:31.118--ClientSession(1251127504)--Connection(1371161697)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--Begin batch statements
[EL Fine]: 2013-05-14 12:29:31.118--ClientSession(1251127504)--Connection(1371161697)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--BEGIN FD_DDL_UTIL.table_sync(ptable_name=>?); END;
[EL Fine]: 2013-05-14 12:29:31.118--ClientSession(1251127504)--Connection(1371161697)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])-- bind => [CO_PROJECT_CP_FC]
[EL Fine]: 2013-05-14 12:29:31.118--ClientSession(1251127504)--Connection(1371161697)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])-- bind => [CO_PROJECT_CP_FT]
[EL Finer]: 2013-05-14 12:29:31.118--ClientSession(1251127504)--Connection(1371161697)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--End Batch Statements
[EL Fine]: 2013-05-14 12:29:31.12--ClientSession(1251127504)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--SELECT 1 FROM DUAL
[EL Warning]: 2013-05-14 12:29:31.128--UnitOfWork(1687571951)--Thread(Thread[[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)',5,Pooled Threads])--Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: invalid arguments in call
Error Code: 17433
Caused by: java.sql.BatchUpdateException: invalid arguments in call
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1357)
at oracle.jdbc.driver.OraclePreparedStatement.sendBatch(OraclePreparedStatement.java:3905)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.sendBatch(OraclePreparedStatementWrapper.java:1432)
at weblogic.jdbc.wrapper.PreparedStatement_oracle_jdbc_driver_OraclePreparedStatementWrapper.sendBatch(Unknown Source)
at org.eclipse.persistence.platform.database.oracle.Oracle10Platform.executeBatch(Oracle10Platform.java:44)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:860)
... 81 more
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.06331 seconds