dataTable with sortBy attempts to UPDATE
daxxy Aug 18, 2009 12:27 PMSeam 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