ERROR : The column index is out of range: 1, numbe

问题:

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)';

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 '((?,?), ?)';    

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 ((?,?), ?);

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

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

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

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 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(?,?), ?);

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.

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

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