[mapserver-users] Dynamic SQL

[mark balman]

Hi All

Many thanks to Pietro Giannini and David Fawcett for your help but I
am still struggling with getting a dynamic sql statement to work. I
have gone back to trying a simple sql statement in the layer
definition which works fine like this:

LAYER
 CONNECTIONTYPE postgis
 NAME "kbapoly"
 CONNECTION "user="" password="" dbname=kba host=localhost"
 DATA "the_geom from (select gid, intname, the_geom from kbapoly where
intname = 'Tibesti massif') as myquery using SRID=4326 using unique
gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/kbas.html"
 CLASS
  COLOR 255 0 0
 END
 HEADER "templates/kbas_header.html"
 FOOTER "templates/kbas_footer.html"
END

However, if I try the following

LAYER
 CONNECTIONTYPE postgis
 NAME "kbapoly"
 CONNECTION "user="" password="" dbname=kba host=localhost"
 DATA "the_geom from (select gid, intname, the_geom from kbapoly where
intname = %qtest%) as myquery using SRID=4326 using unique gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/kbas.html"
 CLASS
  COLOR 255 0 0
 END
 HEADER "templates/kbas_header.html"
 FOOTER "templates/kbas_footer.html"
END

Together with the following in the template file:

<input type="hidden" name="Name" value="[qtest]">

<p><b>IntName: </b><br>
        <textarea name="IntName" rows=2 cols=5>[qtest]</textarea>
        <p>
        <hr>

This returns the following error

[Mon Oct 13 16:54:53 2008].386000 msDrawMap(): Image handling error.
Failed to draw layer named 'kbapoly'.
[Mon Oct 13 16:58:49 2008].733000 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
(select gid, intname, the_geom from kbapoly where intname = %qtest%)
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 "%"
LINE 1: ...d, intname, the_geom from kbapoly where intname = %qtest%) a...

Am I missing something obvious and doing something wrong?

Any further suggestions would be gratefully received

Thanks

Mark