I am trying to issue a SQL update statement with nHibernate (2.0.1GA) like this:
我正在尝试用nHibernate (2.0.1GA)发出一个SQL update语句:
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.
但是，我收到以下错误:'geography@p0'不是一个公认的内置函数名。
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?
我认为CreateSQLQuery会传递我给它的SQL并执行它…猜不是。在nHibernate的背景下我怎么做呢?
4 个解决方案
#1
4
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:
我认为问题在于，NHibernate使用了“:”字符来创建一个命名参数。你的表情变成:
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.
@p0是一个SQL变量。不幸的是，我找不到任何转义冒号的文档，因此它们不被视为命名参数。
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.
如果存在转义字符(我对NHibernate源代码的快速浏览没有找到;命名参数在nhibernate . engineering . query中处理。如果您想花更多时间搜索的话，可以使用ParameterParser。
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).
- 向源添加转义字符。然后可以使用修改后的NHibernate。如果您这样做，您应该向团队提交您的补丁，这样它就可以包含在真实的东西中，并且您不需要维护源代码的修改版本(没有乐趣)。
- 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.
- 在DB中创建一个用户定义的函数，返回一个geography: Point，然后调用函数而不是标准的SQL函数。这似乎是最快/最简单的起床和跑步的方式，但也有点像创可贴。
- 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].
- 看看NHibernate Spatial中是否有什么东西可以让您以编程方式添加geography: Point()[或者编辑该项目的代码以添加一个并将补丁提交给该团队]。
#2
0
"{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.
“{whatever}不是公认的内置函数名”是一条SQL Server错误消息，不确定Hibernate在那里做什么，但是SQL Server才是抱怨它的人。
#3
0
There is an implicit conversion from varchar to Point.
从varchar到Point有一个隐式转换。
Use NHibernate to set the geographic parameters to their string representation
使用NHibernate将地理参数设置为它们的字符串表示形式
Define a SQL query template with named paramter loc:
定义一个名为paramter loc的SQL查询模板:
const string Query = @"SELECT {location.*}
FROM {location}
WHERE {location}.STDistance(:loc) is not null
ORDER BY {location}.STDistance(:loc)";
Set the parameter to a string representation of Point:
将参数设置为点的字符串表示形式:
return session
.CreateSQLQuery(Query)
.AddEntity("location", typeof (Location))
.SetString("loc", "Point (53.39006999999999 -3.0084007)")
.SetMaxResults(1)
.UniqueResult<Location>();
This is for a Select. but I see no reason why it wouldn't work for an Insert or Update.
这是一个选择。但是我看不出为什么它不能用于插入或更新。
#4
0
Following on @Chris's answer, here is a copy and paste solution:
根据@Chris的回答，这里有一个复制粘贴解决方案:
CREATE FUNCTION GetPoint
(
@lat float,
@lng float,
@srid int
)
RETURNS geography
AS
BEGIN
declare @point geography = geography::Point(@lat, @lng, @srid);
RETURN @point
END
GO
The you do
你做
dbo.GetPoint(@Latitude, @Longitude, 4326)
instead of
而不是
geography::Point(@Latitude, @Longitude, 4326);
And NH is happy
NH是快乐的
#1
4
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:
我认为问题在于，NHibernate使用了“:”字符来创建一个命名参数。你的表情变成:
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.
@p0是一个SQL变量。不幸的是，我找不到任何转义冒号的文档，因此它们不被视为命名参数。
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.
如果存在转义字符(我对NHibernate源代码的快速浏览没有找到;命名参数在nhibernate . engineering . query中处理。如果您想花更多时间搜索的话，可以使用ParameterParser。
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).
- 向源添加转义字符。然后可以使用修改后的NHibernate。如果您这样做，您应该向团队提交您的补丁，这样它就可以包含在真实的东西中，并且您不需要维护源代码的修改版本(没有乐趣)。
- 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.
- 在DB中创建一个用户定义的函数，返回一个geography: Point，然后调用函数而不是标准的SQL函数。这似乎是最快/最简单的起床和跑步的方式，但也有点像创可贴。
- 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].
- 看看NHibernate Spatial中是否有什么东西可以让您以编程方式添加geography: Point()[或者编辑该项目的代码以添加一个并将补丁提交给该团队]。
#2
0
"{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.
“{whatever}不是公认的内置函数名”是一条SQL Server错误消息，不确定Hibernate在那里做什么，但是SQL Server才是抱怨它的人。
#3
0
There is an implicit conversion from varchar to Point.
从varchar到Point有一个隐式转换。
Use NHibernate to set the geographic parameters to their string representation
使用NHibernate将地理参数设置为它们的字符串表示形式
Define a SQL query template with named paramter loc:
定义一个名为paramter loc的SQL查询模板:
const string Query = @"SELECT {location.*}
FROM {location}
WHERE {location}.STDistance(:loc) is not null
ORDER BY {location}.STDistance(:loc)";
Set the parameter to a string representation of Point:
将参数设置为点的字符串表示形式:
return session
.CreateSQLQuery(Query)
.AddEntity("location", typeof (Location))
.SetString("loc", "Point (53.39006999999999 -3.0084007)")
.SetMaxResults(1)
.UniqueResult<Location>();
This is for a Select. but I see no reason why it wouldn't work for an Insert or Update.
这是一个选择。但是我看不出为什么它不能用于插入或更新。
#4
0
Following on @Chris's answer, here is a copy and paste solution:
根据@Chris的回答，这里有一个复制粘贴解决方案:
CREATE FUNCTION GetPoint
(
@lat float,
@lng float,
@srid int
)
RETURNS geography
AS
BEGIN
declare @point geography = geography::Point(@lat, @lng, @srid);
RETURN @point
END
GO
The you do
你做
dbo.GetPoint(@Latitude, @Longitude, 4326)
instead of
而不是
geography::Point(@Latitude, @Longitude, 4326);
And NH is happy
NH是快乐的