<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">I see what Ben was worried about and what you're trying to do. I haven't done a lot of variable substitution but I have done a lot of head scratching to when queries don't work as expected. A couple things that might shed some light you could try:<br><br>"like '%spp%'" and pass &spp=%TAR%<br><br>If you're looking for a string containing TAR. It might confirm that the double %% are being misinterpreted. <br><br>Or you could try if this is the case and you want to force the wildcards at the mapfile level. <br>"like '%' || '%spp%' || '%"'<br><br>Josh<br><br>--- On <b>Sun, 3/14/10, pcreso@pcreso.com <i><pcreso@pcreso.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: pcreso@pcreso.com <pcreso@pcreso.com><br>Subject: Re: [mapserver-users] runtime
substitution not working<br>To: mapserver-users@lists.osgeo.org<br>Date: Sunday, March 14, 2010, 1:29 PM<br><br><div class="plainMail"><br>Hi Ben...<br><br>I have it working in other setups fine - just this one won't behave.<br><br>If I use "like '%spp%'" & pass &spp=TAR, then the query that gets run is<br>"like 'TAR', which is basically an un-indexed =<br><br>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)<br><br>Capitalisation might be the problem... I'll try that...<br><br><br>Thanks,<br><br> Brent<br> <br><br>--- On Sun, 3/14/10, Ben Madin <<a ymailto="mailto:lists@remoteinformation.com.au" href="/mc/compose?to=lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>> wrote:<br><br>> From: Ben Madin <<a ymailto="mailto:lists@remoteinformation.com.au"
href="/mc/compose?to=lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>><br>> Subject: Re: [mapserver-users] runtime substitution not working<br>> To: <a ymailto="mailto:mapserver-users@lists.osgeo.org" href="/mc/compose?to=mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>> Cc: <a ymailto="mailto:pcreso@pcreso.com" href="/mc/compose?to=pcreso@pcreso.com">pcreso@pcreso.com</a><br>> Date: Sunday, March 14, 2010, 1:25 PM<br>> G'day Brent,<br>><br>> It might not matter, but you have double %% in the DATA<br>> statement you have passed,<br>><br>> and for whatever reason - I recevied advice from this list<br>> or OpenLayers list - I have all my variable names<br>> capitalised in the query:<br>><br>> <br>> WHERE transferdate<br>> >= '%STARTDATE%' AND transferdate <=
'%ENDDATE%'<br>> <br>> AND<br>> line_geom IS NOT NULL<br>> <br>> AND<br>> sourceshire NOT LIKE destinationshire<br>> <br>> AND<br>> sourceshire LIKE '%SOURCESHIRE%'<br>><br>> So I hope these might help if Steve's solution didn't<br>><br>> cheers<br>><br>> Ben<br>><br>><br>><br>> On 13/03/2010, at 24:00 , <a ymailto="mailto:mapserver-users-request@lists.osgeo.org" href="/mc/compose?to=mapserver-users-request@lists.osgeo.org">mapserver-users-request@lists.osgeo.org</a><br>> wrote:<br>><br>> > I'm obviously doing something wrong, but can't see<br>> what it is... I have a Postgis table of species
occurences<br>> & I want to pass a variable to the mapfile to select<br>> which speciesusing runtime substitution.<br>> ><br>> > The relevant DATA statement (joining species in the<br>> catch to locations in the station) in the mapfile is:<br>> ><br>> > DATA "startp from (select c.id, s.startp, s.trip_code,<br>><br>> > <br>> <br>> s.station_no, c.species,<br>> > <br>> c.weight<br>> > <br>> from t_station s, catch c<br>> > <br>> where<br>> s.station_no=c.station_no<br>> >
<br>> and c.species ilike<br>> '%%spp%%')<br>> > <br>> as myquery<br>> > <br>> using unique id using<br>> srid=4326"<br>> ><br>> ><br>> > The query works in Postgis & in the mapfile (via<br>> QGIS) when I hard code the %spp% (eg, to 'TAR')<br>> ><br>> > I'm using QGIS to open the layer, with the URL:<br>> > <a href="http://wms-dev/cgi-bin/mapserv?map=/srv/www/htdocs/mapdata/catch.map&spp=TAR" target="_blank">http://wms-dev/cgi-bin/mapserv?map=/srv/www/htdocs/mapdata/catch.map&spp=TAR</a><br>> ><br>> > QGIS appends the request type, etc & it works with<br>> the hard coded "spp"<br>> ><br>> >
The mapfile also has:<br>> > METADATA<br>> > "wms_title" "species"<br>> > <br>> "wms_srs" "epsg:4326"<br>> > "wfs_title" "species"<br>> > "wfs_version" "1.0.0"<br>> > "wfs_typename" "species"<br>> > "wfs_request_method" "GET"<br>> > "wfs_service" "WFS"<br>> > "spp_validation_pattern"<br>> '^[A-Z0-9][A-Z0-9][A-Z0-9]$'<br>> > END<br>> ><br>> > The version I'm running is MapServer version 5.4.1<br>> > (so no default supported)<br>> ><br>> > The docs say I can use shp2img to test runtime<br>> substitution in a mapfile, but I can't see how to invoke the<br>> substitution with the equivalent of a spp=TAR statement to<br>> test
this.<br><br>_______________________________________________<br>mapserver-users mailing list<br><a ymailto="mailto:mapserver-users@lists.osgeo.org" href="/mc/compose?to=mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br></div></blockquote></td></tr></table>