I added the following line at the end of my method to reset the
connection to autocommit and it seems to be fixed:
connection.setAutoCommit( true );
Scott Semyan
-----Original Message-----
From: Jeremiah Johnson
Sent: Thursday, May 12, 2005 12:44 PM
To: Beehive Developers
Cc: Chad Schoettger; Scott Semyan
Subject: RE: Problem using transactions with JDBC control
Thanks, Scott.
I see that it is the viewOrder action that is actually failing (as
opposed to the confirm where commitOrder takes place). I have attached
logs of Tomcat failing and success in hopes that maybe Chad knows why
Derby is acting the way that it is. Specifically, the connection is
holding on to the auto commit setting that you have given it in between
onRelease / onAcquire calls - that doesn't seem right to me, but perhaps
that is correct.
- jeremiah
Note the setAutoCommit( true ) calls - that is the key difference.
--- Working code for DerbyOrderDao.java
public int addOrder(Order order, Cart cart)
{
int orderId = -1;
java.sql.Connection connection = null;
try {
connection = _dbControl.getConnection();
connection.setAutoCommit( false );
// Add order to DB
_dbControl.addOrder(order);
orderId =
_dbControl.getLastOrderIdForUser(order.getUserId());
if (orderId == -1)
{
// somehow the order didn't get recorded
_logger.error( "Unexpected DAO exception");
try {
connection.rollback();
connection.setAutoCommit( true );
connection = null;
} catch( SQLException re ) {
_logger.error( "rollback
failed with exception", re );
}
throw new DataStoreException("unexpected
database exception");
}
// Add the cart items and update the quantities in the
DB
Item item = null;
Iterator i = cart.getLineItems().iterator();
while (i.hasNext())
{
LineItem lineItem = (LineItem) i.next();
_catalogControl.updateItemQuantity(lineItem.getItem().getItemId(),
lineItem.getQuantity());
_dbControl.addOrderItem(orderId,
lineItem.getItem().getItemId(), lineItem.getQuantity());
}
} catch (SQLException e) {
_logger.error( "Unexpected DAO exception", e );
try {
connection.rollback();
connection.setAutoCommit( true );
connection = null;
} catch( SQLException re ) {
_logger.error( "rollback
failed with exception", re );
}
throw new DataStoreException("unexpected
database exception");
} finally {
if( connection != null ) {
try {
_logger.info( "order tx
committed" );
connection.commit();
connection.setAutoCommit( true
);
} catch (SQLException e) {
_logger.error( "exception
committing tx", e );
throw new
DataStoreException("unexpected database exception committing tx");
}
}
}
return orderId;
}
---
> -----Original Message-----
> From: Scott Semyan
> Sent: Thursday, May 12, 2005 1:33 PM
> To: Beehive Developers
> Subject: RE: Problem using transactions with JDBC control
>
> Here is the full stack trace.
>
> - PreparedStatement: insert into orders (userId, totalPrice,
> creditCard, exprDate, cardType, status, shippingAddress,
> billingAddress) values (?, ?, ?, ?, ?, 'OK', ?, ?) Params: {beehive,
> 18.50, 098098, jgh, Visa, 1, 1}
> - PreparedStatement: select max(orderId) from Orders where userId = ?
> Params: {beehive}
> - PreparedStatement: update Items set inventoryQuantity = ? where
> itemId = ? Params: {1, EST-4}
> - PreparedStatement: insert into orderitems values (?, ?, ?) Params:
> {2, EST-4, 1}
> - Attempting to instantiate SharedFlowControllers for request
> /petstoreWeb/checkout/viewOrder.do
> - PreparedStatement: select orderId, userId, orderDate, totalPrice,
> creditCard,exprDate, cardType, status, shippingAddress, billingAddress
> from Orders where orderId = ? and userId = ? Params: {2, beehive}
> - PreparedStatement: select addressId, userId, name, phone, addr1,
> addr2, city, state, zip, country from Addresses where addressId = ?
> Params: {1}
> - PreparedStatement: select addressId, userId, name, phone, addr1,
> addr2, city, state, zip, country from Addresses where addressId = ?
> Params: {1}
> - PreparedStatement: select o.itemId, productId, listPrice, unitCost,
> supplier, status, attr1, inventoryQuantity as Qty, o.quantity from
> OrderItems o, Items i where o.orderId = ? and o.itemId = i.itemId
> Params: {2}
> - Label: expression null resolved to null, using empty string.
> - Handling uncaught Throwable
> org.apache.beehive.controls.api.ControlException
> - Could not find exception handler method handleException for
> org.apache.beehive.controls.api.ControlException.
> - Could not find exception handler method handleException for
> java.lang.RuntimeException.
> [/petstoreWeb] Unhandled exception caught in SharedFlow.jpfs:
> org.apache.beehive.controls.api.ControlException: SQL Exception while
> attempting to close database connection.[Invalid transaction state.]
> at
> org.apache.beehive.controls.system.jdbc.JdbcControlImpl.onRelease(Jdbc
> Co
> ntrolImpl.java:133)
> at
> org.apache.beehive.controls.system.jdbc.JdbcControlImplInitializer$_re
> so
> urceContextResourceEventsEventAdaptor.onRelease(JdbcControlImplInitial
> iz
> er.java:20)
> at
> org.apache.beehive.controls.runtime.bean.ResourceContextImpl.release(R
> es
> ourceContextImpl.java:144)
> at
> org.apache.beehive.controls.runtime.bean.ControlContainerContext.relea
> se
> Resources(ControlContainerContext.java:136)
> at
> org.apache.beehive.controls.runtime.bean.ControlContainerContext.endCo
> nt
> ext(ControlContainerContext.java:64)
> at
> org.apache.beehive.controls.runtime.servlet.ServletBeanContext.endCont
> ex
> t(ServletBeanContext.java:92)
> at
> org.apache.beehive.netui.pageflow.internal.JavaControlUtils.uninitiali
> ze
> ControlContext(JavaControlUtils.java:85)
> at
> org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlow
> Pa
> geFilter.java:224)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:186)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
> tc
> her.java:704)
> at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDi
> sp
> atcher.java:590)
> at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDisp
> at
> cher.java:510)
> at
> org.apache.beehive.netui.tags.template.IncludeSection.callDefault(Incl
> ud
> eSection.java:279)
> at
> org.apache.beehive.netui.tags.template.IncludeSection.doStartTag(Inclu
> de
> Section.java:233)
> at
> org.apache.jsp.site.template_jsp._jspx_meth_netui$1template_includeSec
> ti
> on_1(template_jsp.java:312)
> at
> org.apache.jsp.site.template_jsp._jspx_meth_netui_body_0(template_jsp.
> ja
> va:232)
> at
> org.apache.jsp.site.template_jsp._jspx_meth_netui_html_0(template_jsp.
> ja
> va:163)
> at
> org.apache.jsp.site.template_jsp._jspService(template_jsp.java:86)
> at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.
> ja
> va:324)
> at
>
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
> at
> org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:237)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.beehive.netui.pageflow.PageFlowPageFilter.runPage(PageFlowP
> ag
> eFilter.java:279)
> at
> org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlow
> Pa
> geFilter.java:204)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:186)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
> tc
> her.java:704)
> at
> org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDi
> sp
> atcher.java:590)
> at
> org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDisp
> at
> cher.java:510)
> at
> org.apache.beehive.netui.tags.template.Template.doEndTag(Template.java
> :2
> 80)
> at
> org.apache.jsp.checkout.viewOrder_jsp._jspx_meth_netui$1template_templ
> at
> e_0(viewOrder_jsp.java:232)
> at
>
org.apache.jsp.checkout.viewOrder_jsp._jspService(viewOrder_jsp.java:97)
> at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.
> ja
> va:324)
> at
>
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
> at
> org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:237)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.beehive.netui.pageflow.PageFlowPageFilter.runPage(PageFlowP
> ag
> eFilter.java:279)
> at
> org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlow
> Pa
> geFilter.java:204)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:186)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
> tc
> her.java:704)
> at
> org.apache.catalina.core.ApplicationDispatcher.processRequest(Applicat
> io
> nDispatcher.java:474)
> at
> org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDi
> sp
> atcher.java:409)
> at
> org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDisp
> at
> cher.java:312)
> at
> org.apache.beehive.netui.pageflow.internal.DefaultForwardRedirectHandl
> er
> .forward(DefaultForwardRedirectHandler.java:127)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.doForward(P
> ag
> eFlowRequestProcessor.java:1750)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processForw
> ar
> dConfig(PageFlowRequestProcessor.java:1624)
> at
>
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
> 231)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processInte
> rn
> al(PageFlowRequestProcessor.java:591)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.process(Pag
> eF
> lowRequestProcessor.java:852)
> at
> org.apache.beehive.netui.pageflow.AutoRegisterActionServlet.process(Au
> to
> RegisterActionServlet.java:606)
> at
> org.apache.beehive.netui.pageflow.PageFlowActionServlet.process(PageFl
> ow
> ActionServlet.java:162)
> at
> org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:237)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
> tc
> her.java:704)
> at
> org.apache.catalina.core.ApplicationDispatcher.processRequest(Applicat
> io
> nDispatcher.java:474)
> at
> org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDi
> sp
> atcher.java:409)
> at
> org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDisp
> at
> cher.java:312)
> at
> org.apache.beehive.netui.pageflow.internal.DefaultForwardRedirectHandl
> er
> .forward(DefaultForwardRedirectHandler.java:127)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.doForward(P
> ag
> eFlowRequestProcessor.java:1750)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processForw
> ar
> dConfig(PageFlowRequestProcessor.java:1624)
> at
>
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
> 231)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processInte
> rn
> al(PageFlowRequestProcessor.java:591)
> at
> org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.process(Pag
> eF
> lowRequestProcessor.java:852)
> at
> org.apache.beehive.netui.pageflow.AutoRegisterActionServlet.process(Au
> to
> RegisterActionServlet.java:606)
> at
> org.apache.beehive.netui.pageflow.PageFlowActionServlet.process(PageFl
> ow
> ActionServlet.java:162)
> at
> org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
> ca
> tionFilterChain.java:237)
> at
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
> lt
> erChain.java:157)
> at
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVa
> lv
> e.java:214)
> at
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
> Co
> ntext.java:104)
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
> :5
> 20)
> at
> org.apache.catalina.core.StandardContextValve.invokeInternal(StandardC
> on
> textValve.java:198)
> at
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextVa
> lv
> e.java:152)
> at
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
> Co
> ntext.java:104)
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
> :5
> 20)
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.ja
> va
> :137)
> at
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
> Co
> ntext.java:104)
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.ja
> va
> :118)
> at
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
> Co
> ntext.java:102)
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
> :5
> 20)
> at
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.
> java:109)
> at
> org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
> Co
> ntext.java:104)
> at
> org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
> :5
> 20)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
> at
>
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
> at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:
> 79
> 9)
> at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proces
> sC
> onnection(Http11Protocol.java:705)
> at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
> 57
> 7)
> at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPo
> ol
> .java:683)
> at java.lang.Thread.run(Unknown Source) Caused by: SQL
Exception:
> Invalid transaction state.
> at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java)
> at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java)
> at
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnec
> ti
> on.java)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.close(EmbedConnection.java)
> at
> org.apache.beehive.controls.system.jdbc.JdbcControlImpl.onRelease(Jdbc
> Co
> ntrolImpl.java:131)
> ... 104 more
>
>
> -----Original Message-----
> From: Jeremiah Johnson
> Sent: Thursday, May 12, 2005 11:09 AM
> To: Beehive Developers
> Subject: RE: Problem using transactions with JDBC control
>
> I wonder if the transaction issue is a side effect of some other
> failure. I see that you are logging the exception including stack
> trace
> - could you send that please?
>
> - jeremiah
>
> > -----Original Message-----
> > From: Scott Semyan
> > Sent: Thursday, May 12, 2005 11:46 AM
> > To: Beehive Developers
> > Subject: Problem using transactions with JDBC control
> >
> > I have a new error in the PetStore web sample. I have a section of
> code
> > that uses a transaction to add something to the database. I
> > initially enabled this with the following code:
> >
> > public int addOrder(Order order, Cart cart) {
> > try {
> > java.sql.Connection connection = _dbControl.getConnection();
> > connection.setAutoCommit( false );
> >
> > // Add order to DB
> > // Add the cart items and update the quantities in the DB
> > //connection.commit();
> >
> > } catch (SQLException e) {
> >
> > _logger.error( "Unexpected DAO exception", e );
> > throw new DataStoreException("unexpected database exception");
> >
> > }
> >
> > return orderId;
> > }
> >
> >
> > This used to work fine. Now when I do it, the order is added
> correctly,
> > but the subsequent call to the database (to read the contents of the
> > order just entered) generates the following:
> >
> > Exception: org.apache.beehive.controls.api.ControlException: SQL
> > Exception while attempting to close database connection.[Invalid
> > transaction state.] caused by : SQL Exception: Invalid transaction
> > state.
> >
> >
> > Did anything change recently? Is this the proper way to do
> transactions
> > with the JDBC control?
> >
> > Scott Semyan
>
>
