[fdo-users] FDO OSGeo for ODBC

[Pierre Gauthier]

Hello Gavin,

 

Thus there seem to be a problem with the provider.

 

Just to be sure and as far as I know, views can't have a primary key on SQL Server.  However, my source table have one.

 

My main table SQL is:

 

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]

 

My view SQL is:

 

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

 

The output in MapGuide Studio after a show table is:

 

[Table]                          [Key Fields]

Tab_XY                         ID

New_View                     vID, vX, vY, vTest, vZ

 

So, I assume that the provider can't detect that vID is a primary key.  

 

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

 

________________________________

From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Gavin Cramer
Sent: June 11, 2008 12:03
To: fdo-users at lists.osgeo.org
Subject: RE: [fdo-users] FDO OSGeo for ODBC

 

Yes, it does look for views.  Does the view lack a primary key?  The FDO ODBC Provider has a number of ways to  find identifier properties, with the main one being primary keys.  After that there are a number of fallbacks, with the last one being to grab any non-NULLable columns and treat them as identifier properties.  There has been discussion in the past of removing this last fallback, but it has not happened yet.  In the meantime, if the view can be given a primary key (anything expressly denoted as such, or anything that is autogenerated), ODBC Provider will use it instead of the columns that you want for ordinates.

 

Gavin

 

 

From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Pierre Gauthier
Sent: Tuesday, June 10, 2008 11:54 AM
To: fdo-users at lists.osgeo.org
Subject: [fdo-users] FDO OSGeo for ODBC

 

Hello,

 

I'm using the FDO ODBC provider with MapGuide Studio 2009 to connect to a SQLServer database and I wonder if views are supported when generating the list of tables.

 

The problem I have is that if my view is created from a table where my X and Y columns does not allow nulls, MapGuide Studio list my X and Y columns in the view as if they are key fields and I can`t select them as X and Y.

 

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.

 

Anybody else has seen this issue?

 

Regards,

Pierre

 

--

Pierre Gauthier

Analyste programmeur sénior

pierrega at consortech.com <mailto:pierrega at consortech.com> 

Solutions Consortech inc.

Tél: 450-676-1555 x217

Fax: 450-676-8851

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-users/attachments/20080611/044255de/attachment-0001.html