OGR Virtual problem with Where clause

[James Perrins]

Apologies - I had intended to reply to the list - my mistake.

 

I was trying to avoid using an OVF file as I'm using CGI and the next
step was to look at dynamically changing the where clause from the URL

 

Using double quotes round the whole xml string does make it work - I
read someone else on the list saying that this didn't work  - so hadn't
tried it

 

Thanks very much

James

 

  _____  

From: Pagurek,Debbie [NCR] [mailto:Debbie.Pagurek at EC.GC.CA] 
Sent: 30 March 2006 17:24
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] OGR Virtual problem with Where clause

 

Please reply to the list so that others can comment too.

One easy way to solve this is to put your xml in an OVF file that
resides outside of your mapfile, and then just refer to that file in
your mapfile.

 

e.g. in your mapfile LAYER section:

LAYER
  NAME "air1_2005"
  DEBUG ON
  DATA "air1_layer" # Must be the same as <OGRVRTLayer name=""> in ovf
file- case sensitive
  CONNECTIONTYPE OGR #Must be OGR for the ODBC connection 
  CONNECTION /web_map_services/data/cesi/air1_map_2005_en.ovf

etc.

 

Another way to solve it is to put double quotes around your XML in the
mapfile so then your single quotes should work?

 

refer to
http://mapserver.gis.umn.edu/docs/reference/vector_data/VirtualSpatialDa
ta/?searchterm=ovf

for more information and examples.

 

D. Pagurek

	-----Original Message-----
	From: James Perrins [mailto:JamesP at esdm.co.uk] 
	Sent: Thursday, March 30, 2006 10:46 AM
	To: Pagurek,Debbie [NCR]
	Subject: RE: [UMN_MAPSERVER-USERS] OGR Virtual problem with
Where clause

	Thanks - but single quotes don't work because the whole xml is
in single quotes in the map file

	Unless there is some way to escape the quotes which I've failed
to manage

	 

	e.g.

	 

	  '<OGRVRTDataSource>

	       <OGRVRTLayer name="TestMemo">

	        <SrcDataSource>ODBC:WKT,TestPolygon</SrcDataSource> 

	        <SrcSQL>SELECT * FROM TestMemo where
Name="Cosh"</SrcSQL>

	        <SrcLayer>TestMemo</SrcLayer> 

	        <GeometryType>wkbPolygon</GeometryType> 

	        <GeometryField encoding="WKT" field="GEOMETRY"/>

	      </OGRVRTLayer>

	  </OGRVRTDataSource>'

	 

	P.S. I know the above example is actually for Access not SQL -
but I'm seeing the same behaviour in both

	 

	Thanks

	James

	 

	
  _____  


	From: Pagurek,Debbie [NCR] [mailto:Debbie.Pagurek at EC.GC.CA] 
	Sent: 30 March 2006 16:18
	To: MAPSERVER-USERS at LISTS.UMN.EDU
	Subject: Re: [UMN_MAPSERVER-USERS] OGR Virtual problem with
Where clause

	 

	Try using single quotes instead of double quotes:

	<SrcSQL>SELECT * FROM TestPolygon WHERE Name='Cosh'</SrcSQL>

	 

	D. Pagurek

	 

		-----Original Message-----
		From: UMN MapServer Users List
[mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On Behalf Of James Perrins
		Sent: Thursday, March 30, 2006 8:57 AM
		To: MAPSERVER-USERS at LISTS.UMN.EDU
		Subject: [UMN_MAPSERVER-USERS] OGR Virtual problem with
Where clause

		Hi,

		 

		I'm just experimenting with this - (just discovered it
:-)) which looks great.  

		I'm calling from mapserver with the XML connection
statement in the map file (IIS5 + Mapserver 4.6.1)

		 

		I've been playing with MDB and SQL Server - both of
which I can get to plot points and polygons (with WKT)

		In the examples below - KeyField is an Integer in the
database and Name is a text / nvarchar (50)

		 

		Plotting the whole layer works fine

		<SrcSQL>SELECT * FROM TestPolygon</SrcSQL>

		 

		Using a filter on an integer field works fine 

		<SrcSQL>SELECT * FROM TestPolygon WHERE
KeyField=1</SrcSQL>

		 

		But I just can't get it to filter using any other sort
of where clause - specifically text string e.g.

		<SrcSQL>SELECT * FROM TestPolygon WHERE
Name="Cosh"</SrcSQL>

		 

		I've tried a variety of syntaxes but no success -
everything I try returns something like:

		SQL statement failed, or returned no layer result:

		SELECT GEOMETRY FROM TestPolygon WHERE Name="Cosh"

		 

		Can anyone help me out ?

		Have I got the syntax wrong - if so is there any doc
that details this ?

		Looking at others questions they seem to have it working
- do I need to be careful of build versions?

		 

		Many Thanks

		James

		 

		 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060330/6da6abf0/attachment.htm>