Eclipse Community Forums: EclipseLink » Update Query stored procedure cannot transparently handle nullable values

Home » Eclipse Projects » EclipseLink » Update Query stored procedure cannot transparently handle nullable values(Update Query, when remapped to a stored procedure, cannot write nulls)

Show: Today's Messages :: Show Polls :: Message Navigator

Update Query stored procedure cannot transparently handle nullable values [message #881393]

Mon, 04 June 2012 09:09

Matej Liszka
Messages: 7
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.

Report message to a moderator

Re: Update Query stored procedure cannot transparently handle nullable values [message #881655 is a reply to message #881393]

Mon, 04 June 2012 22:12

Marvin Toll
Messages: 20
Registered: July 2009

Junior 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

Report message to a moderator

Re: Update Query stored procedure cannot transparently handle nullable values [message #881692 is a reply to message #881655]

Tue, 05 June 2012 00:55

Matej Liszka
Messages: 7
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.

Report message to a moderator

Re: Update Query stored procedure cannot transparently handle nullable values [message #881751 is a reply to message #881692]

Tue, 05 June 2012 03:47

Matej Liszka
Messages: 7
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.

Report message to a moderator

Re: Update Query stored procedure cannot transparently handle nullable values [message #881862 is a reply to message #881692]

Tue, 05 June 2012 07:53

Marvin Toll
Messages: 20
Registered: July 2009

Junior 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

Report message to a moderator

Re: Update Query stored procedure cannot transparently handle nullable values [message #881957 is a reply to message #881862]

Tue, 05 June 2012 10:44

James Sutherland
Messages: 1834
Registered: July 2009

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

Report message to a moderator

Previous Topic:	Update Call stored procedure parameter name length issue
Next Topic:	Eclipselink 1.0

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 08:50:23 EDT 2013