ERROR: column index is out of range: 1, number of columns: 0

Question

I am using wso2dss 3.0.0. I am trying to execute a postgresql ie request

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

It works great in PostgreSQL. When I use the same request in wso2dss ie

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

This gives me an error, for example:

 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 delete the " ' " ( quotation mark ) of the circle, then it will not be executed. query '' looks like this:

SELECT addressid, geocode From the crazy bag WHERE geocode :: point <@circle ((?,?) ,?);

this will result in the 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 the circle is a built-in geographical function of PostgreSQL, then do I need to write an explicit function? else where is the exact error? Even if I put a query with an input parameter, as I do in PostgreSQL, then it works. If then why doesn’t it accept dynamic parameters? Please let me know..

Answer 1

Geometric types can be entered in several ways.

• In the first form, the parameters ? are not replaced by values, as they are literal parts of a string. Thus, 0 parameters are expected ...

• In the second form without single quotes, your options ? are replaced, but ((18.9750,72.8258), 5) interpreted as a row type that does not work with circle() .

You are trying to call the circle() function, which takes point and a double precision ("center and radius for circle"). These are valid syntax options:

 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 script.
_{Casting to ::text is just to clear the displayed screen in SQL script}

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

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

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

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

... where the parameter is a concatenated literal, as shown above.

You can let Postgres concatenate and still pass in three numerical values:

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