Opened 11 years ago

Closed 11 years ago

#349 closed defect (wontfix)

ODBC provider incorrectly read SQL Server views

Reported by: pierrega Owned by: gregboone
Priority: blocker Milestone:
Component: ODBC Provider Version: 3.3.0
Severity: 3 Keywords: odbc sql server view
Cc: External ID:

Description

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

Change History (4)

comment:1 Changed 11 years ago by pierrega

Version: 3.2.03.3.0

comment:2 Changed 11 years ago by 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.

comment:3 Changed 11 years ago by 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.

comment:4 Changed 11 years ago by gregboone

Resolution: wontfix
Status: newclosed

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.

Note: See TracTickets for help on using tickets.