-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: SQL Query gives incorrect count
PostPosted: Fri Mar 16, 2012 3:49 pm 
Newbie

Joined: Wed Apr 28, 2010 11:55 am
Posts: 13
When I run this SQLQuery in Hibernate I aways get a count of 0. Run the same Query in SQL/Developer and it gives the correct results.

What am I doing wrong ?

Query:
Code:
      count = (Long) session.createSQLQuery(""
          + "select count(*) as count "
          + "  from tbl_role2 "
          + "  where role_parent in (:allRoles) "
          + "  start with role_name = :currRole "
          + "  connect by prior role_parent = role_name ").
          addScalar("count", Hibernate.LONG).
          setParameter("allRoles", allRoles).
          setParameter("currRole", ure.getId().getRoleName()).
          uniqueResult();

Log:
2012-03-16 15:40:11,057 [http-127.0.0.1-8180-1] DEBUG - Ws: compressRoleList(): All Roles = 'Encoder', 'SAS', 'Station-Manager', 'Supervisor'
2012-03-16 15:40:11,069 [http-127.0.0.1-8180-1] DEBUG - Ws: compressRoleList(): Role='Encoder'; count=0

SQL/Dev Query
Code:
select count(*) as count
  from tbl_role2
       where role_parent in ('Encoder', 'SAS', 'Station-Manager', 'Supervisor')
       start with role_name = 'Encoder'
       connect by prior role_parent = role_name;

Result:
Count = 2

Any ideas ?


Top
 Profile  
 
 Post subject: Re: SQL Query gives incorrect count
PostPosted: Thu Mar 22, 2012 3:31 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 990
I suggest you to log the jdbc-activities of your hibernate application with p6spy,
so you see exactly which sql-statement gets finally propagated to the database.


Furthermore the different result may be caused due isolation (see http://en.wikipedia.org/wiki/ACID),
it could be that the 2 elements were inserted within an active transaction which not had been commited yet.
When you do the query with SQL/Dev Query you probably use READ_UNCOMMMITED,
whilst your hibernate application probably is working with READ_COMMITED (=default) isolation level.


Top
 Profile  
 
 Post subject: Re: SQL Query gives incorrect count
PostPosted: Mon Apr 09, 2012 1:08 pm 
Newbie

Joined: Wed Apr 28, 2010 11:55 am
Posts: 13
I was screwing up the setXxx for :allRoles.
I was passing in a single string (setParameter) instead of using setParameterList.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

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:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.