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

Google Groups | Sunil Tantry | 3 years ago
  1. 0

    Rundeck 2.0.1 Integrating with Oracle Database

    Google Groups | 3 years ago | Sunil Tantry
    org.hibernate.exception.SQLGrammarException: could not execute query
  2. 0

    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)

    Hibernate JIRA | 1 decade ago | Sverker Abrahamsson
    org.hibernate.exception.SQLGrammarException: could not execute query
  3. Speed up your debug routine!

    Automated exception search integrated into your IDE

  4. 0

    Hibernate & Joda DateTime - SQLException: expected DATE got BINARY

    Coderanch | 4 months ago | Simon Ritchie
    org.hibernate.exception.SQLGrammarException: could not execute query
  5. 0

    OpenUDDI / Mailing Lists

    sourceforge.net | 1 year ago
    org.hibernate.exception.SQLGrammarException: could not execute query

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.hibernate.exception.SQLGrammarException

      could not execute query

      at org.codehaus.groovy.grails.web.context.GrailsContextLoader.initWebApplicationContext()
    2. Grails
      GrailsContextLoader.initWebApplicationContext
      1. org.codehaus.groovy.grails.web.context.GrailsContextLoader.initWebApplicationContext(GrailsContextLoader.java:124)
      1 frame
    3. Spring
      ContextLoaderListener.contextInitialized
      1. org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:111)
      1 frame
    4. Jetty
      AbstractLifeCycle.start
      1. org.eclipse.jetty.server.handler.ContextHandler.startContext(ContextHandler.java:733)
      2. org.eclipse.jetty.servlet.ServletContextHandler.startContext(ServletContextHandler.java:233)
      3. org.eclipse.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1214)
      4. org.eclipse.jetty.server.handler.ContextHandler.doStart(ContextHandler.java:676)
      5. org.eclipse.jetty.webapp.WebAppContext.doStart(WebAppContext.java:455)
      6. org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:59)
      7. org.eclipse.jetty.server.handler.HandlerWrapper.doStart(HandlerWrapper.java:90)
      8. org.eclipse.jetty.server.Server.doStart(Server.java:261)
      9. org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:59)
      9 frames
    5. com.dtolabs.rundeck
      RunServer.main
      1. com.dtolabs.rundeck.RunServer.run(RunServer.java:105)
      2. com.dtolabs.rundeck.RunServer.main(RunServer.java:67)
      2 frames
    6. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      4. java.lang.reflect.Method.invoke(Method.java:597)
      4 frames