1 Reply Latest reply on Mar 20, 2009 6:24 PM by Elvis D,

    Column not found problem

    Elvis D, Novice

      Hi all,

      I have a problem using native queries.
      I should query a view on my database. The resultset is then manually mapped to a non-@entity tagged pojo.

      The view has the following columns:

      +--------------------+--------------+------+-----+---------+-------+
      | Field | Type | Null | Key | Default | Extra |
      +--------------------+--------------+------+-----+---------+-------+
      | artStoreid | bigint(20) | NO | | 0 | |
      | articleId | bigint(20) | NO | | 0 | |
      | articleNbr | int(11) | NO | | NULL | |
      | articleDescription | varchar(126) | NO | | NULL | |
      | storeId | bigint(20) | NO | | 0 | |
      | storeCode | varchar(10) | NO | | NULL | |
      | storeDescription | varchar(62) | NO | | NULL | |
      | siteId | bigint(20) | NO | | 0 | |
      +--------------------+--------------+------+-----+---------+-------+
      


      The log message is the following:
      18:32:51,212 INFO [OASI] Native query: SELECT artStoreId, articleId, articleNbr, articleDescription, storeId, storeCode, storeDescription, siteId FROM artstoresearch WHERE siteId=3 AND (articleNbr LIKE'%' OR articleDescription LIKE '') order by articleNbr
      18:32:51,214 INFO [STDOUT] Hibernate:
       SELECT
       artStoreId,
       articleId,
       articleNbr,
       articleDescription,
       storeId,
       storeCode,
       storeDescription,
       siteId
       FROM
       artstoresearch
       WHERE
       siteId=3
       AND (
       articleNbr LIKE'%'
       OR articleDescription LIKE ''
       )
       order by
       articleNbr
      18:32:51,214 INFO [BigIntegerType] could not read column value from result set: id; Column 'id' not found.
      18:32:51,215 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
      18:32:51,215 ERROR [JDBCExceptionReporter] Column 'id' not found.
      18:32:51,216 ERROR [OASI] Loading ArticleStoreView records via NativeQuery.
      javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
       at ch.oasi.xlnet.dao.ArticleStoreViewDao.findByArticleAndSite(ArticleStoreViewDao.java:38)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeTarget(MethodInvocation.java:122)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)
       at org.jboss.ejb3.EJBContainerInvocationWrapper.invokeNext(EJBContainerInvocationWrapper.java:69)
       at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:73)
       at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:59)
       at sun.reflect.GeneratedMethodAccessor330.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)
       at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_7988008.invoke(InvocationContextInterceptor_z_fillMethod_7988008.java)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)
       at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_7988008.invoke(InvocationContextInterceptor_z_setup_7988008.java)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
       at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:159)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:65)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)
       at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
       at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:486)
       at org.jboss.ejb3.session.InvokableContextClassProxyHack._dynamicInvoke(InvokableContextClassProxyHack.java:56)
       at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:91)
       at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
       at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:908)
       at org.jboss.remoting.transport.socket.ServerThread.completeInvocation(ServerThread.java:742)
       at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:695)
       at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:522)
       at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:230)
      Caused by: 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.custom.CustomLoader.list(CustomLoader.java:312)
       at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
       at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
       at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
       ... 59 more
      Caused by: java.sql.SQLException: Column 'id' not found.
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
       at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
       at com.mysql.jdbc.ResultSetImpl.getBigDecimal(ResultSetImpl.java:1406)
       at org.jboss.resource.adapter.jdbc.WrappedResultSet.getBigDecimal(WrappedResultSet.java:320)
       at org.hibernate.type.BigIntegerType.get(BigIntegerType.java:57)
       at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
       at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
       at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
       at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
       at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
       at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
       at org.hibernate.loader.Loader.doQuery(Loader.java:724)
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
       at org.hibernate.loader.Loader.doList(Loader.java:2228)
       ... 66 more