Ok, I've had a chance to play around with this a bit and here is what is
going on:
Summary:
Adding the con.setAutoCommit(true) fix to your control (that Jeremiah
proposed) is the correct solution for this particular case.
Details:
When a control is used within another control, the control lifecycle
events get propagated from the outer control to the inner control.
The JdbcControl maintains a connection to the database from the point it
receives an onAquire event to the point it receives an onRelease event.
In the petstore sample app a JdbcControl which is inside of another
control will only receive the onRelease event when its parent control is
released.
In this particular case, the connection which the JdbcControl maintains
had its autoCommit value set to false. The connection will retain that
value until:
1) The JdbcControl receives an onRelease event
- OR -
2) The autoCommit value is returned to true by calling
setAutoCommit(true) on the connection.
Based on the structure of your application I would recommend always
explicitly setting autoCommit to true after you have completed your
transaction.
- Hope this helps,
Chad
-----Original Message-----
From: Jeremiah Johnson
Sent: Thursday, May 12, 2005 1:52 PM
To: Beehive Developers
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(JdbcCo
> ntrolImpl.java:133)
> at
>
org.apache.beehive.controls.system.jdbc.JdbcControlImplInitializer$_reso
>
urceContextResourceEventsEventAdaptor.onRelease(JdbcControlImplInitializ
> er.java:20)
> at
>
org.apache.beehive.controls.runtime.bean.ResourceContextImpl.release(Res
> ourceContextImpl.java:144)
> at
>
org.apache.beehive.controls.runtime.bean.ControlContainerContext.release
> Resources(ControlContainerContext.java:136)
> at
>
org.apache.beehive.controls.runtime.bean.ControlContainerContext.endCont
> ext(ControlContainerContext.java:64)
> at
>
org.apache.beehive.controls.runtime.servlet.ServletBeanContext.endContex
> t(ServletBeanContext.java:92)
> at
>
org.apache.beehive.netui.pageflow.internal.JavaControlUtils.uninitialize
> ControlContext(JavaControlUtils.java:85)
> at
>
org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlowPa
> geFilter.java:224)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica
> tionFilterChain.java:186)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatc
> her.java:704)
> at
>
org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDisp
> atcher.java:590)
> at
>
org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispat
> cher.java:510)
> at
>
org.apache.beehive.netui.tags.template.IncludeSection.callDefault(Includ
> eSection.java:279)
> at
>
org.apache.beehive.netui.tags.template.IncludeSection.doStartTag(Include
> Section.java:233)
> at
>
org.apache.jsp.site.template_jsp._jspx_meth_netui$1template_includeSecti
> 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(Applica
> tionFilterChain.java:237)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.beehive.netui.pageflow.PageFlowPageFilter.runPage(PageFlowPag
> eFilter.java:279)
> at
>
org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlowPa
> geFilter.java:204)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica
> tionFilterChain.java:186)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatc
> her.java:704)
> at
>
org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDisp
> atcher.java:590)
> at
>
org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispat
> 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_templat
> 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(Applica
> tionFilterChain.java:237)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.beehive.netui.pageflow.PageFlowPageFilter.runPage(PageFlowPag
> eFilter.java:279)
> at
>
org.apache.beehive.netui.pageflow.PageFlowPageFilter.doFilter(PageFlowPa
> geFilter.java:204)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica
> tionFilterChain.java:186)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatc
> her.java:704)
> at
>
org.apache.catalina.core.ApplicationDispatcher.processRequest(Applicatio
> nDispatcher.java:474)
> at
>
org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDisp
> atcher.java:409)
> at
>
org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispat
> cher.java:312)
> at
>
org.apache.beehive.netui.pageflow.internal.DefaultForwardRedirectHandler
> .forward(DefaultForwardRedirectHandler.java:127)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.doForward(Pag
> eFlowRequestProcessor.java:1750)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processForwar
> dConfig(PageFlowRequestProcessor.java:1624)
> at
>
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
> 231)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processIntern
> al(PageFlowRequestProcessor.java:591)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.process(PageF
> lowRequestProcessor.java:852)
> at
>
org.apache.beehive.netui.pageflow.AutoRegisterActionServlet.process(Auto
> RegisterActionServlet.java:606)
> at
>
org.apache.beehive.netui.pageflow.PageFlowActionServlet.process(PageFlow
> 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(Applica
> tionFilterChain.java:237)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatc
> her.java:704)
> at
>
org.apache.catalina.core.ApplicationDispatcher.processRequest(Applicatio
> nDispatcher.java:474)
> at
>
org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDisp
> atcher.java:409)
> at
>
org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispat
> cher.java:312)
> at
>
org.apache.beehive.netui.pageflow.internal.DefaultForwardRedirectHandler
> .forward(DefaultForwardRedirectHandler.java:127)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.doForward(Pag
> eFlowRequestProcessor.java:1750)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processForwar
> dConfig(PageFlowRequestProcessor.java:1624)
> at
>
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
> 231)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processIntern
> al(PageFlowRequestProcessor.java:591)
> at
>
org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.process(PageF
> lowRequestProcessor.java:852)
> at
>
org.apache.beehive.netui.pageflow.AutoRegisterActionServlet.process(Auto
> RegisterActionServlet.java:606)
> at
>
org.apache.beehive.netui.pageflow.PageFlowActionServlet.process(PageFlow
> 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(Applica
> tionFilterChain.java:237)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
> erChain.java:157)
> at
>
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValv
> e.java:214)
> at
>
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
> ntext.java:104)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:5
> 20)
> at
>
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardCon
> textValve.java:198)
> at
>
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValv
> e.java:152)
> at
>
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
> ntext.java:104)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:5
> 20)
> at
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java
> :137)
> at
>
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
> ntext.java:104)
> at
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java
> :118)
> at
>
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveCo
> 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(StandardValveCo
> 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.processC
> 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(ThreadPool
> .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(EmbedConnecti
> on.java)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.close(EmbedConnection.java)
> at
>
org.apache.beehive.controls.system.jdbc.JdbcControlImpl.onRelease(JdbcCo
> 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
>
>
