org.hibernate.exception.SQLGrammarException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • Hibernate not able to find HSQLDB Table
    via hsqldb by txie
    ,
  • Hibernate does not create table from Entity
    via Stack Overflow by kasiaF
    ,
  • ---------- <hibernate-mapping package="mx.com.dtc.ayde.model"> <class name="Instancia" table="INSTANCIA"> <id name="id" column="ID" type="java.lang.Long"> <generator class="sequence"> <param name="sequence">sec_instancia</param> </generator> </id> <property name="fecha" column="FECHA" type="java.util.Date" /> <property name="titulo" column="TITULO" type="java.lang.String" /> <property name="version" column="VERSION" type="java.lang.Long" not-null="true"/> <property name="documento" column="DOCUMENTO" type="java.lang.Long" /> <property name="nombreArchivo" column="NOMBRE_ARCHIVO" type="java.lang.String"/> <many-to-one name="plantilla" column="PLANTILLA_ID" class="Plantilla" lazy="false"/> <many-to-one name="usuario" column="USUARIO_ID" class="Usuario" lazy="false"/> <many-to-one name="creador" column="CREADOR_ID" class="Usuario" lazy="false"/> <bag name="datos" inverse="true" lazy="false"> <key column="INSTANCIA_ID"/> <one-to-many class="DatoPlantilla"/> </bag> </class> </hibernate-mapping> ----------------- <hibernate-mapping package="mx.com.dtc.ayde.model"> <class name="UsuarioInstancia" table="USUARIO_INSTANCIA"> <composite-id name="id" class="UsuarioInstanciaKey"> <key-many-to-one name="instancia" column="INSTANCIA_ID" class="Instancia"/> <key-many-to-one name="usuario" column="USUARIO_ID" class="Usuario"/> </composite-id> <property name="lectura" column="LECTURA" type="java.lang.String" /> </class> </hibernate-mapping> from Instancia as i where i.version = ( select max(i2.version) as version from UsuarioInstancia ui right outer join ui.id.instancia i2 where i2.documento=? and (i2.usuario=? or ui.id.usuario=?) ) and i.documento=? this will generate: 13:39:57,906 DEBUG SQL:324 - select instancia0_.ID as ID, instancia0_.FECHA as FECHA6_, instancia0_.TITULO as TITULO6_, instancia0_.VERSION as VERSION6_, instancia0_.DOCUMENTO as DOCUMENTO6_, instancia0_.NOMBRE_ARCHIVO as NOMBRE6_6_, instancia0_.PLANTILLA_ID as PLANTILLA7_6_, instancia0_.USUARIO_ID as USUARIO8_6_, instancia0_.CREADOR_ID as CREADOR9_6_ from INSTANCIA instancia0_ where instancia0_.VERSION=(select instancia2_.VERSION from USUARIO_INSTANCIA usuarioins1_, right outer join INSTANCIA instancia2_ on usuarioins1_.INSTANCIA_ID=instancia2_.ID where instancia2_.DOCUMENTO=? and (instancia2_.USUARIO_ID=? or usuarioins1_.USUARIO_ID=?) and instancia2_.VERSION=?) and instancia0_.DOCUMENTO=? 13:39:58,000 WARN JDBCExceptionReporter:71 - SQL Error: 942, SQLState: 42000 13:39:58,000 ERROR JDBCExceptionReporter:72 - ORA-00942: table or view does not exist 13:39:58,203 ERROR ControlVersionesAction:72 - org.hibernate.exception.SQLGrammarException: could not execute query org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1596) at org.hibernate.loader.Loader.list(Loader.java:1577) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) --- and it seems to be because of this: from INSTANCIA instancia0_ where instancia0_.VERSION=(select instancia2_.VERSION from USUARIO_INSTANCIA usuarioins1_, right outer join INSTANCIA instancia2_ on usuarioins1_.INSTANCIA_ID=instancia2_.ID it takes "right outer " like a table name! Regards
    via by Hector Suarez Barenca,
  • Hi, with the new query parser I get an exception when doing a query which contain a join between two entities with a many-to-many relation inbetween. This is the query: session.createQuery("select distinct category from Category category " + "left join fetch category.content as content " + "left join fetch content.price " + "where category.id = :categoryId") .setLong("categoryId", id.longValue() ).list(); And this is the result: 02:50:08,140 INFO [STDOUT] Hibernate: select distinct category0_.ID as ID0_, content2_.ID as ID1_, price3_.ID as ID2_, category0_.NAME as NAME3_0_, category0_.DESCRIPTION as DESCRIPT3_3_0_, category0_.HEADER_IMAGE as HEADER4_3_0_, category0_.MAIN_IMAGE as MAIN5_3_0_, category0_.TIME as TIME3_0_, category0_.PARENT_FK a s PARENT7_3_0_, content2_.DESCRIPTION as DESCRIPT2_7_1_, content2_.NAME as NAME7_1_, content2_.TIME as TIME7_1_, content2_.PRIORITY as PRIORITY7_1_, content2_.BRAND as BRAND7_1_, content2_.PRICE_FK as PRICE7_7_1_, content1_.CATEGORIES_FK as CATEGORIES1___, content1_.CONTENT_FK as CONTENT2___, price3_.CURRENCY as CURRENCY2_2_, price3_.AMOUNT as AMOUNT2_2_ from CATEGORY category0_ left outer join CATEGORIES2CONTENT content1_ on category0_.ID=content1_.CATEGORIES_FK left outer j oin CONTENT content2_ on content1_.CONTENT_FK=content2_.ID left outer join PRICE price3_ on content2_.PRICE_FK=price3_.ID where (category0_.ID=?) order by content2_.CONTENT_FK 02:50:08,296 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 02:50:08,296 ERROR [JDBCExceptionReporter] Unknown column 'content2_.CONTENT_FK' in 'order clause' 02:50:08,312 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query 02:57:47,984 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could n ot execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) In the SQL query an alias content1_ is created for the relation table and content2_ for the Content entity table. The error is that in the order by clause it try to order on content2_.CONTENT_FK and there is no such column. The order by clause should be either on content1_.CONTENT_FK or content2_.ID. Here is another query that shows the same behaviour: 03:20:47,171 INFO [STDOUT] Hibernate: select content0_.ID as ID0_, category2_.ID as ID1_, filesets3_.ID as ID2_, price4_.ID as ID3_, content0_.DESCRIPTION as DESCRIPT2_7_0_, content0_.NAME as NAME7_0_, content0_.TIME as TIME7_0_, content0_.PRIORITY as PRIORITY7_0_, content0_.BRAND as BRAND7_0_, content0_.PRICE_FK as PRICE7_7_0_, category2_.NAME as NAME3_1_, category2_.DESCRIPTION as DESCRIPT3_3_1_, category2_.HEADER_IMAGE as HEADER4_3_1_, category2_.MAIN_IMAGE as MAIN5_3_1_, category2_.TIME as TIME3_1_, category2_.PARENT_FK as PARENT7_3_1_, categories1_.CONTENT_FK as CONTENT2___, categories1_.CATEGORIES_FK as CATEGORIES1___, filesets3_.ORDER_ID as ORDER2_9_2_, filesets3_.HANDLER_CLASS as HANDLER3_9_2_, filesets3_.CONTENT_FK as CONTENT4_9_2_, filesets3_.CONTENT_FK as CONTENT4___, filesets3_.ID as ID__, price4_.CURRENCY as CURRENCY2_3_, price4_.AMOUNT as AMOUNT2_3_ from CONTENT content0_ left outer join CATEGORIES2CONTENT categories1_ on content0_.ID=categories1_.CONTENT_FK left outer join CATEGORY category2_ on categories1_.CATEGORIES_FK=category2_.ID inner join FILESET filesets3_ on content0_.ID=filesets3_.CONTENT_FK left outer join PRICE price4_ on content0_.PRICE_FK=price4_.ID where (category2_.ID=?) order by category2_.CATEGORIES_FK , filesets3_.CONTENT_FK 03:20:47,218 WARN [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22 03:20:47,218 ERROR [JDBCExceptionReporter] Unknown column 'category2_.CATEGORIES_FK' in 'order clause' 03:20:47,218 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1502) at org.hibernate.loader.Loader.list(Loader.java:1482) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
    via by Sverker Abrahamsson,
  • I am trying to use "getNamedQuery". When I run the SQL, as shown by putting the property "hibernate.show_sql" in config file, The SQL Runs perfectly fine. but in Hibernate it is throwing an exception of "invalid Column", What is annoying is , It worked fine when I was not using named query. below is the copy of my hibernate.cfg.xml ============================== <?xml version='1.0' encoding='UTF-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <!-- Generated by MyEclipse Hibernate Tools. --> <hibernate-configuration> <session-factory> <property name="connection.username">pdbread</property> <property name="connection.url"> jdbc:oracle:thin:@chi1dds1:1521:pdbdev </property> <property name="dialect"> org.hibernate.dialect.Oracle9Dialect </property> <property name="myeclipse.connection.profile">PDBDEV</property> <property name="connection.password">owner</property> <property name="hibernate.show_sql">true</property> <property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </property> <mapping resource="com/uscellular/shared/services/hibernate/Organization.hbm.xml" /> </session-factory> </hibernate-configuration> ======================== The Organization.hbm.xml file ===================== <?xml version="1.0"?> <!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 - Hibernate Tools --> <hibernate-mapping> <class name="com.uscellular.shared.services.hibernate.Organization" table="ORGANIZATION" schema="PDBREAD"> <id name="organizationKey" type="java.lang.Long"> <column name="ORGANIZATION_KEY" precision="10" scale="0" /> <generator class="assigned" /> </id> <property name="region" type="java.lang.String"> <column name="REGION" length="30" /> </property> <property name="clusterDesc" type="java.lang.String"> <column name="CLUSTER_DESC" length="30" /> </property> <property name="submarketId" type="java.lang.String"> <column name="SUBMARKET_ID" length="3" /> </property> <property name="submarketDesc" type="java.lang.String"> <column name="SUBMARKET_DESC" length="30" /> </property> <property name="marketStatus" type="java.lang.String"> <column name="MARKET_STATUS" length="1" /> </property> <property name="dataCenter" type="java.lang.String"> <column name="DATA_CENTER" length="3" /> </property> <property name="roamingAdminFee" type="java.lang.Double"> <column name="ROAMING_ADMIN_FEE" precision="9" /> </property> <property name="networkSurcharge" type="java.lang.Double"> <column name="NETWORK_SURCHARGE" precision="9" /> </property> <property name="state911Fee" type="java.lang.Double"> <column name="STATE_911_FEE" precision="9" /> </property> <property name="state911FeeType" type="java.lang.String"> <column name="STATE_911_FEE_TYPE" length="3" /> </property> <property name="additionalComments" type="java.lang.String"> <column name="ADDITIONAL_COMMENTS" length="2000" /> </property> <property name="caresModificationDate" type="java.util.Date"> <column name="CARES_MODIFICATION_DATE" length="7" /> </property> <property name="modificationDate" type="java.util.Date"> <column name="MODIFICATION_DATE" length="7" /> </property> <property name="modificationUser" type="java.lang.String"> <column name="MODIFICATION_USER" length="10" /> </property> <property name="approvalDate" type="java.util.Date"> <column name="APPROVAL_DATE" length="7" /> </property> <property name="approvalUser" type="java.lang.String"> <column name="APPROVAL_USER" length="10" /> </property> <property name="lastReleaseDate" type="java.util.Date"> <column name="LAST_RELEASE_DATE" length="7" /> </property> <property name="clusterId" type="java.lang.Long"> <column name="CLUSTER_ID" precision="10" scale="0" not-null="true" /> </property> </class> <sql-query name="GetAllOrganization"> <return alias="Organization" class="com.uscellular.shared.services.hibernate.Organization"> <return-property name="ORGANIZATION_KEY" column="organizationKey"/> <return-property name="REGION" column="region"/> <return-property name="CLUSTER_DESC" column="clusterDesc"/> <return-property name="SUBMARKET_ID" column="submarketId"/> <return-property name="SUBMARKET_DESC" column="submarketDesc"/> <return-property name="MARKET_STATUS" column="marketStatus"/> <return-property name="DATA_CENTER" column="dataCenter"/> <return-property name="ROAMING_ADMIN_FEE" column="roamingAdminFee"/> <return-property name="NETWORK_SURCHARGE" column="networkSurcharge"/> <return-property name="STATE_911_FEE" column="state911Fee"/> <return-property name="STATE_911_FEE_TYPE" column="state911FeeType"/> <return-property name="ADDITIONAL_COMMENTS" column="additionalComments"/> <return-property name="CARES_MODIFICATION_DATE" column="caresModificationDate"/> <return-property name="MODIFICATION_DATE" column="modificationDate"/> <return-property name="MODIFICATION_USER" column="modificationUser"/> <return-property name="APPROVAL_DATE" column="approvalDate"/> <return-property name="APPROVAL_USER" column="approvalUser"/> <return-property name="LAST_RELEASE_DATE" column="lastReleaseDate"/> <return-property name="CLUSTER_ID" column="clusterId"/> </return> select organization_key AS organizationKey, region AS region, cluster_desc AS clusterDesc, submarket_id AS submarketId, submarket_desc AS submarketDesc, market_status AS marketStatus, data_center AS dataCenter, nvl(roaming_admin_fee,0) AS roamingAdminFee, nvl(network_surcharge,0) AS networkSurcharge, nvl(state_911_fee,0) AS state911Fee, state_911_fee_type AS state911FeeType, additional_comments AS additionalComments, cares_modification_date AS caresModificationDate, modification_date AS modificationDate, modification_user AS modificationUser, approval_date AS approvalDate, approval_user AS approvalUser, last_release_date AS lastReleaseDate, cluster_id AS clusterId from organization </sql-query> </hibernate-mapping> =========================== when I make a call like this >>>> public Organization[] getAll() { List<Organization> retList; Organization[] org= null; try { org.hibernate.Session s = getSession(); retList = s.getNamedQuery("GetAllOrganization").list(); org = new Organization[retList.size()]; retList.toArray(org); } catch (RuntimeException re) { log.error("get failed", re); throw re; } return org; } >>>>> I get an Exception as below Hibernate: select organization_key AS organizationKey, region AS region, cluster_desc AS clusterDesc, submarket_id AS submarketId, submarket_desc AS submarketDesc, market_status AS marketStatus, data_center AS dataCenter, nvl(roaming_admin_fee,0) AS roamingAdminFee, nvl(network_surcharge,0) AS networkSurcharge, nvl(state_911_fee,0) AS state911Fee, state_911_fee_type AS state911FeeType, additional_comments AS additionalComments, cares_modification_date AS caresModificationDate, modification_date AS modificationDate, modification_user AS modificationUser, approval_date AS approvalDate, approval_user AS approvalUser, last_release_date AS lastReleaseDate, cluster_id AS clusterId from organization <Jan 9, 2007 12:04:00 AM CST> <Warning> <org.hibernate.util.JDBCExceptionReporter> <000000> <SQL Error: 17006, SQLState: null> <Jan 9, 2007 12:04:00 AM CST> <Error> <org.hibernate.util.JDBCExceptionReporter> <000000> <Invalid column name> <Jan 9, 2007 12:04:00 AM CST> <Error> <com.uscellular.shared.services.hibernate.OrganizationDAO> <000000> <get failed org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1518) at org.hibernate.loader.Loader.list(Loader.java:1498) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103) Truncated. see log file for complete stacktrace java.sql.SQLException: Invalid column name at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3291) at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914) Truncated. see log file for complete stacktrace > org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:1518) at org.hibernate.loader.Loader.list(Loader.java:1498) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1340) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151) at com.uscellular.shared.services.hibernate.OrganizationDAO.getAll(OrganizationDAO.java:68) Any help in this is appreciated, I am wondering, why this could be a problem, ( because of any mistake in my configuration or .hbm.xml file ) or something else. when the sql is perfectly running file I have tried to remove the 'AS' clause in SQL - did not work I tried to put like ' ORGANIZATION_KEY AS Organization.organizationKey ' = did not work I tried to put ' From Organization org ' alias in table name - did not work I have tried putting the Oracle schema.table name in the query - Did not work. please help !!!!!!!!!!!!!!!!!! have already spent a week worth of time on this issue already. Thanks
    via by Srikant Panda,
    • org.hibernate.exception.SQLGrammarException: could not fetch initial value at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.id.IncrementGenerator.getNext(IncrementGenerator.java:107) at org.hibernate.id.IncrementGenerator.generate(IncrementGenerator.java:45) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:85) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
    No Bugmate found.