Sounds like it would work in general, but in my specific case I'm afraid it doesn't. This is a limitation of the PreparedStatement's implementation, however... Since I am using DB transactions, I always have different connections (transactions) executing the same statements. This means that using a PreparedStatement associated with any old connection won't work. Why is it that PreparedStatements need to be associated with a particular connection anyway?
Emily -----Original Message----- From: David Jencks [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 7:10 PM To: [EMAIL PROTECTED] Subject: Re: [JBoss-user] Connection handle is not currently associated with a ManagedConnection On 2002.12.26 21:22:13 -0500 Rick LaBanca wrote: > 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). > So much for what various "experts" told me about "most" databases and Oracle in particular. I'd think it would be fairly easy to write, except for the bad experiences with the previous version. I'm thinking of a hashmap from sql to object that has PreparedStatement and timestamp, and a thread that periodically removes overaged objects. This would be turned off by default :-) Does that seem like it would work? Thanks david jencks > 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 > > ------------------------------------------------------- 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
