0 Replies Latest reply on Jan 9, 2011 5:06 PM by keerthi b

    Error fetching audited data which has oneTomany relationship

    keerthi b Newbie

      Hello,

       

      I have two entities Quotes and QuotesClient which has @oneTomany relationship.I am able to audit the data properly.But while fetching data i get "org.hibernate.exception.SQLGrammarException:" exception and

      Caused by: java.sql.SQLException: Table 'quoteappdb1.quotes_quotesclient_aud' doesn't exist(i pasted whole exception at the end).

       

      Could somebody help me in resolving this error?

       

      From the exception its clear that  it s trying to look for "quotes_quotesclient_aud" table which does not exist becuase my audit table name is "quotes_client_aud".

       

      I pasted my mapping files below.

       

       

      Other information:

       

      audit table names: quotes_aud and quotes_client_aud

       

      Mapping files:

       

      Quotes.hbm.xml:

       

      <?xml version="1.0" encoding="utf-8"?>

      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

      <!--

          Mapping file autogenerated by MyEclipse Persistence Tools

      -->

      <hibernate-mapping default-access="field">

          <class name="quoteapp.vo.Quotes" table="quotes" >

              <id name="id" type="java.lang.Integer">

                  <column name="QUOTE_ID" />

                  <generator class="native" />

              </id>

              <many-to-one name="divisions" class="quoteapp.vo.Divisions" fetch="join" lazy="false">

                  <column name="FK_DIVISION_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="staff" class="quoteapp.vo.Staff" fetch="join" lazy="false">

                  <column name="FK_STAFF_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="jobTypes" class="quoteapp.vo.JobTypes" fetch="join" lazy="false">

                  <column name="FK_JOB_TYPE_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="category" class="quoteapp.vo.Categories" fetch="join" lazy="false">

                  <column name="FK_CATEGORY_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="reason" class="quoteapp.vo.Reasons" fetch="join" lazy="false">

                  <column name="FK_REASON_ID" />

              </many-to-one>

              <property name="quoteDesc" type="java.lang.String">

                  <column name="QUOTE_DESC" />

              </property>

              <property name="quoteRefId" type="java.lang.String">

                  <column name="QUOTE_REF_ID" length="100" />

              </property>

              <property name="status" type="java.lang.String">

                  <column name="STATUS" length="100" not-null="true" />

              </property>

              <property name="createdBy" type="java.lang.Integer">

                  <column name="CREATED_BY" />

              </property>

              <property name="createdDate" type="java.util.Date">

                  <column name="CREATED_DATE" length="19" />

              </property>

              <property name="updatedBy" type="java.lang.Integer">

                  <column name="UPDATED_BY" />

              </property>

              <property name="updatedDate" type="java.util.Date">

                  <column name="UPDATED_DATE" length="19" />

              </property>

               <property name="comments" type="java.lang.String">

                  <column name="COMMENTS" length="255" />

              </property>

              <property name="assignedBy" type="java.lang.Integer">

                  <column name="ASSIGNED_BY" />

              </property>

              <set name="quotesClients" inverse="false" lazy="false" cascade="all" fetch="subselect">

                  <key>

                      <column name="FK_QUOTE_ID" not-null="true" />

                  </key>

                  <one-to-many class="quoteapp.vo.QuotesClient" />

              </set>

       

          </class>

      </hibernate-mapping>

       

      QuotesClient.hbm.xml

       

      <?xml version="1.0" encoding="utf-8"?>

      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

      <!--

          Mapping file autogenerated by MyEclipse Persistence Tools

      -->

      <hibernate-mapping default-access="field">

          <class name="quoteapp.vo.QuotesClient" table="quotes_client"  dynamic-update="true">

              <id name="id" type="java.lang.Integer">

                  <column name="QUOTE_CLIENT_ID" />

                  <generator class="native" />

              </id>

              <many-to-one name="clients" class="quoteapp.vo.Clients" fetch="join" lazy="false">

                  <column name="FK_CLIENT_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="segment" class="quoteapp.vo.Segments" fetch="join" lazy="false">

                  <column name="FK_SEGMENT_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="contacts" class="quoteapp.vo.Contacts" fetch="join" lazy="false">

                  <column name="FK_CONTACT_ID" />

              </many-to-one>

              <many-to-one name="quotes" class="quoteapp.vo.Quotes" fetch="join" lazy="false">

                  <column name="FK_QUOTE_ID" not-null="true" />

              </many-to-one>

              <many-to-one name="reason" class="quoteapp.vo.Reasons" fetch="join" lazy="false">

                  <column name="FK_Reason_ID"/>

              </many-to-one>

             <!--  <many-to-one name="supplierProbability" class="quoteapp.vo.Probability" fetch="select" lazy="false">

                  <column name="FK_SUPPLIER_PROBABILITY" not-null="true" />

              </many-to-one>

              <many-to-one name="projectProbability" class="quoteapp.vo.Probability" fetch="select" lazy="false">

                  <column name="PROBABILITY_ID" not-null="true" />

              </many-to-one>-->

             <property name="closeComments" type="java.lang.String">

                  <column name="CLOSE_COMMENTS" />

              </property>

       

              <property name="supplierProbability" type="java.lang.Integer">

                  <column name="FK_SUPPLIER_PROBABILITY" />

              </property>

              <property name="followupDate" type="java.util.Date">

                  <column name="FOLLOW_UP_DATE" />

              </property>

              <property name="projectProbability" type="java.lang.Integer">

                  <column name="FK_PROJECT_PROBABILITY" />

              </property>

              <property name="recordProbability" type="java.lang.Integer">

                  <column name="RECORD_PROBABILITY" />

              </property>

              <property name="probability" type="java.lang.Integer">

                  <column name="PROBABILITY" />

              </property>

              <property name="quoteValue" type="java.lang.Double">

                  <column name="QUOTE_VALUE" precision="22" scale="0" />

              </property>

              <property name="comments" type="java.lang.String">

                  <column name="COMMENTS" length="100" />

              </property>

              <property name="dueDate" type="java.util.Date">

                  <column name="DUE_DATE" length="10" />

              </property>

              <property name="revisionId" type="java.lang.Integer">

                  <column name="REVISION_ID" />

              </property>

              <property name="clientStatus" type="java.lang.String">

                  <column name="CLIENT_STATUS" length="15" not-null="true" />

              </property>

              <property name="createdBy" type="java.lang.Integer">

                  <column name="CREATED_BY" />

              </property>

              <property name="createdDate" type="java.util.Date">

                  <column name="CREATED_DATE" length="19" />

              </property>

              <property name="updatedBy" type="java.lang.Integer">

                  <column name="UPDATED_BY" />

              </property>

              <property name="updatedDate" type="java.util.Date">

                  <column name="UPDATED_DATE" length="19" />

              </property>

              <property name="forecast" type="java.lang.String">

                  <column name="FORECAST" length="1" not-null="true" />

              </property>

              <property name="optionStatus" type="java.lang.String">

                  <column name="OPTION_STATUS" length="100" />

              </property>

              <property name="sellingPrice" type="java.lang.Double">

                  <column name="SELLING_PRICE" precision="22" scale="0" />

              </property>

       

              <set name="clientOptionses" inverse="true" lazy="false" cascade="all" fetch="subselect">

                  <key>

                      <column name="FK_QUOTE_CLIENT_ID" />

                  </key>

                  <one-to-many class="quoteapp.vo.ClientOptions" />

              </set>

          </class>

          <sql-query name="UpdateQuoteStatus" callable="true" >  

              <return-scalar column="ROW_COUNT" type="java.lang.Integer"/>

              {call QuoteStatus_proc(?,?)}  

          </sql-query> 

            <sql-query name="UpdateQuoteClientStatus" callable="true" >  

              <return-scalar column="ROW_COUNT" type="java.lang.Integer"/>

              {call QuoteClientStatus_proc(?,?)}  

          </sql-query>

      </hibernate-mapping>

      But when i tried to fetch audited data i get below exception:

       

      org.hibernate.exception.SQLGrammarException: could not execute query

          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)

          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

          at org.hibernate.loader.Loader.doList(Loader.java:2231)

          at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)

          at org.hibernate.loader.Loader.list(Loader.java:2120)

          at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)

          at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)

          at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)

          at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)

          at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)

          at org.hibernate.envers.entities.mapper.relation.lazy.initializor.AbstractCollectionInitializor.initialize(AbstractCollectionInitializor.java:62)

          at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.checkInit(CollectionProxy.java:50)

          at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.iterator(CollectionProxy.java:70)

          at quoteapp.envers.AuditReaderImpl.getQuoteRevisions(AuditReaderImpl.java:33)

          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

          at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

          at java.lang.reflect.Method.invoke(Unknown Source)

          at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)

          at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)

          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

          at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)

          at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)

          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

          at $Proxy34.getQuoteRevisions(Unknown Source)

          at quoteapp.service.LoginManagerImpl.getQuoteRevisions(LoginManagerImpl.java:1136)

          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

          at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

          at java.lang.reflect.Method.invoke(Unknown Source)

          at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)

          at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)

          at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)

          at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)

          at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)

          at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)

          at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)

          at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)

          at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)

          at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)

          at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:322)

          at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

          at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)

          at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

          at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)

          at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

          at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

          at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)

          at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)

          at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)

          at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)

          at java.lang.Thread.run(Unknown Source)

      Caused by: java.sql.SQLException: Table 'quoteappdb1.quotes_quotesclient_aud' doesn't exist

          at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)

          at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)

          at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)

          at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)

          at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)

          at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)

          at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)

          at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)

          at org.hibernate.loader.Loader.doQuery(Loader.java:697)

          at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)

          at org.hibernate.loader.Loader.doList(Loader.java:2228)

          ... 52 more

       

      Could somebaody help me.