Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Update Query stored procedure cannot transparently handle nullable values(Update Query, when remapped to a stored procedure, cannot write nulls)
icon4.gif  Update Query stored procedure cannot transparently handle nullable values [message #881393] Mon, 04 June 2012 13:09 Go to next message
Matej Liszka is currently offline Matej LiszkaFriend
Messages: 12
Registered: March 2012
Junior Member
When a stored procedure in Oracle 10 (ojdbc14) is used to remap generated Update Query of EclipseLink, a serious problem pops up. While the original Update Query coming from EclipseLink can normally set fields to any non-null value and also to null value, the stored procedure cannot distinguish these as the fields that are not being updated, are bound as null values to parameters of the stored procedure as well as the fields that are being updated to null value intentionally (I tested by using default values of SP parameters, all fields were always bound).
Can anybody please suggest how to resolve this design issue for an application that requires use of stored procedure?
Using of special values for re-mapping of null data to constants is not a solution as it would not be transparent (and would be a big issue in case of reference fields). Ignoring all null values will not allow setting values to null.
Please help me to understand what to do in this case. I really do not know what is the right solution.
Re: Update Query stored procedure cannot transparently handle nullable values [message #881655 is a reply to message #881393] Tue, 05 June 2012 02:12 Go to previous messageGo to next message
Marvin Toll is currently offline Marvin TollFriend
Messages: 30
Registered: July 2009
Member
At this stage of the evolution of application development you really really really should stay away from stored procedures.

The latest issue is the inability for stored procedures to take full advantage of multi-core processing... where you divide up the work on an application server and parse it out to a database. ( http;//patternenabled.com/soaj/performance/ )



Marvin Toll
CTO, Pattern Enabled Development
http://PatternEnabled.com
Re: Update Query stored procedure cannot transparently handle nullable values [message #881692 is a reply to message #881655] Tue, 05 June 2012 04:55 Go to previous messageGo to next message
Matej Liszka is currently offline Matej LiszkaFriend
Messages: 12
Registered: March 2012
Junior Member
Thanks for your reaction. You are however talking not about the feature of EclipseLink I am interested in.
There is no discussion that business logic of an application can be implemented on database level (and use of stored procedures in Oracle database is a standard way of implementing business logic).

If you are talking about performance, EclipseLink and all entity frameworks in general help to save time by reducing the amount and complexity of written code but they definitely do not provide the best possible performance.

So, my concern is how to use the feature of Update Call in EclipseLink without loosing most of benefits of EclipseLink. If I cannot find a solution, I would have to use a proprietary Data Access implementation. I still hope that it will not be necessary.
Re: Update Query stored procedure cannot transparently handle nullable values [message #881751 is a reply to message #881692] Tue, 05 June 2012 07:47 Go to previous messageGo to next message
Matej Liszka is currently offline Matej LiszkaFriend
Messages: 12
Registered: March 2012
Junior Member
After doing more investigation I found out that the issue was more complex and not possible to understand at all. Merge operation of a detached entity (coming from a web application) produces very different result when using the built-in update query of EclipseLink (and it works as expected) and very different result when mapping to Update Call stored procedure (many of values are null while they should not be).
I have to give up and stop using merge operations for this moment as they are apparently not working well with stored procedure Update Call mapping.
Re: Update Query stored procedure cannot transparently handle nullable values [message #881862 is a reply to message #881692] Tue, 05 June 2012 11:53 Go to previous messageGo to next message
Marvin Toll is currently offline Marvin TollFriend
Messages: 30
Registered: July 2009
Member
<Matej>

If you are talking about performance, EclipseLink and all entity frameworks in general help to save time by reducing the amount and complexity of written code but they definitely do not provide the best possible performance.

</Matej>

My data indicates that combining multi-core (concurrent) Java processing with JPA2 yields far better performance then sequentially executed stored procedures. And that, you can actually beat COBOL platform performance:

http link: //patternenabled.com/soaj/performance/



Marvin Toll
CTO, Pattern Enabled Development
http://PatternEnabled.com
Re: Update Query stored procedure cannot transparently handle nullable values [message #881957 is a reply to message #881862] Tue, 05 June 2012 14:44 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

My guess is it is a change tracking issue. Can you try adding @ChangeTracking(DEFERRED) to your Entity with the store procedure.

Also include the code you use to set the stored procedure in the descriptor.


James : Wiki : Book : Blog : Twitter
Previous Topic:Update Call stored procedure parameter name length issue
Next Topic:Eclipselink 1.0
Goto Forum:
  


Current Time: Thu Nov 27 07:06:23 GMT 2014

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

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