FW: [mapguide-users] I can't see views when I use the FDO/ODBC.

Thu Jul 6 12:24:18 EDT 2006

Please see below for a summary of the issues regarding the exposure of
views through the ODBC Provider.

Greg

-----Original Message-----
From: Gavin Cramer 
Sent: Thursday, July 06, 2006 11:26 AM
To: Greg Boone
Cc: Robert Fortin; Brent Robinson
Subject: RE: [mapguide-users] I can't see views when I use the FDO/ODBC.

In Summary...

A. Skipping views is by design in the Oracle, SQL Server, MySQL and ODBC
Providers.  The main reasons are:

    1. Some datastores (e.g. Oracle) have a large number of views
       in the user's schema by default.

    2. Some tools are not advanced enough to differentiate when 
       needed.  For example, BulkCopy would write out a view's
       results as a new table in the destination datastore.

B. View names can be used in lieu of table names in schema overrides.
An example (from our unit tests) of an override for SNDG is below.  It
works regardless of whether SNDG is a table or a view.

C. Supporting views by default has been requested by several customers.
It is under consideration.

Gavin

---------- Example configuration document with schema overrides
------------

<?xml version="1.0" encoding="UTF-8"?>
<!-- Sample configuration file for ODBC DataStore that contains
Hydrographic soundings -->
<fdo:DataStore 
 xmlns:fds="http://fdo.osgeo.org/schemas/fds"
 xmlns:fdo="http://fdo.osgeo.org/schemas"
 xmlns:gml="http://www.opengis.net/gml"
 xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:xlink="http://www.w3.org/1999/xlink"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xsi:schemaLocation="http://fdo.osgeo.org/schemas/fds
 FdoDocumentSample.xsd"
>
	<!-- spatial context for the Hydrographic Soundings -->
	<gml:DerivedCRS gml:id="survey1">
		<!-- remarks contain spatial context description -->
		<gml:remarks>Spatial context for Survey Area
1</gml:remarks>
		<!-- srsName contains spatial context name -->
		<gml:srsName>survey1</gml:srsName>
		<!-- ValidArea defines the spatial context extents. -->
		<gml:validArea>
			<!-- GML allows polygon areas but only
rectangular areas valid for FDO 
			     Spatial Context extents -->
			<gml:boundingBox>
				<gml:pos>200000 1000000</gml:pos>
				<gml:pos>300000 1100000</gml:pos>
			</gml:boundingBox>
		</gml:validArea>
		<!-- BaseCRS defines the coordinate system name, which
is "utm_zone1" in this case. 
		     The CRS is assumed to be a well-known CRS and this
file does not define it -->
		<gml:baseCRS
xlink:href="http://www.mysurvey.com/coord_ref_systems#utm_zone1"/>
		<!-- DefinedByConversion defines the coordinate
conversion from the base CRS to this CRS.
		     This would always be an identity transformation
since Spatial Contexts don't define any
		-->
		<gml:definedByConversion
xlink:href="http://www.mysurvey.com/coord_conversions#identity"/>
		<gml:derivedCRSType
codeSpace="http://fdo.osgeo.org/crs_types">geographic</gml:derivedCRSTyp
e>
		<!-- UsesCS defines the Coordinate System for this
Coordinate Reference system. For FDO, this 
		  would be the same as the Coordinate System for the
BaseCRS. 
		-->
		<gml:usesCS
xlink:href="http://www.mysurvey.com/coord_systems#default"/>
	</gml:DerivedCRS>

	<!-- Hydrographic Feature Schema -->
	<!-- was: http://www.oceanographic.org -->
	<xs:schema 
	 xmlns:xs="http://www.w3.org/2001/XMLSchema" 
	 targetNamespace="http://fdo.osgeo.org/schemas/feature/hydro" 
	 xmlns:fdo="http://fdo.osgeo.org/schemas" 
	 xmlns:hydro="http://fdo.osgeo.org/schemas/feature/hydro" 
	 elementFormDefault="qualified" 
	 attributeFormDefault="qualified"
	>
		<!-- Only one class (Sounding). The following element
specifies the identityProperties -->
		<xs:element name="Sounding" type="hydro:SoundingType"
abstract="false"
		 substitutionGroup="gml:_Feature"
		>
			<xs:key name="SoundingKey"> 
  				<xs:selector xpath=".//Sounding"/>
				<xs:field xpath="Id"/>
			</xs:key>
 		</xs:element>
		<!-- Sounding class definition -->
		<xs:complexType name="SoundingType" abstract="false" >
			<xs:complexContent>
				<!-- following extension defines
Sounding as a Feature Class -->
				<xs:extension
base="gml:AbstractFeatureType">
					<xs:sequence>
						<xs:element name="Id"
type="fdo:int64" />
						<xs:element
name="SoundingDate" type="xs:dateTime" />
						<xs:element
name="Geometry" type="gml:AbstractGeometryType" fdo:hasElevation="true"
fdo:geometricTypes="Point" associatedSCName="survey1" />
		        	</xs:sequence>
      			</xs:extension>
   			</xs:complexContent>
		</xs:complexType>
	</xs:schema>

	<!-- Schema Overrides for Hydrographic Feature Schema -->
	<SchemaMapping name="hydro" provider="OSGeo.ODBC.3.1"
physicalSchemaName="" 
	 xmlns="http://fdoodbc.osgeo.org/schemas"
	>
		<!-- Schema Overrides for Sounding Feature Class -->
		<complexType name="SoundingType">
			<!-- Soundings kept in SNDG table and geometry
columns are overridden -->
			<Table name="SNDG"/>
			<element name="Id">
				<!-- Identifier kept in Id column -->
				<Column name="FEATID1"/>
			</element>
			<element name="SoundingDate">
				<!-- Date kept in SNDG_DATE column -->
				<Column name="SNDG_DATE"/>
			</element>
                  <!-- Geometry ordinate columns are overridden -->
			<element name="Geometry" xColumnName="POINT_X"
yColumnName="POINT_Y" zColumnName="POINT_Z" />
		</complexType>
	</SchemaMapping>
</fdo:DataStore>

-----Original Message-----
From: Gavin Cramer 
Sent: Thursday, July 06, 2006 10:22 AM
To: Greg Boone
Subject: RE: [mapguide-users] I can't see views when I use the FDO/ODBC.

I have done a rudimentary test.  Views are actually detected at the
lowest level in our code ("OdbcDriver"), but skipped in the Schema
Manager, in FdoSmPhRdClassReader::ClassifyObject().

We skip views by default, due to some databases having lots of views by
default (e.g. Oracle USER_*).  However, they can be named individually
in physical schema overrides and used as expected there.  

Gavin

-----Original Message-----
From: Gavin Cramer 
Sent: Wednesday, July 05, 2006 8:18 PM
To: Greg Boone
Subject: RE: [mapguide-users] I can't see views when I use the FDO/ODBC.

The ODBC Provider does request both tables and views.  However, we have
not tested views with Access.  The symptom may be a shortcoming of the
MS Access driver itself, as we have experienced many times before.
According to MS documentation for the MS Access ODBC driver, views are
supported.

I recall the issue coming up in the past, but I do not recall the
details.  Unfortunately, the keywords "access" and "view" aren't very
good for narrowing down a search.

I'll try a test tomorrow morning, using ODBC Provider, and possible
another ODBC client e.g. Excel.  If ODBC Provider cannot see views, but
another client can, there must be a way to make it work.  If neither
client can see views, it's a bug in Microsoft's driver.

Gavin

-----Original Message-----
From: Andy Morsell [mailto:amorsell at spatialgis.com] 
Sent: Wednesday, July 05, 2006 5:53 PM
To: users at mapguide.osgeo.org
Subject: RE: [mapguide-users] I can't see views when I use the FDO/ODBC.

A view is a query definition within the Access database itself.  There
are
several types of views, but in this case, it would be a select view
where
either a single table is filtered or two or more tables are joined on a
common key.  Views are very common.  They are called Queries in SQL
Server
and Views in Oracle.

Andy 

-----Original Message-----
From: Andrew DeMerchant [mailto:andrew.demerchant at gemtec.ca] 
Sent: Wednesday, July 05, 2006 2:47 PM
To: users at mapguide.osgeo.org
Subject: Re: [mapguide-users] I can't see views when I use the FDO/ODBC.

What exactly are you trying to look for other than a table? What's a
'view'?
Do you mean a 'form'? ODBC connections will only let you access tables.
No
forms, no reports, etc....

Andrew

Luiz Marcio Viana wrote:
> Hi,
>
> I'm trying to see views in MapGuide Studio, using the FDO/ODBC, but I 
> see only the tables.
> I'm using the MS Access database and the last versions of MapGuide 
> Open Source and MapGuide Studio Preview.
>
> Are there any problem with FDO/ODBC to publish views?
> I know FDO/ODBC works only with MS Access. Is It right?
>
> Thanks,
> Luiz Marcio
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe at mapguide.osgeo.org
> For additional commands, e-mail: users-help at mapguide.osgeo.org
>
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe at mapguide.osgeo.org
For additional commands, e-mail: users-help at mapguide.osgeo.org

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe at mapguide.osgeo.org
For additional commands, e-mail: users-help at mapguide.osgeo.org