解析来自C#的PostgreSQL参数化查询中的错误，但可用于pgAdmin III [英] Parse error in PostgreSQL parametrised query from C# but works in pgAdmin III

问题描述

我正在尝试从C#执行参数化查询，但出现错误:

I am trying to execute a parametrised query from C# and I get the error:

错误:XX000:分析错误-无效的几何图形

ERROR: XX000: parse error - invalid geometry

带有提示

"POLYGON((:: m"<-解析几何图形中位置11处的错误

"POLYGON((:m" <-- parse error at position 11 within geometry

但是当我在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！