ODBC provider incorrectly read SQL Server views

[pierrega]

Environment: SQL Server 2005 MapGuide Studio 2009

Main Table SQL:

CREATE TABLE [dbo].[Tab_XY](
      [ID] [smallint] NOT NULL,
      [X] [float] NOT NULL,
      [Y] [float] NOT NULL,
      [Test] [float] NOT NULL,
      [Z] [float] NOT NULL,
 CONSTRAINT [PK_Tab_XY] PRIMARY KEY CLUSTERED 
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

View SQL:

CREATE VIEW [dbo].[New_View]
AS
SELECT     ID AS vID, X AS vX, Y AS vY, Test AS vTest, Z AS vZ
FROM         dbo.Tab_XY

When I click "Show tables" in studio, what I get looks like:

[Table]     [Key Fields]
Tab_XY      ID
New_View    vID, vX, vY, vTest, vZ

So, I can't select my vX and vY columns as X and Y for geometry.

I found a way to workaround this problem by using the mapagent and editing the xml but this is a bit annoying for myself and I can imagine how it can be for my customer.

Note that I tested that if the X, Y and Z columns are nullable, the provider does not choose them as keys in New_View but my customer can’t (or don’t want to) change the structure of his tables.

Regards,
Pierre

[jbirch]

As far as I know, there is no way for the ODBC provider to programmatically determine the appropriate primary key for that view, so something (FDO, Studio) is making a “safe” guess of all of the columns.

I suspect that this is more of a MapGuide Studio enhancement request than a bug request in FDO, where Studio would have to be modified to allow the user to override the primary key for view-based feature sources.

[ksgeograf]

You can select the primary key of an ODBC connection using Maestro: ​http://trac.osgeo.org/mapguide/wiki/maestro

You can't select multiple columns as the primary key, but for this particular problem it may be a usable solution.

You cannot autodetect a primary key on a view, because the view may come from two tables, each with a primary key. Choosing either or both keys as the view's primary key won't work in all cases. So for views you are forced to choose the primary key manually, until some DB guys make metadata on views describing keys.

I agree with Jason, this is not really anything FDO can solve.

[gregboone]

This doesn't seem like an issue that FDO needs to reolve. This should be resolved at the MapGuide level or at the application level.