[mapserver-users] dynamic sql

[mark balman]

Hi all

Many thanks for your iputs to this. I have managed to get the simple
query as follows working;

DATA "the_geom from (select gid, intname, the_geom from kbapoly where
intname = '%mquery%') As myquery using SRID=4326 using unique gid"

It was a simple case of quoting the %mquery% (thought I had tried that..)

Now I want to move onto my original query that allows a user to enter
a latitude, longitude and a distance and show all polygons that fall
within the given buffer distance

e.g.

DATA "the_geom FROM (select * from kbapoly where
st_distance(the_geom,GeomFromText('Point(0 1)',4326)) < 6) AS myquery
using SRID=4326 using unique gid

I have tried the following:

DATA "the_geom FROM ( '%mquery%') AS myquery using SRID=4326 using unique gid

and entering the appropriate sql into the text box

select * from kbapoly where st_distance(the_geom,GeomFromText('Point(0
1)',4326)) < 6

returns the error message:

msDrawMap(): Image handling error. Failed to draw layer named
'kbapoly'. prepare_database(): Query error. Error executing POSTGIS
DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR
FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from ('%(mquery)%') AS myquery WHERE the_geom &&
setSRID('BOX3D(-179.699499165275 -120.200333889816,180.300500834725
119.599332220367)'::BOX3D, 4326 )' Postgresql reports the error as
'ERROR: syntax error at or near "'%(mquery)%'" LINE 1:
...ection(force_2d(the_geom)),'NDR'),gid::text from ('%(mquery)... ^

I am going to have a go at using Pietro Giannini idea of using his
javascript idea, any more insights would be most welcomed

Thanks again

Mark