Hibernate version: 3.0.5
I want to use a Microsoft SQL server user defined user function from Hibernate, in HQL statement in a report query.

Code between sessionFactory.openSession() and session.close():

Query theQuery9 = session.createQuery("select dbo.funDecrypt(df.ccriteria3Dfr,'ABCDEFGH') from DataformIrmdfr df");

Full stack trace of any exception that occurs:
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.MethodNode
+-[METHOD_CALL] MethodNode: '('
| \-[METHOD_NAME] SqlNode: 'exprList'
| +-[DOT] DotNode: 'dataformir0_.CCRITERIA3_DFR' {propertyName=ccriteria3Dfr,dereferenceType=4,propertyPath=ccriteria3Dfr,path=df.ccriteria3Dfr,tableAlias=dataformir0_,className=com.atosorigin.mev.gms.irm.hibernate.business.objects.DataformIrmdfr,classAlias=df}
| | +-[ALIAS_REF] IdentNode: '(dataformir0_.NIDENTIFIER_DFRFRM, dataformir0_.NIDENTIFIER_DFR)' {alias=df, className=com.atosorigin.mev.gms.irm.hibernate.business.objects.DataformIrmdfr, tableAlias=dataformir0_}
| | \-[IDENT] IdentNode: 'ccriteria3Dfr' {originalText=ccriteria3Dfr}
| \-[QUOTED_STRING] LiteralNode: ''ABCDEFGH''

at org.hibernate.hql.ast.SelectClause.initializeExplicitSelectClause(SelectClause.java:136)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:440)
at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:351)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.afterQuery(HqlSqlBaseWalker.java:126)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:471)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:201)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:151)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at test.main(test.java:179)

Name and version of the database I'm using:
Microsoft SQL Server 2000

I try with the non user defined functions (ex. ltrim function) and the HQL works correctly:
Query theQuery9 = session.createQuery("select ltrim(df.ccriteria3Dfr) from DataformIrmdfr df");


Can someone help me ??

Thanks,

I have the same problem. I'm guessing that HQL takes issues with the dot (".") notation.... expecting it to be a mapped class or something. Anyway, I couldn't find anything in the forums on it, but we can't be the only people using user-defined functions with MSSQL & Hibernate can we??
Maybe it's a new issue in 3.x. Unfortunately, it appears MSSQL requires us to prepend the function name with the owner. I hope somebody has a resolution or I'm royally screwed ;(

Just write a SQLFunction than translates from a name w/o a dot to the name with the dot.

Sorry if I'm misunderstanding your post. But the way I read it, is that you're saying the solution is to write another function (using TSQL in MSSQL) and call that in the HQL.
The problem, is that *any* function that you write in MSSQL (they call "user defined function") can only be called this way:

owner.function(param1, param2,...)

In other words, the 'owner' prefix is not optional. It's crazy. But I've googled it quite a bit and it doesn't look like there's anyway of getting around the dot notation which I think HQL doesn't like.

So bottom line is, if my assumptions are correct, we wouldn't be able to call the function that you're suggesting we write. Or am I just totally missing the ball?

Well the problem hasn't gone away... (for me anyway). Please, if anyone uses user-defined functions with hibernate and SQL Server, post here and let me know what versions.

Otherwise, as a last resort, I could fix this for myself temporarily if I could modify the SQL between the time it is generated by Hibernate and it is sent to the database. Anybody have a suggestion on the best place in Hibernate to do this? I looked into Hibernate events and nothing jumped out at me. Maybe interceptors, or do I need to patch it myself? If somebody could just point me in the right direction, that would be really helpful.

Thanks!