错误:列索引超出范围:1，列数:0

I'm using wso2dss 3.0.0.I'm trying to execute a postgresql query i.e.

我用wso2dss 3.0.0。我正在尝试执行一个postgresql查询。

SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle '((18.9750,72.8258), 5)';

It is working fine in PostgreSQL.When i'm using same query in wso2dss i.e.

它在PostgreSQL中运行良好。当我在wso2dss中使用相同的查询时。

SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle '((?,?), ?)';

It gives me error like :

它给我的错误是:

DS Fault Message: Error in 'SQLQuery.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: Geofence_DataService
Location: /Geofence_DataService.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: adding_geofence_op
Current Params: {longitude=72.8258, radius=4, latitude=18.9750}
Nested Exception:-
DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

if i remove " ' "(quotation mark) of circle then also it will not execute. query '' look like this :

如果我删除圆圈的“'”(引号)，那么它也不会执行。查询“如下:

SELECT addressid, geocode FROM maddress WHERE geocode::point <@ circle ((?,?), ?);

从maddress中选择addressid, geocode::point <@ circle ((?，?)， ?);

it'll give following error :

会出现以下错误:

Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: Geofence_DataService
Location: /Geofence_DataService.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: geofence_op
Current Params: {longitude=72.8258, radius=4, latitude=18.9750}
Nested Exception:-
org.postgresql.util.PSQLException: ERROR: function circle(record, double precision) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type cast

But circle is inbuilt geographical function of PostgreSQL then is it necessary to write explicit function? else where is the exact error? Even if i put the query with input parameter as i execute in PostgreSQL then also it's working.If so then why it is not accepting dynamic parameters? Please let me know..

但圈是PostgreSQL内置的地理函数，那么是否有必要编写显式函数?否则，准确的误差在哪里?即使我在执行PostgreSQL时使用输入参数输入查询，它也可以工作。如果是，为什么它不接受动态参数?请让我知道。

1 个解决方案

#1

Geometric types can be input in multiple ways.

几何类型可以以多种方式输入。

In the first form, your ? parameters are not replaced with values because they are literal parts of a string. So 0 parameters are expected ...

在第一种形式中，你的?参数不会被值替换，因为它们是字符串的文字部分。所以0个参数是期望的。
In the second form without single quotes, your ? parameters are replaced, but ((18.9750,72.8258), 5) is interpreted to be a row type, which doesn't work with circle().

在没有单引号的第二种形式中，你的?参数被替换，但是(18.9750,72.8258)，5)被解释为行类型，与circle()不兼容。

You are trying to invoke the function circle() that takes a point and a double precision ("center and radius to circle"). These are valid syntax variants:

您正在尝试调用函数circle()，该函数具有一个点和一个双精度(“圆的中心和半径”)。这些是有效的语法变体:

SELECT circle '((18.9750,72.8258), 5)'        AS cast_literal
     ' <(18.9750,72.82580),5>'::circle        AS cast_literal2
     , circle(point '(18.9750,72.8258)', '5') AS literal_point_n_radius
     , circle(point(18.9750,72.8258), '5')    AS point_n_literal_radius
     , circle(point(18.9750,72.8258), 5)      AS point_n_radius

SQL fiddle.
_{The cast to ::text is just to sanitize the deranged display in SQL fiddle}

SQL小提琴。转换为::text仅仅是为了清除SQL fiddle中的异常显示

In your case, to provide numeric values (not a string literal), use the last form and it should work:

在您的例子中，要提供数值(而不是字符串文字)，请使用最后一个表单，它应该可以工作:

SELECT addressid, geocode
FROM   maddress
WHERE  geocode::point <@ circle(point(?,?), ?);

If wso2dss (which I have no experience with) does not accept functions, you have to use one of the first two forms and provide a single parameter as string literal:

如果wso2dss(我没有使用过)不接受函数，您必须使用前两种形式中的一种，并提供一个参数作为string literal:

SELECT addressid, geocode
FROM   maddress
WHERE  geocode::point <@ circle ?;

... where the parameter is the concatenated literal as displayed above.

…其中的参数是上面显示的连接文字。

You could let Postgres do the concatenation and still pass three numeric values:

您可以让Postgres执行连接，并仍然传递三个数值:

SELECT addressid, geocode
FROM   maddress
WHERE  geocode::point <@ ('(('::text || ? || ',' || ? || '),' || ? || ')')::circle;

#1