[mapserver-users] runtime substitution not working

[pcreso at pcreso.com]

Hi Ben...

I have it working in other setups fine - just this one won't behave.

If I use "like '%spp%'" & pass &spp=TAR, then the query that gets run is
"like 'TAR', which is basically an un-indexed =

I need a "like '%TAR%'" in the SQL hence the double % sign. I have this working elsewhere fine (but with different versions of mapserver & Postgis)

Capitalisation might be the problem... I'll try that...


Thanks,

   Brent
 

--- On Sun, 3/14/10, Ben Madin <lists at remoteinformation.com.au> wrote:

> From: Ben Madin <lists at remoteinformation.com.au>
> Subject: Re: [mapserver-users] runtime substitution not working
> To: mapserver-users at lists.osgeo.org
> Cc: pcreso at pcreso.com
> Date: Sunday, March 14, 2010, 1:25 PM
> G'day Brent,
>
> It might not matter, but you have double %% in the DATA
> statement you have passed,
>
> and for whatever reason - I recevied advice from this list
> or OpenLayers list - I have all my variable names
> capitalised in the query:
>
>            
>         WHERE transferdate
> >= '%STARTDATE%' AND transferdate <= '%ENDDATE%'
>            
>             AND
> line_geom IS NOT NULL
>            
>             AND
> sourceshire NOT LIKE destinationshire
>            
>             AND
> sourceshire LIKE '%SOURCESHIRE%'
>
> So I hope these might help if Steve's solution didn't
>
> cheers
>
> Ben
>
>
>
> On 13/03/2010, at 24:00 , mapserver-users-request at lists.osgeo.org
> wrote:
>
> > I'm obviously doing something wrong, but can't see
> what it is... I have a Postgis table of species occurences
> & I want to pass a variable to the mapfile to select
> which speciesusing runtime substitution.
> >
> > The relevant DATA statement (joining species in the
> catch to locations in the station) in the mapfile is:
> >
> > DATA "startp from (select c.id, s.startp, s.trip_code,
>
> >               
>              
> s.station_no, c.species,
> >               
>               c.weight
> >               
>        from t_station s, catch c
> >               
>        where
> s.station_no=c.station_no
> >               
>          and c.species ilike
> '%%spp%%')
> >               
>        as myquery
> >               
>        using unique id using
> srid=4326"
> >
> >
> > The query works in Postgis & in the mapfile (via
> QGIS) when I hard code the %spp% (eg, to 'TAR')
> >
> > I'm using QGIS to open the layer, with the URL:
> > http://wms-dev/cgi-bin/mapserv?map=/srv/www/htdocs/mapdata/catch.map&spp=TAR
> >
> > QGIS appends the request type, etc & it works with
> the hard coded "spp"
> >
> > The mapfile also has:
> >    METADATA
> >      "wms_title" "species"
> >     
> "wms_srs"   "epsg:4326"
> >      "wfs_title" "species"
> >      "wfs_version" "1.0.0"
> >      "wfs_typename" "species"
> >      "wfs_request_method" "GET"
> >      "wfs_service" "WFS"
> >      "spp_validation_pattern"
> '^[A-Z0-9][A-Z0-9][A-Z0-9]$'
> >    END
> >
> > The version I'm running is MapServer version 5.4.1
> > (so no default supported)
> >
> > The docs say I can use shp2img to test runtime
> substitution in a mapfile, but I can't see how to invoke the
> substitution with the equivalent of a spp=TAR statement to
> test this.