3 Replies Latest reply on Aug 19, 2009 2:46 PM by Tanya Ruttenberg

    dataTable with sortBy attempts to UPDATE

    Tanya Ruttenberg Expert

      Seam 2.1.2, Richfaces 3.3.1, mysql back end

      I am trying to implement a richfaces dataTable with sortable fields. However, when I click on one of the headers in an attempt to sort, I get all kinds of errors about not being able to UPDATE my table. I have no wish to update the table and thankfully the username I am using does not have UPDATE priv on the table. But I don't understand why it's even trying to do it!? This is so aggravating. Why would a sort result in an update? And how in the world do I fix it?

      Here is the code:

      <!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
       "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
      <ui:composition xmlns="http://www.w3.org/1999/xhtml"
       xmlns:s="http://jboss.com/products/seam/taglib"
       xmlns:ui="http://java.sun.com/jsf/facelets"
       xmlns:f="http://java.sun.com/jsf/core"
       xmlns:h="http://java.sun.com/jsf/html"
       xmlns:rich="http://richfaces.org/rich"
       template="layout/template.xhtml">
      
       <ui:define name="body">
       <h:form id="tdr">
       <rich:extendedDataTable value="#{officeHome.devices}"
       var="_devices">
       <rich:column sortBy="#{_devices.devName}">
       <f:facet name="header">#{messages['ond.label.devname']}</f:facet>
       <h:outputText value="#{_devices.devName}" />
       </rich:column>
       <rich:column sortBy="#{_devices.devOsVer}">
       <f:facet name="header">#{messages['ond.label.devosver']}</f:facet>
       <h:outputText value="#{_devices.devOsVer}" />
       </rich:column>
       <rich:column sortBy="#{_devices.peRouter}">
       <f:facet name="header">#{messages['ond.label.perouter']}</f:facet>
       <h:outputText value="#{_devices.peRouter}" />
       </rich:column>
       <rich:column sortBy="#{_devices.devName}">
       <f:facet name="header">#{messages['ond.label.devname']}</f:facet>
       <h:outputText value="#{_devices.devName}" />
       </rich:column>
       </rich:extendedDataTable>
       </h:form>
       </ui:define>
      </ui:composition>
      



      Here are highlights from the results:


      12:23:08,815 INFO [STDOUT] Hibernate:
       select
       office0_.office_id as office1_82_0_,
       office0_.addr1 as addr2_82_0_,
      
      <stuff deleted for brevity>
      
       office0_.zip as zip82_0_
       from
       ondtdr.office_view office0_
       where
       office0_.office_id=?
      12:23:08,815 INFO [STDOUT] Hibernate:
       select
       devices0_.office_id as office12_1_,
       devices0_.dev_id as dev1_1_,
      
      <stuff deleted for brevity>
      
      
       devices0_.is_router as is10_81_0_,
       devices0_.smc as smc81_0_
       from
       ondtdr.devices devices0_
       where
       devices0_.office_id=?
      12:23:08,847 INFO [STDOUT] Hibernate:
       update
       ondtdr.office_view
       set
       addr1=?,
       building_id=?,
       circuit_id=?,
      
      <stuff deleted for brevity>
      
      
       videounits=?,
       voip=?,
       wanvendor=?,
       zip=?
       where
       office_id=?
      12:23:08,847 WARN [JDBCExceptionReporter] SQL Error: 1142, SQLState: 42000
      12:23:08,847 ERROR [JDBCExceptionReporter] UPDATE command denied to user 'ondqry'@'s003005.ba.ad.ssa.gov' for table 'office_view'
      12:23:08,847 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
      org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
       at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
      
      <stuff deleted for brevity>
      
      
      Caused by: java.sql.BatchUpdateException: UPDATE command denied to user 'ondqry'@'s003005.ba.ad.ssa.gov' for table 'office_view'
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
       at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
       at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
       at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
       ... 68 more
      12:23:08,847 WARN [arjLoggerI18N] [com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator_2] TwoPhaseCoordinator.beforeCompletion - failed for com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple@1d61358
      javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
       at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
       at org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:513)
       at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:101)
      
      <stuff deleted for brevity>
      
      
      Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
      
      <stuff deleted for brevity>
      
      
       ... 61 more
      Caused by: java.sql.BatchUpdateException: UPDATE command denied to user 'ondqry'@'s003005.ba.ad.ssa.gov' for table 'office_view'
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
      
      <stuff deleted for brevity>
      
      
      12:23:08,847 WARN [SeamPhaseListener] uncaught exception, passing to exception handler
      java.lang.IllegalStateException: Could not commit transaction
       at org.jboss.seam.jsf.SeamPhaseListener.commitOrRollback(SeamPhaseListener.java:625)
       at org.jboss.seam.jsf.SeamPhaseListener.commitOrRollback(SeamPhaseListener.java:604)
       at org.jboss.seam.jsf.SeamPhaseListener.handleTransactionsAfterPhase(SeamPhaseListener.java:345)
       at org.jboss.seam.jsf.SeamPhaseListener.afterServletPhase(SeamPhaseListener.java:245)
      
      <stuff deleted for brevity>
      
      
      Caused by: javax.transaction.RollbackException: [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] Could not commit transaction.
       at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1426)
       at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135)
       at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:75)
      
      <stuff deleted for brevity>
      
      
       at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
       at org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:504)
       ... 61 more
      Caused by: java.sql.BatchUpdateException: UPDATE command denied to user 'ondqry'@'s003005.ba.ad.ssa.gov' for table 'office_view'
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
       at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
       at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
       at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
       ... 68 more
      


        • 1. Re: dataTable with sortBy attempts to UPDATE
          Nick Belaevski Master

          Hi,

          Does this happen if you use h:dataTable and h:commandLink?

          • 2. Re: dataTable with sortBy attempts to UPDATE
            Tanya Ruttenberg Expert

            When I use h:dataTable and h:commandLink the sort (still) doesn't work, but at least no update is attempted. Here is the output in the logfile:

            09:12:59,530 INFO [STDOUT] Hibernate:
             select
             office0_.office_id as office1_18_0_,
             office0_.addr1 as addr2_18_0_,
             office0_.building_id as building3_18_0_,
             office0_.circuit_id as circuit4_18_0_,
             office0_.city as city18_0_,
             office0_.contact_name as contact6_18_0_,
             office0_.contact_phone1 as contact7_18_0_,
             office0_.extranet as extranet18_0_,
             office0_.nickname as nickname18_0_,
             office0_.OOB_num as OOB10_18_0_,
             office0_.ring as ring18_0_,
             office0_.ROCC as ROCC18_0_,
             office0_.site_code as site13_18_0_,
             office0_.state as state18_0_,
             office0_.swvendor as swvendor18_0_,
             office0_.total_users as total16_18_0_,
             office0_.type as type18_0_,
             office0_.videounits as videounits18_0_,
             office0_.voip as voip18_0_,
             office0_.wanvendor as wanvendor18_0_,
             office0_.zip as zip18_0_
             from
             ondtdr.office_view office0_
             where
             office0_.office_id=?
            09:12:59,608 INFO [STDOUT] Hibernate:
             select
             devices0_.office_id as office12_1_,
             devices0_.dev_id as dev1_1_,
             devices0_.dev_id as dev1_17_0_,
             devices0_.dev_ip as dev2_17_0_,
             devices0_.dev_name as dev3_17_0_,
             devices0_.dev_os_ver as dev4_17_0_,
             devices0_.dev_platform as dev5_17_0_,
             devices0_.dev_serial_num as dev6_17_0_,
             devices0_.last_update as last7_17_0_,
             devices0_.netqos as netqos17_0_,
             devices0_.office_id as office12_17_0_,
             devices0_.pe_router as pe9_17_0_,
             devices0_.is_router as is10_17_0_,
             devices0_.smc as smc17_0_
             from
             ondtdr.devices devices0_
             where
             devices0_.office_id=?
            


            TDR

            • 3. Re: dataTable with sortBy attempts to UPDATE
              Tanya Ruttenberg Expert

              It seems that the simpleTogglePanel opened attribute does not work when nested within rich:dataTable. The result is always an opened simpleTogglePanel no matter the value of "opened".

              When I change rich:dataTable to h:dataTable the panel is opened or closed depending on the value of "opened".

              If anyone has a workaround to this I'd be interested to hear it. I can't use h:dataTable because my need to nest a table is more than my need to have my togglepanel default to "opened = false". As fas as I can tell you cannot nest h:dataTable within another h:dataTable.