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)

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

Mon, 04 June 2012 09:09

Eclipse User

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]

Mon, 04 June 2012 22:12

Eclipse User

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/ )

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

Tue, 05 June 2012 00:55

Eclipse User

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 03:47

Eclipse User

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 07:53

Eclipse User

<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/

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

Tue, 05 June 2012 10:44

Eclipse User

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.

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

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 20:43:26 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter