[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3227) Oracle's connect by syntax is mishandled when used with Filter

[Sean Molloy (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38493#action_38493 ] 

Sean Molloy commented on HHH-3227:
----------------------------------

I have been able to solve this by creating a custom Dialect that extends the Oracle10g dialect, and registering the CONNECT BY PRIOR START WITH words as keywords.  Here is my class:


public class Oracle10gDialectFixed extends Oracle10gDialect {
	public Oracle10gDialectFixed() {
        super(); 
        // workaround for http://opensource.atlassian.com/projects/hibernate/browse/HHH-2304 
        registerHibernateType(Types.CHAR,1,   Hibernate.CHARACTER.getName()); 
        registerHibernateType(Types.CHAR,255, Hibernate.STRING.getName()); 
        
        // workaround for http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227 
        registerKeyword("CONNECT");
        registerKeyword("PRIOR");
        registerKeyword("START");
        registerKeyword("WITH");
        
        registerKeyword("connect");
        registerKeyword("prior");
        registerKeyword("start");
        registerKeyword("with");
	}
	
}

And then I use this dialect in my persistence.xml and it works!

> Oracle's connect by syntax is mishandled when used with Filter
> --------------------------------------------------------------
>
>                 Key: HHH-3227
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3227
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.2.5
>         Environment: Hibernaet 3.2.5.GA, Oracle 10g.
>            Reporter: Kyrill Alyoshin
>
> Basically, I have a filter definition on a class (via annotation), here it is:
> @Filter(name = "limitInvoicesByAccountHolderHierarchy",
>         condition = "CHRG_ACCT_ID in (" +
>                 "select ca1.chrg_acct_id from cv_chrg_acct ca1 " +
>                 "inner join cv_org_unit og1 on ca1.acct_hldr_id = og1.org_unit_id " +
>                 "where og1.org_unit_id in (" +
>                 "select og2.org_unit_id from cv_org_unit og2 " +
>                 "start with og2.org_unit_id = :root " +
>                 " connect by prior og2.org_unit_id = og2.hier_par_org_unit_id" +
>                 ')' +
>                 ')'
> It contains Oracle's "connect by" clause. Here is the SQL statement that Hibernate generates when trying to query invoices with the filter enabled:
> Hibernate: 
>     /* 
> from
>     Invoice */ select
>         invoice0_.inv_id as inv1_31_,
>         ...... 
>     from
>         CV_INV invoice0_ 
>     where
>         invoice0_.CHRG_ACCT_ID in (
>             select
>                 ca1.chrg_acct_id 
>             from
>                 cv_chrg_acct ca1 
>             inner join
>                 cv_org_unit og1 
>                     on ca1.acct_hldr_id = og1.org_unit_id 
>             where
>                 og1.org_unit_id in (
>                     select
>                         og2.org_unit_id 
>                     from
>                         cv_org_unit og2 invoice0_.start invoice0_.with og2.org_unit_id = ?  invoice0_.connect 
>                     by
>                         invoice0_.prior og2.org_unit_id = og2.hier_par_org_unit_id
>                 )
>             )
> Even though the documentation states that the condition of the filter is pure SQL (which is great!), it still does not pass SQL verbatim but is actually trying to pre-process the following words: start, with, connect, prior.
> Is there an easy work around to make Oracle10gDialect recognize these words?
> Thanks a lot for looking into this!

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