HQL upper function not supported in Access Dialect
Description
I got this error when using the Access Dialect. Here is the code which works fine in the SQL-server 2000 dialect: <CODE> string query = "Select cate FROM Category_UN as cate WHERE UPPER(cate.Name) like '%" + keyword + "%'" IList result = sess.Find(query); </CODE>
Here is the exception I got:
Undefined function 'UPPER' in expression. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Undefined function 'UPPER' in expression.
Source File: C:\Documents and Settings\kwang\My Documents\Visual Studio Projects\mmpire\MHContainer\DomainTemplates\DomainOnNHibernate\Sessions.cs Line: 209
This is a duplicate and is already fixed. See for more info but in brief, UPPER and LOWER function in JET are UCASE and LCASE respectively and these are already replaced with correct jet functions.
KailuoK
January 12, 2006 at 2:53 PM
I didn't search the Jet function reference, but I also guess what you said is probabaly the reason. I am familiar with neither Access nor Jet. I just assumed that a DB dialect should fulfil all HQL functions, if that's not neccessarily hold, please remove this issue. It's very trival to our development. I posted it just just FYI. I do want to take this chance to thank the NHibernate team for this great work. It made .Net development a real pleasure to me. Can't wait until NHibernate 2.0 come out.
Former user
January 12, 2006 at 1:16 PM
Well, I searched the net now and couldn't find a Jet function reference. Could it be that Jet relies on Access (VBA) to provide the UPPER function, and that the function isn't actually built in? Note also that the error is raised inside OleDbCommand, not inside NHibernate.
KailuoK
January 12, 2006 at 12:01 PM
Access do have UPPER function, so in theory, the upper function in HQL should be supported, isn't it. not sure if it's a bug though, but I think it's an issue. I appologize if i posted it to a wrong place.
Former user
January 12, 2006 at 11:45 AM
Looks like it's a problem with Access (Jet), not with NHibernate.
I got this error when using the Access Dialect.
Here is the code which works fine in the SQL-server 2000 dialect:
<CODE>
string query = "Select cate FROM Category_UN as cate WHERE UPPER(cate.Name) like '%" + keyword + "%'"
IList result = sess.Find(query);
</CODE>
Here is the exception I got:
Undefined function 'UPPER' in expression.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Undefined function 'UPPER' in expression.
Source File: C:\Documents and Settings\kwang\My Documents\Visual Studio Projects\mmpire\MHContainer\DomainTemplates\DomainOnNHibernate\Sessions.cs Line: 209
Stack Trace:
[OleDbException (0x80040e14): Undefined function 'UPPER' in expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +69
System.Data.OleDb.OleDbCommand.ExecuteReader() +7
NHibernate.JetDriver.JetDbCommand.System.Data.IDbCommand.ExecuteReader() +23
NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd) +108
NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session) +249
NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies) +286
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies) +75
NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes) +272
NHibernate.Hql.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters) +64
NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters) +230