Eclipse Community Forums: EclipseLink » Stored Procedure call

Help

Home

Home » Eclipse Projects » EclipseLink » Stored Procedure call - Autocommit behavior

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

Stored Procedure call - Autocommit behavior [message #523845]

Mon, 29 March 2010 10:28

Thomas Haskes

Messages: 147
Registered: July 2009

Senior Member

Hi all,

I'm calling a stored procedure that changes some data in the database
using the following code, and i noticed that the procedure call the way
I use it does a commit after being executed. The problem is that I dont
want the session to be autocommitted at all. How can I achieve that?
Here is the code I use:

JpaEntityManager jpaEm = JpaHelper.getEntityManager(tm.getEntityManager());
Session session = jpaEm.getActiveSession();
StoredProcedureCall procedureCall = new StoredProcedureCall();
procedureCall.addNamedArgument("Blabla", "Blabla", String.class);
DataReadQuery query = new DataReadQuery();
query.setCall(procedureCall);
ArrayList<Object> args = new ArrayList<Object();
query.addArgument("Blabla");
args.add("SomeValue");
session.executeQuery(query, args);

Besides: Is it possible to have EL decide whether data is manipulated in
the procedure and, if so, throw an Exception when outside of a transaction?

Thanks in advance

Tom

Report message to a moderator

Re: Stored Procedure call - Autocommit behavior [message #523993 is a reply to message #523845]

Tue, 30 March 2010 08:34

Thomas Haskes

Messages: 147
Registered: July 2009

Senior Member

Am 29.03.2010 17:19, schrieb Thomas Haskes:
Hi again,

I think I need to give some more information about this. My use case is
that I have a three tier application where standard jpa is use mostly,
but I need to reuse some database code that runs in stored procedures,
and I need every logged in user to use his own EM, because the stored
procedures track the user account that called it. To achive that, I
switched off the connection pool and used exclusive connections. Now
every user that logs in has his own em. This works so far. Now I want to
be able to call some stored procedures within the exact same database
session the exclusive em uses, and I down't want this session to
commited after a SP was called, because that user may decide to roll
back all changes he made (including the ones using the "normal" jpa EM)
later.

I read that starting a transaction should turn the autocommit off, I
tried it, but it didnt work for me. No matter what I do, after calling
the procedure i can see the changed data in the database. I think this
might have to do with the way I am acquiring the session in the code
below - but I dont know a better way. It seems that getActiveSession
does not return the exact same session that the em is using. Anyway, I'm
stuck. I tried the other methods the jpaEm-object offer me to get a
session, but non of them seem right.

Any help is appreciated, thanks

Tom

> JpaEntityManager jpaEm = JpaHelper.getEntityManager(tm.getEntityManager());
> Session session = jpaEm.getActiveSession();

Report message to a moderator

Re: Stored Procedure call - Autocommit behavior [message #524041 is a reply to message #523993]

Tue, 30 March 2010 08:00

Thomas Haskes

Messages: 147
Registered: July 2009

Senior Member

Am 30.03.2010 10:34, schrieb Thomas Haskes:

I found a solution here [1]. It was first to start an entitytransaction
on the EM and the issue an beginEarlyTransaction() on the session., now
it works. The changes are sent to the database when commit is issued on
the EM.

But still this leaves me worried. What if a developer accidentally
calles a procedure that does make changes many times without noticing it
does (because he never started a transaction). This would all be
commited. I would expect EL to throw an exception when a procedure tries
to make changes to the database outside of a transaction.

Can this somehow be achieved? I would feel much safer having the
autocommit turned off. The scenario dexcribed above horrifies me. I read
in many posts that turning the autocommit off shouldnt be done (but I
couldnt find how to do that anyway). Why is that?

Greets, Tom

Report message to a moderator

Re: Stored Procedure call - Autocommit behavior [message #524049 is a reply to message #524041]

Tue, 30 March 2010 08:22

Thomas Haskes

Messages: 147
Registered: July 2009

Senior Member

Am 30.03.2010 14:36, schrieb Thomas Haskes:
> Am 30.03.2010 10:34, schrieb Thomas Haskes:
>
> I found a solution here [1]. It was first to start an entitytransaction
> on the EM and the issue an beginEarlyTransaction() on the session., now
> it works. The changes are sent to the database when commit is issued on
> the EM.
>
>
> But still this leaves me worried. What if a developer accidentally
> calles a procedure that does make changes many times without noticing it
> does (because he never started a transaction). This would all be
> commited. I would expect EL to throw an exception when a procedure tries
> to make changes to the database outside of a transaction.
>
> Can this somehow be achieved? I would feel much safer having the
> autocommit turned off. The scenario dexcribed above horrifies me. I read
> in many posts that turning the autocommit off shouldnt be done (but I
> couldnt find how to do that anyway). Why is that?
>
> Greets, Tom
>

[1] http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg01 399.html

Report message to a moderator

Re: Stored Procedure call - Autocommit behavior [message #524620 is a reply to message #524049]

Thu, 01 April 2010 15:44

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

Hello,

EclipseLink cannot evaluate the procedure and determine if anything is modified - the entire procedure is in the database, so there is nothing to use to make that determination. The only information that can be used must be provided by the user.

Unless you are in a transaction, read queries use the read connection pool. Instead of issuing a read query, procedures that make modifications should be using a DataModifyQuery type query instead - these will be executed within the transaction or a transaction started for them as described in the linked thread. If using JPA, this determination is made by using the executeUpdate instead of the getResult methods.

Best Regards,
Chris

Report message to a moderator

Previous Topic:	PreUpdate not working
Next Topic:	Unexplained Update Statements affecting Concurrency

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:12:25 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter