Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problems with Eclipselink batching statements(When making consecutive calls to the same stored procedure in a transaction, exception occurrs)
Problems with Eclipselink batching statements [message #1057898] Thu, 09 May 2013 15:43 Go to next message
John Dugo is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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 #1058597 is a reply to message #1058584] Tue, 14 May 2013 14:15 Go to previous messageGo to next message
John Dugo is currently offline John Dugo
Messages: 13
Registered: October 2011
Junior Member
We are using Oracles batch writing, however, I see the same problems when I disable batch all together.

I just don't understand why 2 stored procedure calls are being turned into 1.

so my procedure looks something like this:

procedure table_sync(ptable_name in varchar2);

When I call the procedure consecutively in a loop (as described in my post)...

instead of this:

table_sync('table1');
table_sync('table2');

the code combines those two calls into this:

table_sync('table1','table2')


Re: Problems with Eclipselink batching statements [message #1058638 is a reply to message #1058597] Tue, 14 May 2013 15:14 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I can't see that occurring. Please enable logging on finest and include the log.


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 Go to previous messageGo to next message
John Dugo is currently offline 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

Re: Problems with Eclipselink batching statements [message #1058694 is a reply to message #1058677] Tue, 14 May 2013 17:44 Go to previous messageGo to next message
John Dugo is currently offline John Dugo
Messages: 13
Registered: October 2011
Junior Member
I can give you a more detailed stack trace if you need it. I just have to make some changes to my code. There are other calls that happen after this in the same transaction and it makes it appear the error is caused by those instead of the procedure.
Re: Problems with Eclipselink batching statements [message #1060015 is a reply to message #1058694] Wed, 22 May 2013 14:28 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The log show it is batching the procedure call, and your database/driver does not support this. You need to disable batch writing before calling this stored procedure.

You could also call flush() after calling it each time to ensure it does not get batched.


James : Wiki : Book : Blog : Twitter
Re: Problems with Eclipselink batching statements [message #1060042 is a reply to message #1060015] Wed, 22 May 2013 15:36 Go to previous message
John Dugo is currently offline John Dugo
Messages: 13
Registered: October 2011
Junior Member
Ok thanks for the help, will experiment with other oracle drivers, but the workaround will be fine for now.
Previous Topic:Modify database schema for class based entities
Next Topic:Call Oracle Function
Goto Forum:
  


Current Time: Sun Oct 26 05:08:53 GMT 2014

Powered by FUDForum. Page generated in 0.13673 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software