-->
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
gir
 Post subject: HQL and Oracle spatial functions problem in Hibernat 3
PostPosted: Mon Jul 04, 2005 8:19 am 
Newbie

Joined: Mon Jul 04, 2005 7:43 am
Posts: 4
Hello everyone,

we updated to Hibernate 3 and got trouble with an hql expression which
uses oracle spatial functions but worked pretty well in Hibernate 2:

Code:
select s.sectorName from SectorCoordinate s where SDO_RELATE(s.geometry,MDSYS.SDO_GEOMETRY(2007, 82027, MDSYS.SDO_POINT_TYPE(:x,:y,0), MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),
MDSYS.SDO_ORDINATE_ARRAY(:x,:yr1,:x,:yr2,:xr,:y)), 'mask=anyinteract querytype=WINDOW')='TRUE'


The hql parser does not like the dot in MDSYS.SDO_GEOMETRY (and the others MDSYS functions).

Code:
ERROR: <AST>:0:0: unexpected AST node: .
ERROR: cs-user com.o2.gis.core.db.CurroutUtil  - findSectornamesByCoor() - caught exception
org.hibernate.hql.ast.QuerySyntaxError: unexpected AST node: . [select s.sectorName from com.o2.gis.core.db.hibernate.mapping.SectorCoordinate s where SDO_RELATE(s.geometry,MDSYS.SDO_GEOMETRY(2007, 82027,MDSYS.SDO_POINT_TYPE(:x,:y,0),MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),MDSYS.SDO_ORDINATE_ARRAY(:x,:yr1,:x,:yr2,:xr,:y)),'mask=anyinteract querytype=WINDOW')='TRUE']
   at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
   at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:196)



Removing "MDSYS." lets hibernate create a nice looking SQL statement, with wrong function names, of course.

Trying to protect the dot with double-quotes or a backlash did not wrork:

Code:
org.hibernate.QueryException: unexpected char: '"' [select s.sectorName from com.o2.gis.core.db.hibernate.mapping.SectorCoordinate s where SDO_RELATE(s.geometry,"MDSYS.SDO_GEOMETRY"(2007, 82027,"MDSYS.SDO_POINT_TYPE"(:x,:y,0),"MDSYS.SDO_ELEM_INFO_ARRAY"(1,1003,4),"MDSYS.SDO_ORDINATE_ARRAY"(:x,:yr1,:x,:yr2,:xr,:y)),'mask=anyinteract querytype=WINDOW')='TRUE']
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:165)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)


As a workaround, we currently use the criteria api, where an sql restriction can be added. However, setting the parameters there is uglier.

Does anyone have an idea how to solve this dot problem? Might this even be a bug in the Hibernate 3 HQL parser or are there ways to protect such characters (which were not needed in Hibernate 2)?

Thanks
Top
 Profile  
 
gir
 Post subject: Seems to be a bug
PostPosted: Tue Jul 05, 2005 4:03 am 
Newbie

Joined: Mon Jul 04, 2005 7:43 am
Posts: 4
Ok, after searching a lot more I found this
http://opensource.atlassian.com/project ... se/HHH-435
Hence, it seems to be a bug in hibernate.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Tue Jul 05, 2005 4:08 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
No, its just not supported. You can of course write the code for it, if you like.
Top
 Profile  
 
gir
 Post subject:
PostPosted: Tue Jul 05, 2005 4:20 am 
Newbie

Joined: Mon Jul 04, 2005 7:43 am
Posts: 4
Sorry, I posted too soon. Yes, the above linked bug entry is no bug
and is not as related to my problem as I thought at first.

However, using the classic the classic hql parser seems to do the trick.

Unfortunately, the functions we have to use (as anyone else who uses Oracle Spatial) have dots in their names. If you give me some pointers I will look into the code and try to add support for such names as soon as I have got some free time on my hands.[/quote]
Top
 Profile  
 
christian
 Post subject:
PostPosted: Tue Jul 05, 2005 4:30 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Check the ANTLR parser grammar and source...
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Jul 05, 2005 10:46 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
The correct way to handle this is to subclass the Dialect and add some SQLFunctions. Trivial.
Top
 Profile  
 
gir
 Post subject:
PostPosted: Fri Jul 15, 2005 11:57 am 
Newbie

Joined: Mon Jul 04, 2005 7:43 am
Posts: 4
gavin wrote:
The correct way to handle this is to subclass the Dialect and add some SQLFunctions. Trivial.

Well, this problem may be trivial for you, but for me, who does not know the gory details of the hibernate internals, it is not.

I tried to simply register the functions in the constructor of the Dialect subclass, but this had no effect.
Code:
   public Oracle9SpatialDialect() {
      super();
      registerFunction("osgeometry", new StandardSQLFunction("MDSYS.SDO_GEOMETRY"));
   }


Unfortunately, the registerFunction() method is not documented in the api doc. Hence, I am just guessing here. Looking at the Oracle9Dialect class source did not give me a clue. I propably have to take better care of the arguement types of the sql function.

Any pointer to information on how to add sql functions would be appreciated.
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.