Hibernate version:
1.0.3
Name and version of the database you are using:
Gupta SQLBase 9.0.1
I am trying to use a db-specific function in the SELECT part of the statement. Namely @NULLVALUE, the equivalent of IsNull in MSSQL, which takes to parameters and returns the first one - unless it is NULL, in which case it returns the second one.
Like this:
Code:
string replaceNull;
if ( session.Connection is System.Data.SqlClient.SqlConnection )
replaceNull = "IsNull";
else
replaceNull = "@nullvalue";
string query = string.Format( "select sum( {0}(foo.Number, bar.AltNumber) ) from (some more code...)", replaceNull );
Unfortunately I get the following exception:
Code:
NHibernate.QueryException: alias or expression expected in SELECT [select sum( @nullvalue(foo.Number, bar.AltNumber) ) from (some more code...)]
at NHibernate.Hql.SelectParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.ClauseParser.End(QueryTranslator q)
at NHibernate.Hql.PreprocessingParser.End(QueryTranslator q)
at NHibernate.Hql.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.QueryTranslator.Compile()
at NHibernate.Hql.QueryTranslator.Compile(ISessionFactoryImplementor factory, IDictionary replacements, Boolean scalar)
at NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow)
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at NHibernate.Impl.AbstractQueryImpl.UniqueResult()
I have successfully used DB-specific code in the WHERE elsewhere, so I guess the reason for that exception is that I call the function in the SELECT part.
This is confirmed for Hibernate
over here:
Quote:
Q: it seems that I can use proprietary SQL functions in the where clause of my HQL statements but not in the select clause. Is that a known issue, and if yes, is it planned to allow using SQL functions in the select clause?
A: Hibernate 2.1 allows you to specify Dialect-specific SQL functions, which may then be used in the SELECT clause.
Yet it seems like that functionality never made it into NHibernate, at least not v. 1.0.3 which we still use.
Anyway, I know the approach is kind of dirty, so I'm also willing to use this as motivation for writing my own dialect. Then I could define aliases for other functions as well (@NOW instead of getdate() etc.).
However, I haven't found any information on what one has to do in order to write one's one dialect.
I've had a look at the source code for the MsSql2000Dialect, but didn't understand how aliases can be defined.
Help will be highly appreciated.
Login
FAQ
Search
