On 2002.12.26 20:08:06 -0500 Emily Short wrote: > 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?
Many databases actually cache the compiled prepared statements in the server, so attempting to keep prepared statements open yourself has minimal effect. (Actually I don't have direct experience with this, but many db experts have said this. I think I recall reading it in some Oracle documentation). I usually prefer prepared statements because you don't have to do nearly as much string manipulation and data to string conversion and generally type conversions work more reliably. At one time there was some code that attempted to cache prepared statements per physical connection in jboss, but it never worked very well. Most of the database experts said this should be the job of the driver or database server. Also, remember Knuth's quote, "premature optimization is the root of all evil". For something like this you might want to try to measure a speed advantage before you make your code more complicated. david jencks > > 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
