-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
helgi
 Post subject: Serious Bug in SQL generation
PostPosted: Mon Sep 15, 2008 11:38 pm 
Newbie

Joined: Wed Sep 10, 2008 10:51 pm
Posts: 2
Hello Hibernate experts;

We are new to Hibernate, actually we indented to use it to replace TopLink, but now it seems we're facing a show-stopper.

The versions we're using are as following:

    Hibernate Annotations 3.4.0.GA
    Hibernate 3.3.0.SP1
    Hibernate Commons Annotations 3.1.0.GA
    Hibernate EntityManager 3.4.0.GA


The database is IBM DB2 9.5.

The error is as following:

Code:
Hibernate: select * from ( select rownumber() over() as rownumber_, * from cimart.customer_dim  where dayofyear(birthday) between dayofyear(CAST(? as date)) and dayofyear(CAST(? as date)) ) as temp_ where rownumber_ <= ?
[WARN] SQL Error: -104, SQLState: 42601
10953 [main] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: -104, SQLState: 42601
[ERROR] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=*;ver() as rownumber_,;<select_sublist>, DRIVER=3.50.152
10953 [main] ERROR org.hibernate.util.JDBCExceptionReporter  - DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=*;ver() as rownumber_,;<select_sublist>, DRIVER=3.50.152
[WARN] SQL Error: -727, SQLState: 56098
10953 [main] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: -727, SQLState: 56098
[ERROR] DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;*|ver() as rownumber_,|<select_sublist>, DRIVER=3.50.152
10953 [main] ERROR org.hibernate.util.JDBCExceptionReporter  - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;*|ver() as rownumber_,|<select_sublist>, DRIVER=3.50.152
[WARN] SQL Error: -727, SQLState: 56098
10953 [main] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: -727, SQLState: 56098
[ERROR] DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;*|ver() as rownumber_,|<select_sublist>, DRIVER=3.50.152
10953 [main] ERROR org.hibernate.util.JDBCExceptionReporter  - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;*|ver() as rownumber_,|<select_sublist>, DRIVER=3.50.152


I.e. a basic syntax error in generating a SQL. The method which is being executed is as following:

Code:
public CustomerDim findOneCustomerWhoHasBirthdayThisWeek() {
   

      Date [] daysArray = getStartAndEndDaysOfCurrentWeek();
      
      List<CustomerDim> resultList = em.createNativeQuery("select * from cimart.customer_dim " +
            " where dayofyear(birthday) between dayofyear(CAST(?1 as date)) and dayofyear(CAST(?2 as date))", CustomerDim.class)
              .setParameter(1, daysArray[0],TemporalType.DATE)
              .setParameter(2, daysArray[1],TemporalType.DATE)
              .setMaxResults(1)
              .getResultList();
      
      if (resultList.isEmpty())
         return null;
      else
         return resultList.get(0);   
      
   }


This seems even stranger since a native query is at hand here.

Any suggestions on a ways around this are welcome.

TIA,
Helgi Viggosson.
Top
 Profile  
 
helgi
 Post subject: Follow up
PostPosted: Tue Sep 16, 2008 9:39 am 
Newbie

Joined: Wed Sep 10, 2008 10:51 pm
Posts: 2
By removing .setMaxResults(1), we get rid of the SQL syntax error. But, the next error we get is as follows:

Code:
java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: is near line 1, column 50 [select c from is.cofus.amr.cimart.CustomerDim c, is.cofus.amr.cimart.CustomerSegments s  where c.customerKey = s.pk.customerKey and s.pk.policyId = ?1 and s.pk.sId = ?2]
   at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:601)
   at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)
   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.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:197)
   at $Proxy26.createQuery(Unknown Source)
   at is.cofus.amr.cimart.CIMartDAO.findOneCustomerInSegment(CIMartDAO.java:423)
   at is.cofus.amr.testing.trx.TestRuleEngine.setUpTransactions(TestRuleEngine.java:99)
   at is.cofus.amr.testing.trx.TestRuleEngine.testIt(TestRuleEngine.java:48)
   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 junit.framework.TestCase.runTest(TestCase.java:168)
   at junit.framework.TestCase.runBare(TestCase.java:134)
   at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
   at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.access$001(AbstractAnnotationAwareTransactionalTests.java:71)
   at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests$1.run(AbstractAnnotationAwareTransactionalTests.java:175)
   at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTest(AbstractAnnotationAwareTransactionalTests.java:283)
   at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runTestTimed(AbstractAnnotationAwareTransactionalTests.java:254)
   at org.springframework.test.annotation.AbstractAnnotationAwareTransactionalTests.runBare(AbstractAnnotationAwareTransactionalTests.java:172)
   at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:174)
   at org.springframework.test.jpa.AbstractJpaTests.runBare(AbstractJpaTests.java:255)
   at junit.framework.TestResult$1.protect(TestResult.java:110)
   at junit.framework.TestResult.runProtected(TestResult.java:128)
   at junit.framework.TestResult.run(TestResult.java:113)
   at junit.framework.TestCase.run(TestCase.java:124)
   at junit.framework.TestSuite.runTest(TestSuite.java:232)
   at junit.framework.TestSuite.run(TestSuite.java:227)
   at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:76)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: is near line 1, column 50 [select c from is.cofus.amr.cimart.CustomerDim c, is.cofus.amr.cimart.CustomerSegments s  where c.customerKey = s.pk.customerKey and s.pk.policyId = ?1 and s.pk.sId = ?2]
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
   at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
   at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
   at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)
   ... 36 more



This stem from this seemingly innocent JPA query:

Code:
public CustomerDim findOneCustomerInSegment(int segPolicyId, int segNr) {
            

      List<CustomerDim> resultList = em.createQuery("select c from CustomerDim c, CustomerSegments s " +
            " where c.customerKey = s.pk.customerKey and s.pk.policyId = ?1 and s.pk.sId = ?2")
              .setParameter(1, segPolicyId)
              .setParameter(2, segNr)
              .setMaxResults(1)
              .getResultList();
      return resultList.get(0);   
      
   }


Any ideas?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.