[postgis-users] problem in dynamic SQL with Mapserver

[Simpson]

Nimalika,

You are only selecting the geom from the table.  You should also select any
other columns that you may be using for labeling, filtering, etc in your
.map file.


DATA "the_geom from (select alldsdivisions.dsd_n, alldsdivisions.the_geom as
the_geom from alldsdivisions where alldsdivisions.dsd_n ='%qstring%')
   AS selectedgs
   USING UNIQUE oid USING SRID=-1"

Drew
  -----Original Message-----
  From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of nimalika
fernando
  Sent: Sunday, February 25, 2007 2:06 AM
  To: postgis-users at postgis.refractions.net
  Subject: [postgis-users] problem in dynamic SQL with Mapserver


  Hi everybody,

  I'm working with some post-disaster management support tool with mapping
forcusing on Tsunami Rehabilation.

  I have managed to use postGIS / UMN mapserver for basic stuff like select
data from different tables , join them and dislaying information according
to predefined selections.

  Now I want to add a very simple serching facility. For an example if user
already knows the name of an affected village name,by selecting the name (
from a drop down menu), the area should be highlighed on the map.

  I tried to pass user selection as a variable to postGIS query but I
failed.
  code segment is as follows

  DATA "the_geom from (select alldsdivisions.the_geom as the_geom from
alldsdivisions where alldsdivisions.dsd_n ='%qstring%')
     AS selectedgs
     USING UNIQUE oid USING SRID=-1"

  qstring is the variable name use in HTML file.It is replaced by user
selection dynamically.
  alldsdivisions is the relation name and dsd_n is the column name of
village names.

  i get following error message

  prepare_database(): Query error. Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
dsd_n::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
from (select alldsdivisions.the_geom as the_geom from alldsdivisions where
alldsdivisions.dsd_n ='Ambalantota') AS selectedgs WHERE (Ambalantota) and
(the_geom && setSRID( 'BOX3D(181659.373889 74242.41425975,303213.047119
165407.66918225)'::BOX3D,-1) )'

  Postgresql reports the error as 'ERROR: column "dsd_n" does not exist '

  but dsd_n column do exist .

  I can't figureout how to handle this.
  any help is highly appriciated.


  thanking in advance.

  nimalika
  ( from Sri Lanka-island in indian ocean)





----------------------------------------------------------------------------
--
  Finding fabulous fares is fun.
  Let Yahoo! FareChase search your favorite travel sites to find flight and
hotel bargains.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070225/dbf85452/attachment.html>