问题描述
我正在尝试从C#执行参数化查询,但出现错误:
I am trying to execute a parametrised query from C# and I get the error:
带有提示
但是当我在pgAdmin III中运行查询时,将参数替换为它们的值,查询就可以了.代码是
But when I run the query in pgAdmin III replacing the parameters with their values the query works. The code is
command.CommandText = "SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((:minx :miny, :minx :maxy, :maxx :maxy, :maxx :miny, :minx :miny))'), shape) AND area_type_code = :typecode";
command.CommandType = CommandType.Text;
var typeCodeParameter = new NpgsqlParameter
{
DbType = DbType.String,
ParameterName = "typecode",
Value = _typeCode
};
var minxParameter = new NpgsqlParameter
{
DbType = DbType.Double,
ParameterName = "minx",
Value = _minX
};
var minyParameter = new NpgsqlParameter
{
DbType = DbType.Double,
ParameterName = "miny",
Value = _minY
};
var maxxParameter = new NpgsqlParameter
{
DbType = DbType.Double,
ParameterName = "maxx",
Value = _maxX
};
var maxyParameter = new NpgsqlParameter
{
DbType = DbType.Double,
ParameterName = "maxy",
Value = _maxY
};
command.Parameters.Add(typeCodeParameter);
command.Parameters.Add(maxxParameter);
command.Parameters.Add(maxyParameter);
command.Parameters.Add(minxParameter);
command.Parameters.Add(minyParameter);
using (var reader = command.ExecuteReader())
while (reader.Read())
areas.Add((string)reader["area_code"]);
工作查询是
SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((-1.0042576967558934 50.78431084582985, -1.0042576967558934 51.199216033050647, 1.9400782407441057 51.199216033050647, 1.9400782407441057 50.78431084582985, -1.0042576967558934 50.78431084582985))'), shape) AND area_type_code = 'County'
我做错了什么?我应该如何设置minx,miny,maxx,maxy参数?
What am I doing wrong? How should I be setting the minx, miny, maxx, maxy parameters?
推荐答案
问题是您要分隔坐标.这些参数不仅在SQL内部被同时击打(不仅仅是模板替换),还需要在语法上有效地放置参数.一个很好的检查方法是尝试相同的查询,但不要使用值直接替换参数,而应使用过程SQL和变量.
The problem is that you're separating the coordinates. The parameters aren't just slapped together inside the SQL (it's not just a template replace), it need to be a syntactically valid place to put a parameter. A good sanity check is to try the same query but instead of replacing the parameters with values directly, use procedural SQL and variables.
您将看到问题在于ST_GeographyFromText
函数不会在输入中得到的字符串内扩展参数-这是预期的行为.如果要使用此功能,则不能在字符串内部使用参数-您仍然只需要将所有值向上传递并将它们作为字符串传递-与删除参数"时所做的相同.最简单的解决方案可能是将整个字符串作为参数传递,或者只是将参数添加到查询中的字符串(例如ST_GeographyFromText('POLYGON((' || cast(:minx ...) || '), ' || ... || ')')
等)
You will see that the problem is that the ST_GeographyFromText
function doesn't expand parameters inside of the string it gets on input - this is an expected behaviour. If you want to use this function, you can't use parameters inside of the string - you still need to just all the values up and pass them as a string - the same thing you've done when you "removed the parameters". The simplest solution would probably be to pass the whole string as a parameter, or just add the parameters to a string in the query (e.g. ST_GeographyFromText('POLYGON((' || cast(:minx ...) || '), ' || ... || ')')
etc.)
似乎您正在使用参数化查询转换旧的连接一串字符串" SQL.坚持不懈,但是您需要考虑语法.就像您不能仅将子查询放入参数中一样,您也不能仅将单个值分成两个这样的参数.
It seems like you're converting old "concatenate a bunch of strings" SQL with parametrized queries. Do persevere, but you need to take the syntax into account. Just like you can't just put a subquery in a parameter, you can't just separate a single value into two parameters like this.
因此,您需要使用更好的类型,或者进行一些辅助转换(例如,需要两个浮点数并返回所需类型的函数).
So you need to either use a better type, or have some helper conversion (like a function that takes two floats, and returns the type you need).
这篇关于解析来自C#的PostgreSQL参数化查询中的错误,但可用于pgAdmin III的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!