Sounds like a case for some kind of statement pooling if there is such a thing. I haven't used j2ee for any of this stuff at this level, but my jdbc experience has shown me that reusing prepared statements can be the biggest optimization in many cases (with oracle it was especially so).
rick ----- Original Message ----- From: "Emily Short" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 8:08 PM Subject: RE: [JBoss-user] Connection handle is not currently associated with a ManagedConnection Hi David, Thanks so much for your quick response. Does this mean I should not use PreparedStatements at all, unless doing a batch of queries from a single method? I was hoping that I was increasing efficiency by using the precompiled sql statements over multiple method calls. Is there any way to still take advantage of PreparedStatements in this way? Thanks again, Emily -----Original Message----- From: David Jencks [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 4:44 PM To: [EMAIL PROTECTED] Subject: Re: [JBoss-user] Connection handle is not currently associated with a ManagedConnection You can't keep connections in static variables (possibly unless you configure the use of the datasource as an unshared resource). I recommend not keeping connections open across method calls: it either makes the caching system work harder (if you use non-static instance variables) or produces wrong results (if you don't). You also can't keep statements open across method calss (again, possibly it will work if the use is marked as an unhasred resource). Here's why: The jca spec includes support for container managed security, which includes the ability for the container to supply different security info (user/pw) every time you get to the ejb that is using the connection. Unfortunately, the spec also includes support for holding connection handles over method calls. This means that for every call into your ejb, the jca framework has to associate whatever connection handles you left open with physical connections that are logged in as the correct user. This means that (1) holding a connection in a static variable won't work, the container has no idea what object you expect it to be associated with, and can't supply a physical connection for it. (2) holding a statement open won't work because the connection handle is apt to be associated with a different physical connection the next time you try to use it. The connection pooling framework basically makes trying to hold onto a connection irrelvant and unnecessary. If a static variable worked, it would also defeat connection pooling by holding onto a connection even when it was possibly not being used. david jencks On 2002.12.26 19:21:06 -0500 Emily Short wrote: > Hello all, > > I am getting this exception when I declare the following class > variables: > > private static Connection connection = null; > private static PreparedStatement queryProductSummary = null; > > and then try to execute this code within that class twice in a row (a > message driven bean calls this code): > > public static Document getProductSummary(String participantID, int > productID) throws Exception{ > Document result = null; > > prepareConnection(); > if(queryProductSummary == null){ > log.info("preparing statement"); > queryProductSummary = connection.prepareStatement("SELECT > product_summary_xml " + > " FROM product_summary_xml " + > " WHERE participant_id = ? " + > " AND product_id = ? "); > } > > queryProductSummary.clearParameters(); > queryProductSummary.setString(1, participantID); > queryProductSummary.setInt(2, productID); > > ResultSet rs = queryProductSummary.executeQuery(); > > ... > > return result; > } > > public static void prepareConnection() throws SQLException{ > if(connection == null || connection.isClosed()) > connection = OracleDataSource.getConnection(); > } > > I realize it is good practice to close statements and connections, but > in this case I wanted to keep them open across multiple calls. > > I have been reading in the forums that this seems to be a JBoss issue, > but I want to make sure I am not doing anything blatantly incorrect. I > am using JBoss4.0alpha. > > Here is the exception: > > 16:18:19,047 ERROR [STDERR] java.sql.SQLException: Connection handle is > not currently associated with a ManagedConnection > 16:18:19,047 ERROR [STDERR] at > org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedCon > nection.java:774) > 16:18:19,047 ERROR [STDERR] at > org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(Wrapp > edConnection.java:755) > 16:18:19,047 ERROR [STDERR] at > org.jboss.resource.adapter.jdbc.WrappedStatement.checkTransaction(Wrappe > dStatement.java:771) > 16:18:19,047 ERROR [STDERR] at > org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(Wr > appedPreparedStatement.java:286) > 16:18:19,047 ERROR [STDERR] at > com.scilearn.db.ProductSummary.getProductSummary(ProductSummary.java:204 > ) > 16:18:19,047 ERROR [STDERR] at > com.scilearn.db.ProductSummary.getExerciseSummaries(ProductSummary.java: > 184) > 16:18:19,047 ERROR [STDERR] at > com.scilearn.beans.UploadQueueListener.onMessage(UploadQueueListener.jav > a:119) > 16:18:19,047 ERROR [STDERR] at > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > 16:18:19,047 ERROR [STDERR] at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav > a:39) > 16:18:19,047 ERROR [STDERR] at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor > Impl.java:25) > 16:18:19,047 ERROR [STDERR] at > java.lang.reflect.Method.invoke(Method.java:324) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.MessageDrivenContainer$ContainerInterceptor.invoke(Message > DrivenContainer.java:405) > 16:18:19,057 ERROR [STDERR] at > org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke( > CachedConnectionInterceptor.java:187) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterce > ptor.java:108) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInte > rceptorBMT.java:144) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.MessageDrivenTxInterceptorBMT.invoke(MessageDriven > TxInterceptorBMT.java:33) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.MessageDrivenInstanceInterceptor.invoke(MessageDri > venInstanceInterceptor.java:88) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.RunAsSecurityInterceptor.invoke(RunAsSecurityInter > ceptor.java:100) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryF > inderInterceptor.java:154) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.MessageDrivenContainer.invoke(MessageDrivenContainer.java: > 312) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.jms.JMSContainerInvoker.invoke(JMSContainerInvoker > .java:697) > 16:18:19,057 ERROR [STDERR] at > org.jboss.ejb.plugins.jms.JMSContainerInvoker$MessageListenerImpl.onMess > age(JMSContainerInvoker.java:763) > 16:18:19,057 ERROR [STDERR] at > org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:241) > 16:18:19,057 ERROR [STDERR] at > org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessage > Consumer.java:643) > 16:18:19,057 ERROR [STDERR] at > org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:457) > 16:18:19,057 ERROR [STDERR] at > org.jboss.mq.SpySession.run(SpySession.java:309) > 16:18:19,057 ERROR [STDERR] at > org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:177) > 16:18:19,057 ERROR [STDERR] at > EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecuto > r.java:655) > 16:18:19,057 ERROR [STDERR] at java.lang.Thread.run(Thread.java:536) > > Thanks for you help, > Emily > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf > _______________________________________________ > JBoss-user mailing list > [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/jboss-user > > ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
