[mapserver-users] Dynamic SQL

[Bernd Deckert]

Hi Mark,

for the sql subquery you have probably to use quotes ''

select gid, intname, the_geom from kbapoly where intname = '%qtest%'

Hope it helps,

Bernd

mark balman a écrit :
> 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
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>
>   


-- 

Bernd DECKERT
bernd.deckert at geolabs.fr

GeoLabs
Future Building I
1280, avenue des Platanes
34970 Lattes
Tél. fixe : 04 67 53 67 37
Tél. portable : 06 70 08 25 39
mail: contact at geolabs.fr