7 Replies Latest reply on Oct 2, 2007 4:40 AM by nickarls

Stored procedure call causes connection close

nickarls Sep 19, 2007 5:17 AM

Yep, Hibernate specific but some of you might have come across this in a seam context (no pun intended):

I execute a stored procedure by getting a connection from the (Hibernate) JPA delegate but when the original method exits, my friendly CachedConnectionManager closes the connection. Which isn't very nice in a long running conversation with SMPC.

Is there a better way to execute stored procedures or is there a way to leave the connection open?

12:00:34,082 INFO [CachedConnectionManager] Closing a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@170e652
java.lang.Throwable: STACKTRACE
 at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:290)
 at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:417)
 at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
 at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
 at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:47)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
 at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
 at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:50)
 at $Proxy136.prepareCall(Unknown Source)
 at concept.misc.Repository.getNextOrderNumber(Repository.java:370)

1. Re: Stored procedure call causes connection close

nickarls Sep 28, 2007 3:15 AM (in response to nickarls)

Tried it on the hibernate forum but apparently my question is so trivial that it isn't even worth answering over there ;-)

So, I return: How do you call stored functions in the db from an entitymanager with hibernate? If I get the delegate and from the session a connection and use normal call syntax, the connection is closed and that causes grief for my SMPC.

If I embed the function call in a select statement then I can't do DML in the function :-/
Actions
2. Re: Stored procedure call causes connection close

knuwu Sep 28, 2007 3:48 AM (in response to nickarls)
Hi nickarls

Read chapter "11.3.2. Extended session and automatic versioning" of Seam Reference Manual Version 1.2.1 GA.

You can chance the flushMode of a session by calling:

Session s = (SessionImpl) entityManager.getDelegate(); s.setFlushMode(FlushMode.NEVER);

This prevents seam from closing the session automatically after the request finished

But you have to flush an close the session manually!

s.flush(); s.close();

Greetings Ralph
Actions
3. Re: Stored procedure call causes connection close

knuwu Sep 28, 2007 4:02 AM (in response to nickarls)

Ups! The mentioned chapter is in Hibernate reference documentation version 3.2.0 GA which is used in seam 1.2.1.

There is even a better way if you are working with annotations. See chapter 22.4. Annotations for context demarcation (Seam ref doc 1.2.1 - really ;-))

Just annotate the methode you are calling the SP with this annotation.

@Begin(flushMode=FlushModeType.MANUAL)
Actions

4. Re: Stored procedure call causes connection close

nickarls Sep 28, 2007 5:25 AM (in response to nickarls)

I'm already in a manual-flush-mode conversation.
And I've tried all of

getConversation().changeFlushMode(FlushModeType.MANUAL);
and
sessio.setFlushMode(FlushMode.MANUAL);
and
sessio.setFlushMode(FlushMode.NEVER);
but it still insists on closing:
[CachedConnectionManager] Closing a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@18a9358

5. Re: Stored procedure call causes connection close

nickarls Oct 1, 2007 3:43 AM (in response to nickarls)

I have also played around with the hibernate properties in persistence.xml but no go.

I'd hate to tell the customers "Well, you can run *one* stored function and then you have to start the app again" ;-)
Actions
6. Re: Stored procedure call causes connection close

nickarls Oct 2, 2007 4:36 AM (in response to nickarls)

I tried setting hibernate.connection.release_mode to "after_statement", that didn't do anything. It reacted to "after_transaction" by not closing the connection immediately, unfortunately seam itself closed it (at the end of the current long running conversation?) so that was a no-go either :-/
Actions
7. Re: Stored procedure call causes connection close

nickarls Oct 2, 2007 4:40 AM (in response to nickarls)

In other words: should I just give up and start fresh from the JNDI or is there a way of getting a connection from an entitymangager, run a stored procedure and just happily live on?
Actions

Go to original post