many-to-one formula has table name appended to SQL keyword

Question

I am working with a legacy database and am trying to convert a char to an int for a many-to-one mapping. I know this isn't a great idea but I'm stuck with the schema as it is.

I have tried to do this with the following mapping but have found that NHibernate is adding the table name alias onto the keyword SQL_INTEGER.

<many-to-one name="host" formula="CONVERT(alloc_code, SQL_INTEGER)" />

Generates the SQL

CONVERT(hostplacem0_.alloc_code, hostplacem0_.SQL_INTEGER)

I have found this post but the solution is not a workable one for my situation.

One thing to note is that we have rolled our own dialect for the DBMS we use (Sybase Advantage Database Server) since NHibernate does not support it out of the box. Therefore I have the option of amending this to address this issue. I have already tried registering SQL_INTEGER as a keyword but that didn't help.

Random thoughts, but you may give a try to RegisterFunctions in the same dialect file, see https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Dialect/MsSql2000Dialect.cs for example. The str declaration seems interesting — jbl, Mar 19 '13 at 09:01
Thanks for the suggestion @jbl. It turns out it was a matter of case sensitivity. Please see my answer below. — mickfold, Mar 19 '13 at 09:53

score 1 · Accepted Answer · edited May 22 '14 at 14:30

After spending quite a few hours going through the NHibernate source I was finally able to identify why RegisterKeyword("SQL_INTEGER") did not work. The method Template.RenderWhereStringTemplate is used to generate the formula SQL. This method calls ToLowerInvariant() on each string token it processes, so when the method checks the list of reserved keywords it looks for sql_integer and therefore cannot find it. After changing the the call to RegisterKeyword("sql_integer") in our custom dialect the correct SQL was generated:

CONVERT(hostplacem0_.alloc_code, SQL_INTEGER)

For completeness I also checked to see if it was possible to inject in new keywords by accessing the dialect class in SessionFactoryImpl and calling RegisterKeyword by reflection, i.e.

var factory_impl = factory as SessionFactoryImpl;
if (factory_impl != null)
{
  MethodInfo mInfoMethod = typeof(Dialect).GetMethod(
         "RegisterKeyword",
         BindingFlags.Instance | BindingFlags.NonPublic,
         Type.DefaultBinder,
         new[] { typeof(string) },
         null);

  mInfoMethod.Invoke(factory_impl.Dialect, new object[] { "sql_integer" });
}

While this code works NHibernate still emits the incorrect SQL. Unforunately it looks like NHibernate creates a new instance of the dialect everytime a dialect object is requested using the Dialect.GetDialect method.

I guess the only way around this problem is to do what we did. Create your own dialect either completely, like in our situation, or by sub-classing from an existing dialect and adding the call for RegisterKeyword into the constructor.

many-to-one formula has table name appended to SQL keyword

1 Answers1