-->
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 » Hibernate & Java Persistence » Hibernate Users

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
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
damonbrent
 Post subject: hql error "not all variables bound"
PostPosted: Mon Dec 19, 2005 12:31 am 
Newbie

Joined: Sun Dec 21, 2003 4:03 am
Posts: 4
I have an hql named query:
Code:
from CatalogEntryImpl as entity
   where entity.catalog = :catalog
   and entity.publication.authors.firstName = :authorFirstName
   and entity.publication.authors.lastName = :authorLastName


CatalogEntryImp.catalog is a CatalogImpl object, and
CatalogEntryImpl.publication is a PublicationImpl object
with a collection of Author objects that have firstName
and lastName properties.

So, I want my hql to select CatalogEntryImpls where
any of their publication's authors match the authorFirstName
and authorLastName values.

However, I get an "ORA-01008: not all variables bound"
error when the query is run. I've verified that my :catalog,
:authorFirstName and :authorLastName parameters
are not null and of correct type.

Now, what's interesting is that it /seems/ to be giving
me errors on CatalogEntryImpl.subjectEntries, which
is a collection of SubjectEntryImpl objects.

Any ideas? (And if the idea is RTFM, please supply me
a link to said FM page, because I have scoured the docs
/pretty well/ at this point...).

Thanks!
Brent


Hibernate version:
3.0.5

Mapping documents:
Will provide if necessary

Code between sessionFactory.openSession() and session.close():
Code:
      Vector r = (Vector) catalogService.execute(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException {
          // query is the named query mentioned above..
          Query q = session.getNamedQuery(query);
          String key = null;
          // params is a Map of parameters...
          for (Iterator it = params.keySet().iterator(); it.hasNext();) {
            key = (String) it.next();
            q.setParameter(key, params.get(key));
          }
          q.setMaxResults(5);
          q.setCacheable(true);
          return new Vector(new LinkedHashSet(q.list()));
        }
      }, true);


Full stack trace of any exception that occurs:
Code:
xxx.CatalogServiceException: Error performing 'xxx.CatalogService.execute(org.springframework.orm.hibernate3.HibernateCallback hibernateCallback, boolean boo)' --> org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not load collection by subselect: [xxx.CatalogEntryImpl.subjectEntries#<275414, 301453>]; uncategorized SQLException for SQL [select subjectent0_.CATALOG_ENTRY_FK as CATALOG3_1_, subjectent0_.ID as ID1_, subjectent0_.ID as ID0_, subjectent0_.ORDINAL as ORDINAL13_0_, subjectent0_.CATALOG_ENTRY_FK as CATALOG3_13_0_, subjectent0_.SUBJECT_FK as SUBJECT4_13_0_ from SUBJECT_ENTRY subjectent0_ where subjectent0_.CATALOG_ENTRY_FK in (select catalogent0_.ID from CATALOG_ENTRY catalogent0_, PUBLICATION publicatio1_, AUTHOR authors2_, AUTHOR authors4_ where publicatio1_.ID=authors4_.PUBLICATION_FK and publicatio1_.ID=authors2_.PUBLICATION_FK and catalogent0_.PUBLICATION_FK=publicatio1_.ID and catalogent0_.CATALOG_FK=? and authors2_.FIRST_NAME=? and authors4_.LAST_NAME=?) order by subjectent0_.CATALOG_ENTRY_FK]; SQL state [72000]; error code [1008]; ORA-01008: not all variables bound
; nested exception is java.sql.SQLException: ORA-01008: not all variables bound
[nested exception: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not load collection by subselect: [xxx.CatalogEntryImpl.subjectEntries#<275414, 301453>]; uncategorized SQLException for SQL [select subjectent0_.CATALOG_ENTRY_FK as CATALOG3_1_, subjectent0_.ID as ID1_, subjectent0_.ID as ID0_, subjectent0_.ORDINAL as ORDINAL13_0_, subjectent0_.CATALOG_ENTRY_FK as CATALOG3_13_0_, subjectent0_.SUBJECT_FK as SUBJECT4_13_0_ from SUBJECT_ENTRY subjectent0_ where subjectent0_.CATALOG_ENTRY_FK in (select catalogent0_.ID from CATALOG_ENTRY catalogent0_, PUBLICATION publicatio1_, AUTHOR authors2_, AUTHOR authors4_ where publicatio1_.ID=authors4_.PUBLICATION_FK and publicatio1_.ID=authors2_.PUBLICATION_FK and catalogent0_.PUBLICATION_FK=publicatio1_.ID and catalogent0_.CATALOG_FK=? and authors2_.FIRST_NAME=? and authors4_.LAST_NAME=?) order by subjectent0_.CATALOG_ENTRY_FK]; SQL state [72000]; error code [1008]; ORA-01008: not all variables bound
; nested exception is java.sql.SQLException: ORA-01008: not all variables bound
]


Name and version of the database you are using:
Oracle 9.0.1

The generated SQL (show_sql=true):
(in stack trace)

Debug level Hibernate log excerpt:

Code:
Code:
Top
 Profile  
 
abg1979
 Post subject:
PostPosted: Tue Dec 27, 2005 3:29 pm 
Regular
Regular

Joined: Fri Sep 09, 2005 11:35 am
Posts: 101
you should set the log level for org.hibernate.type to debug. this should tell you whether hibernate had bound proper values to all the parameters.
Top
 Profile  
 
damonbrent
 Post subject:
PostPosted: Thu Dec 29, 2005 12:46 pm 
Newbie

Joined: Sun Dec 21, 2003 4:03 am
Posts: 4
Thanks. Extra logging did show that there were no parameters being set
for the query mentioned above... For the time, I've just gone to using
Criteria instead of hql :-(

Any idea why this hql wouldn't work, but the following Criteria does?
ISTM that they're saying the same thing...enlightenment appreciated :-)

Code:
from CatalogEntryImpl as entity
where entity.catalog = :catalog
    and entity.publication.authors.firstName = :firstName
    and entity.publication.authors.lastName = :lastName


Code:
Criteria rootCriteria = session.createCriteria(CatalogEntry.class);
                      .add(Expression.eq("catalog", catalog));
Criteria authorCriteria = rootCriteria
                        .createCriteria("publication")
                        .createCriteria("authors");
authorCriteria.add(Expression.eq("firstName", firstName);
authorCriteria.add(Expression.eq("lastName", lastName);
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Thu Dec 29, 2005 4:56 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
The code you've written will work; the only thing I can see is that params mightn't be set up correctly. Have you debugged the loop? Verified that the parameters all have values in the map, and the keys are the correct strings? The keys should be in the same case, and not have the leading colons.
Top
 Profile  
 
abg1979
 Post subject:
PostPosted: Thu Dec 29, 2005 5:00 pm 
Regular
Regular

Joined: Fri Sep 09, 2005 11:35 am
Posts: 101
damonbrent wrote:
Vector r = (Vector) catalogService.execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
// query is the named query mentioned above..
Query q = session.getNamedQuery(query);
String key = null;
// params is a Map of parameters...
for (Iterator it = params.keySet().iterator(); it.hasNext();) {
key = (String) it.next();
q.setParameter(key, params.get(key));
}
q.setMaxResults(5);
q.setCacheable(true);
return new Vector(new LinkedHashSet(q.list()));
}
}, true);


can you print out all parameter values from your this loop and post them?

It is hard to say why criteria works and hql does not without these values.
Top
 Profile  
 
damonbrent
 Post subject:
PostPosted: Thu Dec 29, 2005 7:55 pm 
Newbie

Joined: Sun Dec 21, 2003 4:03 am
Posts: 4
I did verify that my parameters were being set correctly, and that
they had value(s).

I then simplified the query to the following, which also fails:
Code:
from CatalogEntryImpl as entity
   where entity.catalog = :catalog


In generating the subquery, there was no value being set for the
parameterized catalog id portion of the subquery:
Code:
catalogent0_.CATALOG_FK=?

and no debug messages to indicate _why_ the parameter wasn't
being set for the query (which happens to be loading SubjectEntries
associated with the CatalogEntry).

I think I've worked around my limited understanding of hibernate/hql
at the moment, so when I actually get my work done, I'll try again to
use hql...and report back my findings :-)

thanks.
b
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Thu Dec 29, 2005 9:45 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
So what's happening in the parameter-setting loop? Add in printlns or whatever. Maybe even post the results here, I'll gladly interpret the results, if I can. (Be verbose in the printlns, too much info is more useful than too little.)
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
  [ 7 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.