[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-158) Customizable SQL

[Mikheil K. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HB-158?page=comments#action_23616 ] 

Mikheil Kapanadze commented on HB-158:
--------------------------------------

Hello,

I'm trying to use Oracle Text in my project and I ned to call function named "score" in my SQL statement.

For this, I have subclassed Oracle9Dialect (see below)

public class MyOracle9Dialect
    extends net.sf.hibernate.dialect.Oracle9Dialect {
    public Oracle9Dialect() {
        super();
        registerFunction("score", new StandardSQLFunction(Hibernate.INTEGER));
    }
}

but the problem is that score function takes constant as its parameter (score(1) for me). So, HQL looks like the following:

select  score(1), wbSwsData  from WbSwsData wbSwsData  
where   wbSwsData.siteId = :sites and  wbSwsData.localeAbbrev in (:locales) and (contains(wbSwsData.localeAbbrev, ' (({one} and {two}) within title)*6  ,  (({one} and {two}) within description)*4 ',1) > 0 ) 

The problem is that when I'm doing list() and iterating over it, row type is not Object[2] in which Object[0] has type Integer, Object[1] - WbSwsData (as It was expected) but the list contains WbSwsData objects. In other words, score(1) is completely ignored by Hibernate

I have tried to do some experiments with built-in functions. For example - sign(). The result was the following:
1. If I call sign(1) there, this fragment is ignored (as in score's case) and WbSwsData objects are returned
2. If I call sign(wbSwsData .id) or something like that, everything is ok. Query result contains Object[2] arrays.

I have tried to call score(1 + wbSwsData .id - wbSwsData .id) as a workaround. Unfortunately, Oracle does not support such kind of things for score() function and it throws "ORA-29909: label for the ancillary operator is not a literal number"

Is there any way in HQL to do it?

Thank you

> Customizable SQL
> ----------------
>
>          Key: HB-158
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-158
>      Project: Hibernate2
>         Type: New Feature

>   Components: core
>     Versions: 2.0.1
>     Reporter: TURIN ACCOUNT

>
>
> Ability to be able to get the SQL Hibernate would send to the database for a given query,
> be able to look at it and possibly tweak it (as a String) and then allow Hibernate to
> continue - sending the SQL on its way to the database.  This allows for subtle hints and
> other database tricks one is often forced to do when you don't "own" the datamodel.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira