Using SQL Server 2008 Geography types with nHibernate's CreateSQLQuery

Question

I am trying to issue a SQL update statement with nHibernate (2.0.1GA) like this:

sqlstring = string.Format("set nocount on;update myusers set geo=geography::Point({0}, {1}, 4326) where userid={2};", mlat, mlong, userid);
_session.CreateSQLQuery(sqlstring).ExecuteUpdate();

However I receive the following error: 'geography@p0' is not a recognized built-in function name.

I thought CreateSQLQuery would just pass the SQL I gave it and execute it...guess not. Any ideas on how I can do that within the context of nHibernate?

Answer 1

"{whatever} is not a recognized built-in function name" is a SQL Server error message, not sure what Hibernate is doing there but SQL Server is the one complaining about it.

Answer 2

I'm pretty sure I can tell you what is happening, but I don't know if there is a fix for it.

I think the problem is that the ':' character is used by NHibernate to create a named parameter. Your expression is getting changed to:

set nocount on;update myusers set geo=geography@p0({0}, {1}, 4326) where userid={2};

And @p0 is going to be a SQL variable. Unfortunately I can't find any documentation for escaping colons so they are not treated as a named parameter.

If an escape character exists (my quick skim of the NHibernate source didn't find one; Named parameters are handled in NHibernate.Engine.Query.ParameterParser if you want to spend a little more time searching), then you could use that.

Other solutions:

• Add an escape character to the source. You can then use a modified version of NHibernate. If you do this, you should submit your patch to the team so it can be included in the real thing and you don't have to maintain a modified version of the source (no fun).
• Create a user defined function in your DB that returns a geography::Point, then call your function instead of the standard SQL function. This seems like the quickest/easiest way to get up and running, but also feels a bit like a band-aid.
• See if there is something in NHibernate Spatial that will let you programmatically add the geography::Point() [or edit the code for that project to add one and submit the patch to that team].