<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-15">
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY>
<DIV>Use double %%</DIV>
<DIV> </DIV>
<DIV>"like '%%spp%%'" and pass &spp=TAR</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV> </DIV>
<DIV>Robert<BR><BR>>>> Joshua Hevenor <jhevenor@rogers.com> 15/03/2010 1:03 p.m. >>><BR></DIV>
<TABLE cellSpacing=0 cellPadding=0 border=0>
<TBODY>
<TR>
<TD vAlign=top>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 defanghtml_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 href="/mc/compose?to=lists@remoteinformation.com.au" defanghtml_ymailto="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</A>> wrote:<BR><BR>> From: Ben Madin <<A href="/mc/compose?to=lists@remoteinformation.com.au" defanghtml_ymailto="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</A>><BR>> Subject: Re: [mapserver-users] runtime substitution not working<BR>> To: <A href="/mc/compose?to=mapserver-users@lists.osgeo.org" defanghtml_ymailto="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</A><BR>> Cc: <A href="/mc/compose?to=pcreso@pcreso.com" defanghtml_ymailto="mailto: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 href="/mc/compose?to=mapserver-users-request@lists.osgeo.org" defanghtml_ymailto="mailto: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 href="/mc/compose?to=mapserver-users@lists.osgeo.org" defanghtml_ymailto="mailto: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></TBODY></TABLE><BR><BR><FONT style="BACKGROUND-COLOR: #ffffff">
<P align=center><FONT style="BACKGROUND-COLOR: #ffffff">Click <A href="https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==">here</A> to report this email as spam.</FONT></P></FONT><br><br><table bgcolor=white style="color:black"><tr><td><br>------------------------------------------------------------------<br>
The contents of this email are confidential to AsureQuality. If you have received this communication in error please notify the sender immediately and delete the message and any attachments. The opinions expressed in this email are not necessarily those of AsureQuality. This message has been scanned for known viruses before delivery. AsureQuality supports the Unsolicited Electronic Messages Act 2007. If you do not wish to receive similar communications in future, please notify the sender of this message.<br>
------------------------------------------------------------------</td></tr></table><br><br>
<P align=center><FONT style="BACKGROUND-COLOR: #ffffff">This message has been scanned for malware by SurfControl plc. </FONT><A href="http://www.surfcontrol.com/"><FONT style="BACKGROUND-COLOR: #ffffff" color=#000000>www.surfcontrol.com</FONT></A></P>
</body></HTML>