dataTable with sortBy attempts to UPDATE

[daxxy]

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

[nbelaevski]

Hi,

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

[daxxy]

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

[daxxy]

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.