错误：列索引超出范围：1，列数：0 [英] ERROR : The column index is out of range: 1, number of columns: 0

问题描述

我正在使用wso2dss 3.0.0。我正在尝试执行一个PostgreSQL查询，即

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

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

在PostgreSQL中工作正常。当我在wso2dss中使用相同查询时，即

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

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

它给了我这样的错误：

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 :

选择地址ID，地理编码
FROM maddress
WHERE geocode :: point< @ circle（（？，？ ），？）;

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

它将给出以下错误：

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

但是圆是PostgreSQL的内置地理功能，那么是否有必要写显式函数？否则确切的错误在哪里？即使我在PostgreSQL中执行查询时使用输入参数查询，它也可以正常工作。如果可以，为什么它不接受动态参数？请让我知道。.

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..

推荐答案

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

Geometric types can be input in multiple ways.

• 在第一种形式中，您的？参数不会替换为值，因为它们是字符串的文字部分。因此，预期会有0个参数...

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

在不带单引号的第二种形式中，您的？参数被替换，但（（（18.9750,72.8258），5）被解释为行类型，​​不适用于 circle（）。

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().

您正在尝试调用函数 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:

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提琴。

_{强制转换为 :: text 只是为了清理SQL提琴中乱码的显示内容}

SQL fiddle.
_{The cast to ::text is just to sanitize the deranged display in 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(?,?), ?);

如果wso2dss（我没有经验）不接受功能，则必须使用以下一种前两种形式，并提供 单 参数作为字符串文字：

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:

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

...其中参数是如上所示的连接文字。

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

您可以让Postgres进行连接并仍然传递三个数值：

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

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

这篇关于错误：列索引超出范围：1，列数：0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！