SQL 2008. View with JOINS. Not able to select features on the layer.

[miansi]

Hello,

Please advise what, where and how should I poke

Here is the issue:

I have SQL 2008 table with geospatial data. I created Dataconnection, Layer, Map and am able to see data from view, which selecting everything from my geospatial table:

TABLE:
/****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009 16:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
        [GeometryItem] [geometry] NULL,
        [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
        [AssetID] [varchar](10) NULL,
        [InstallYea] [int] NULL,
        [LifeStatus] [varchar](50) NULL,
        [LocDesc] [varchar](50) NULL,
        [MaterialID] [varchar](50) NULL,
        [SpatialKey] [varchar](10) NULL,
        [SpatialSta] [varchar](50) NULL,
        [AssetType] [varchar](50) NULL,
        [Backfill] [varchar](50) NULL,
        [BedMat] [varchar](50) NULL,
        [Capacity] [decimal](38, 18) NULL,
        [Diameter] [int] NULL,
        [DoStreamIn] [decimal](38, 18) NULL,
        [HasLining] [bit] NULL,
        [Height] [int] NULL,
        [Length] [decimal](38, 18) NULL,
        [LiningYear] [int] NULL,
        [LiningLen] [decimal](38, 18) NULL,
        [PipeClass] [varchar](50) NULL,
        [PipeFunc] [varchar](50) NULL,
        [PipeShape] [varchar](50) NULL,
        [UpStreamIn] [decimal](38, 18) NULL,
        [Width] [varchar](50) NULL,
 CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
        [GeometryIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_srid]
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_type] CHECK  (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_type]
GO




VIEW:
SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus, LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill, BedMat, Capacity, Diameter,
                      DoStreamIn, HasLining, Height, Length, LiningYear, LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM         dbo.Sanit_GM AS new

I am able to see layer pointing to this view. AND I am able to select features on the map.


If I modify View to include any JOIN I will be able to see layer pointing to view, BUT would NOT be able to select features on the map:


VIEW:
SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID, new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID, new.SpatialKey, new.SpatialSta, new.AssetType,
                      new.Backfill, new.BedMat, new.Capacity, new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length, new.LiningYear, new.LiningLen, new.PipeClass,
                      new.PipeFunc, new.PipeShape, new.UpStreamIn, new.Width
FROM         dbo.Sanit_GM AS new INNER JOIN
                      dbo.Asset AS a ON a.DisplayId = new.AssetID


Now, MYSTERY 

If I will not refresh Dataconnection after adding join to the view - I am still able to select features on the map.

Since Map, Layer and FeatureSource XML looks exactly the same in both cases I wonder what is the difference? What is changing when FDO detects JOIN in view?
Can I override this change? Can I force FDO to ignore it? Is it FDO, which prevents selection or something else?

Is this right forum to post? Is this a bug?


Thank you,

[Jackie Ng]

Maybe try looking at this data source through FDO Toolbox (http://fdotoolbox.googlecode.com), and take a look at the feature classes, and see if the view (with the join) is structurally different from the view (without the join).

For example, I could foresee some problem if the view (without the join) has an Identity Property and the view (with the join) doesn't.

- Jackie

miansi wrote:

Hello,

Please advise what, where and how should I poke

Here is the issue:

I have SQL 2008 table with geospatial data. I created Dataconnection, Layer, Map and am able to see data from view, which selecting everything from my geospatial table:

TABLE:
/****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009 16:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
        [GeometryItem] [geometry] NULL,
        [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
        [AssetID] [varchar](10) NULL,
        [InstallYea] [int] NULL,
        [LifeStatus] [varchar](50) NULL,
        [LocDesc] [varchar](50) NULL,
        [MaterialID] [varchar](50) NULL,
        [SpatialKey] [varchar](10) NULL,
        [SpatialSta] [varchar](50) NULL,
        [AssetType] [varchar](50) NULL,
        [Backfill] [varchar](50) NULL,
        [BedMat] [varchar](50) NULL,
        [Capacity] [decimal](38, 18) NULL,
        [Diameter] [int] NULL,
        [DoStreamIn] [decimal](38, 18) NULL,
        [HasLining] [bit] NULL,
        [Height] [int] NULL,
        [Length] [decimal](38, 18) NULL,
        [LiningYear] [int] NULL,
        [LiningLen] [decimal](38, 18) NULL,
        [PipeClass] [varchar](50) NULL,
        [PipeFunc] [varchar](50) NULL,
        [PipeShape] [varchar](50) NULL,
        [UpStreamIn] [decimal](38, 18) NULL,
        [Width] [varchar](50) NULL,
 CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
        [GeometryIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_srid]
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_type] CHECK  (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_type]
GO




VIEW:
SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus, LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill, BedMat, Capacity, Diameter,
                      DoStreamIn, HasLining, Height, Length, LiningYear, LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM         dbo.Sanit_GM AS new

I am able to see layer pointing to this view. AND I am able to select features on the map.


If I modify View to include any JOIN I will be able to see layer pointing to view, BUT would NOT be able to select features on the map:


VIEW:
SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID, new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID, new.SpatialKey, new.SpatialSta, new.AssetType,
                      new.Backfill, new.BedMat, new.Capacity, new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length, new.LiningYear, new.LiningLen, new.PipeClass,
                      new.PipeFunc, new.PipeShape, new.UpStreamIn, new.Width
FROM         dbo.Sanit_GM AS new INNER JOIN
                      dbo.Asset AS a ON a.DisplayId = new.AssetID


Now, MYSTERY 

If I will not refresh Dataconnection after adding join to the view - I am still able to select features on the map.

Since Map, Layer and FeatureSource XML looks exactly the same in both cases I wonder what is the difference? What is changing when FDO detects JOIN in view?
Can I override this change? Can I force FDO to ignore it? Is it FDO, which prevents selection or something else?

Is this right forum to post? Is this a bug?


Thank you,

[miansi]

Some javascript/style in this post has been disabled (why?)

Thank you Jackie.

 

Can you help me with connection string? Seems like FDO toolbox missing simple example of connection string for SQL 2008.

 

 

Thank you,

 

From: Jackie Ng (via Nabble) [mailto:[hidden email]]
Sent: Thursday, February 19, 2009 5:15 PM
To: Sinelnikov, Andrei
Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select features on the layer.

 

Maybe try looking at this data source through FDO Toolbox (http://fdotoolbox.googlecode.com), and take a look at the feature classes, and see if the view (with the join) is structurally different from the view (without the join).

For example, I could foresee some problem if the view (without the join) has an Identity Property and the view (with the join) doesn't.

- Jackie

miansi wrote:

Hello,

Please advise what, where and how should I poke

Here is the issue:

I have SQL 2008 table with geospatial data. I created Dataconnection, Layer, Map and am able to see data from view, which selecting everything from my geospatial table:

TABLE:
/****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009 16:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
        [GeometryItem] [geometry] NULL,
        [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
        [AssetID] [varchar](10) NULL,
        [InstallYea] [int] NULL,
        [LifeStatus] [varchar](50) NULL,
        [LocDesc] [varchar](50) NULL,
        [MaterialID] [varchar](50) NULL,
        [SpatialKey] [varchar](10) NULL,
        [SpatialSta] [varchar](50) NULL,
        [AssetType] [varchar](50) NULL,
        [Backfill] [varchar](50) NULL,
        [BedMat] [varchar](50) NULL,
        [Capacity] [decimal](38, 18) NULL,
        [Diameter] [int] NULL,
        [DoStreamIn] [decimal](38, 18) NULL,
        [HasLining] [bit] NULL,
        [Height] [int] NULL,
        [Length] [decimal](38, 18) NULL,
        [LiningYear] [int] NULL,
        [LiningLen] [decimal](38, 18) NULL,
        [PipeClass] [varchar](50) NULL,
        [PipeFunc] [varchar](50) NULL,
        [PipeShape] [varchar](50) NULL,
        [UpStreamIn] [decimal](38, 18) NULL,
        [Width] [varchar](50) NULL,
 CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
        [GeometryIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_srid]
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT [Sanit_GM_GeometryItem_type] CHECK  (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT [Sanit_GM_GeometryItem_type]
GO




VIEW:
SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus, LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill, BedMat, Capacity, Diameter,
                      DoStreamIn, HasLining, Height, Length, LiningYear, LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM         dbo.Sanit_GM AS new

I am able to see layer pointing to this view. AND I am able to select features on the map.


If I modify View to include any JOIN I will be able to see layer pointing to view, BUT would NOT be able to select features on the map:


VIEW:
SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID, new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID, new.SpatialKey, new.SpatialSta, new.AssetType,
                      new.Backfill, new.BedMat, new.Capacity, new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length, new.LiningYear, new.LiningLen, new.PipeClass,
                      new.PipeFunc, new.PipeShape, new.UpStreamIn, new.Width
FROM         dbo.Sanit_GM AS new INNER JOIN
                      dbo.Asset AS a ON a.DisplayId = new.AssetID


Now, MYSTERY 

If I will not refresh Dataconnection after adding join to the view - I am still able to select features on the map.

Since Map, Layer and FeatureSource XML looks exactly the same in both cases I wonder what is the difference? What is changing when FDO detects JOIN in view?
Can I override this change? Can I force FDO to ignore it? Is it FDO, which prevents selection or something else?

Is this right forum to post? Is this a bug?


Thank you,

 

---

This email is a reply to your post @ http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-features-on-the-layer.-tp2356197p2356328.html
You can reply by email or by visting the link above.

 

----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended only for the party to whom it is addressed and may contain information that is confidential or privileged. Sierra Systems Group Inc. and its affiliates accept no responsibility for any loss or damage suffered by any person resulting from any unauthorized use of or reliance upon this Email. If you are not the intended recipient, you are hereby notified that any dissemination, copying or other use of this Email is prohibited. Please notify us of the error in communication by return email and destroy all copies of this Email. Thank you.

[Jackie Ng]

A Feature Source describes a FDO connection, so get the XML content for that feature source, and look at each <Name> and <Value> element between the <Parameter> elements.

When you connect via the generic dialog, select the SQL Server Spatial provider. The connection property grid should be filled with the required parameters (which should match your <Name> elements). Fill in the blanks using the matching <Value> elements in the feature source XML.

You shouldn't need to use the ConnectionString property, but if you do I think you have to surround the value with quotes (like the ODBC provider)

- Jackie

miansi wrote:

Thank you Jackie.

 

Can you help me with connection string? Seems like FDO toolbox missing
simple example of connection string for SQL 2008.

 

 

Thank you,

 

From: Jackie Ng (via Nabble)
[mailto:[e-mail]]
Sent: Thursday, February 19, 2009 5:15 PM
To: Sinelnikov, Andrei
Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.

 

Maybe try looking at this data source through FDO Toolbox
(http://fdotoolbox.googlecode.com), and take a look at the feature
classes, and see if the view (with the join) is structurally different
from the view (without the join).

For example, I could foresee some problem if the view (without the join)
has an Identity Property and the view (with the join) doesn't.

- Jackie

miansi wrote:

Hello,

Please advise what, where and how should I poke
<http://n2.nabble.com/images/smiley/anim_confused.gif>

Here is the issue:

I have SQL 2008 table with geospatial data. I created Dataconnection,
Layer, Map and am able to see data from view, which selecting everything
from my geospatial table:

TABLE:
/****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009
16:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
        [GeometryItem] [geometry] NULL,
        [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
        [AssetID] [varchar](10) NULL,
        [InstallYea] [int] NULL,
        [LifeStatus] [varchar](50) NULL,
        [LocDesc] [varchar](50) NULL,
        [MaterialID] [varchar](50) NULL,
        [SpatialKey] [varchar](10) NULL,
        [SpatialSta] [varchar](50) NULL,
        [AssetType] [varchar](50) NULL,
        [Backfill] [varchar](50) NULL,
        [BedMat] [varchar](50) NULL,
        [Capacity] [decimal](38, 18) NULL,
        [Diameter] [int] NULL,
        [DoStreamIn] [decimal](38, 18) NULL,
        [HasLining] [bit] NULL,
        [Height] [int] NULL,
        [Length] [decimal](38, 18) NULL,
        [LiningYear] [int] NULL,
        [LiningLen] [decimal](38, 18) NULL,
        [PipeClass] [varchar](50) NULL,
        [PipeFunc] [varchar](50) NULL,
        [PipeShape] [varchar](50) NULL,
        [UpStreamIn] [decimal](38, 18) NULL,
        [Width] [varchar](50) NULL,
 CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
        [GeometryIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
[Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR
[GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
[Sanit_GM_GeometryItem_srid]
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
[Sanit_GM_GeometryItem_type] CHECK
(([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem]
IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
[Sanit_GM_GeometryItem_type]
GO




VIEW:
SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus,
LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill,
BedMat, Capacity, Diameter,
                      DoStreamIn, HasLining, Height, Length, LiningYear,
LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM         dbo.Sanit_GM AS new

I am able to see layer pointing to this view. AND I am able to select
features on the map.
 <http://n2.nabble.com/file/n2356197/1.Selecting.png>
<http://n2.nabble.com/file/n2356197/2.Selected.png>

If I modify View to include any JOIN I will be able to see layer
pointing to view, BUT would NOT be able to select features on the map:
 <http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png>
<http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png>

VIEW:
SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID,
new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID,
new.SpatialKey, new.SpatialSta, new.AssetType,
                      new.Backfill, new.BedMat, new.Capacity,
new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length,
new.LiningYear, new.LiningLen, new.PipeClass,
                      new.PipeFunc, new.PipeShape, new.UpStreamIn,
new.Width
FROM         dbo.Sanit_GM AS new INNER JOIN
                      dbo.Asset AS a ON a.DisplayId = new.AssetID


Now, MYSTERY  <http://n2.nabble.com/images/smiley/anim_crazy.gif>

If I will not refresh Dataconnection after adding join to the view - I
am still able to select features on the map.

Since Map, Layer and FeatureSource XML looks exactly the same in both
cases I wonder what is the difference? What is changing when FDO detects
JOIN in view?
Can I override this change? Can I force FDO to ignore it? Is it FDO,
which prevents selection or something else?

Is this right forum to post? Is this a bug?


Thank you,

 

________________________________

This email is a reply to your post @
http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
res-on-the-layer.-tp2356197p2356328.html
You can reply by email or by visting the link above.

 



----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended only for the party to whom it is addressed and may contain information that is confidential or privileged.  Sierra Systems Group Inc. and its affiliates accept no responsibility for any loss or damage suffered by any person resulting from any unauthorized use of or reliance upon this Email.  If you are not the intended recipient, you are hereby notified that any dissemination, copying or other use of this Email is prohibited.  Please notify us of the error in communication by return email and destroy all copies of this Email.  Thank you.

[miansi]

Am I missing something?

Jackie Ng wrote:

A Feature Source describes a FDO connection, so get the XML content for that feature source, and look at each <Name> and <Value> element between the <Parameter> elements.

When you connect via the generic dialog, select the SQL Server Spatial provider. The connection property grid should be filled with the required parameters (which should match your <Name> elements). Fill in the blanks using the matching <Value> elements in the feature source XML.

You shouldn't need to use the ConnectionString property, but if you do I think you have to surround the value with quotes (like the ODBC provider)

- Jackie

miansi wrote:

Thank you Jackie.

 

Can you help me with connection string? Seems like FDO toolbox missing
simple example of connection string for SQL 2008.

 

 

Thank you,

 

From: Jackie Ng (via Nabble)
[mailto:[e-mail]]
Sent: Thursday, February 19, 2009 5:15 PM
To: Sinelnikov, Andrei
Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.

 

Maybe try looking at this data source through FDO Toolbox
(http://fdotoolbox.googlecode.com), and take a look at the feature
classes, and see if the view (with the join) is structurally different
from the view (without the join).

For example, I could foresee some problem if the view (without the join)
has an Identity Property and the view (with the join) doesn't.

- Jackie

miansi wrote:

Hello,

Please advise what, where and how should I poke
<http://n2.nabble.com/images/smiley/anim_confused.gif>

Here is the issue:

I have SQL 2008 table with geospatial data. I created Dataconnection,
Layer, Map and am able to see data from view, which selecting everything
from my geospatial table:

TABLE:
/****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009
16:14:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Sanit_GM](
        [GeometryItem] [geometry] NULL,
        [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
        [AssetID] [varchar](10) NULL,
        [InstallYea] [int] NULL,
        [LifeStatus] [varchar](50) NULL,
        [LocDesc] [varchar](50) NULL,
        [MaterialID] [varchar](50) NULL,
        [SpatialKey] [varchar](10) NULL,
        [SpatialSta] [varchar](50) NULL,
        [AssetType] [varchar](50) NULL,
        [Backfill] [varchar](50) NULL,
        [BedMat] [varchar](50) NULL,
        [Capacity] [decimal](38, 18) NULL,
        [Diameter] [int] NULL,
        [DoStreamIn] [decimal](38, 18) NULL,
        [HasLining] [bit] NULL,
        [Height] [int] NULL,
        [Length] [decimal](38, 18) NULL,
        [LiningYear] [int] NULL,
        [LiningLen] [decimal](38, 18) NULL,
        [PipeClass] [varchar](50) NULL,
        [PipeFunc] [varchar](50) NULL,
        [PipeShape] [varchar](50) NULL,
        [UpStreamIn] [decimal](38, 18) NULL,
        [Width] [varchar](50) NULL,
 CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
(
        [GeometryIndex] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
[Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR
[GeometryItem] IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
[Sanit_GM_GeometryItem_srid]
GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
[Sanit_GM_GeometryItem_type] CHECK
(([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem]
IS NULL))
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
OBJECT_ID(N'[dbo].[Sanit_GM]'))
ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
[Sanit_GM_GeometryItem_type]
GO




VIEW:
SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus,
LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill,
BedMat, Capacity, Diameter,
                      DoStreamIn, HasLining, Height, Length, LiningYear,
LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
FROM         dbo.Sanit_GM AS new

I am able to see layer pointing to this view. AND I am able to select
features on the map.
 <http://n2.nabble.com/file/n2356197/1.Selecting.png>
<http://n2.nabble.com/file/n2356197/2.Selected.png>

If I modify View to include any JOIN I will be able to see layer
pointing to view, BUT would NOT be able to select features on the map:
 <http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png>
<http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png>

VIEW:
SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID,
new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID,
new.SpatialKey, new.SpatialSta, new.AssetType,
                      new.Backfill, new.BedMat, new.Capacity,
new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length,
new.LiningYear, new.LiningLen, new.PipeClass,
                      new.PipeFunc, new.PipeShape, new.UpStreamIn,
new.Width
FROM         dbo.Sanit_GM AS new INNER JOIN
                      dbo.Asset AS a ON a.DisplayId = new.AssetID


Now, MYSTERY  <http://n2.nabble.com/images/smiley/anim_crazy.gif>

If I will not refresh Dataconnection after adding join to the view - I
am still able to select features on the map.

Since Map, Layer and FeatureSource XML looks exactly the same in both
cases I wonder what is the difference? What is changing when FDO detects
JOIN in view?
Can I override this change? Can I force FDO to ignore it? Is it FDO,
which prevents selection or something else?

Is this right forum to post? Is this a bug?


Thank you,

 

________________________________

This email is a reply to your post @
http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
res-on-the-layer.-tp2356197p2356328.html
You can reply by email or by visting the link above.

 



----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended only for the party to whom it is addressed and may contain information that is confidential or privileged.  Sierra Systems Group Inc. and its affiliates accept no responsibility for any loss or damage suffered by any person resulting from any unauthorized use of or reliance upon this Email.  If you are not the intended recipient, you are hereby notified that any dissemination, copying or other use of this Email is prohibited.  Please notify us of the error in communication by return email and destroy all copies of this Email.  Thank you.

[Brent Robinson]

Hi,

When the SQLServerSpatial provider generates a class for a view, it derives the identity properties from the primary key (or unique index if primary key is not present) of the base table. However, this is only done if the view is only based on one table. If the view joins 2 tables, it ends up without identity. It's features can be drawn in MapGuide but are not selectable. I looked at a couple of options but some limitations prevent them from being used until modifications are done to the provider.

One way to specify the identity properties is through a config document, which can be attached to the feature source. Unfortunately, the SQLServerSpatial advertises that it does not support config documents even though it does. I've logged a trac ticket (http://trac.osgeo.org/fdo/ticket/469) for this defect.

SQL Server allows clustered indexes to be set on deterministic views but the provider does not currently look at indexes on views. A ticket has also been logged for this issue (http://trac.osgeo.org/fdo/ticket/470). In another e-mail, you mentioned trying the deterministic view (with schemabinding). Did you encounter a problem setting up the view, or did you get it created successfully but found that the features were still not selectable?  

Unfortunately, none of these lead to an immediate solution for FDO 3.4. I'll continue to investigate to see if there is another alternative.

Brent.
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Jackie Ng
Sent: Thursday, February 19, 2009 10:30 PM
To: [hidden email]
Subject: RE: [fdo-users] SQL 2008. View with JOINS. Not able to select features on the layer.


A Feature Source describes a FDO connection, so get the XML content for that
feature source, and look at each <Name> and <Value> element between the
<Parameter> elements.

When you connect via the generic dialog, select the SQL Server Spatial
provider. The connection property grid should be filled with the required
parameters (which should match your <Name> elements). Fill in the blanks
using the matching <Value> elements in the feature source XML.

You shouldn't need to use the ConnectionString property, but if you do I
think you have to surround the value with quotes (like the ODBC provider)

- Jackie


miansi wrote:

>
> Thank you Jackie.
>
>
>
> Can you help me with connection string? Seems like FDO toolbox missing
> simple example of connection string for SQL 2008.
>
>
>
>
>
> Thank you,
>
>
>
> From: Jackie Ng (via Nabble)
> [mailto:[hidden email]]
> Sent: Thursday, February 19, 2009 5:15 PM
> To: Sinelnikov, Andrei
> Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select
> features on the layer.
>
>
>
> Maybe try looking at this data source through FDO Toolbox
> (http://fdotoolbox.googlecode.com), and take a look at the feature
> classes, and see if the view (with the join) is structurally different
> from the view (without the join).
>
> For example, I could foresee some problem if the view (without the join)
> has an Identity Property and the view (with the join) doesn't.
>
> - Jackie
>
> miansi wrote:
>
> Hello,
>
> Please advise what, where and how should I poke
> <http://n2.nabble.com/images/smiley/anim_confused.gif>
>
> Here is the issue:
>
> I have SQL 2008 table with geospatial data. I created Dataconnection,
> Layer, Map and am able to see data from view, which selecting everything
> from my geospatial table:
>
> TABLE:
> /****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009
> 16:14:56 ******/
> SET ANSI_NULLS ON
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
>
> SET ANSI_PADDING ON
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[Sanit_GM](
>         [GeometryItem] [geometry] NULL,
>         [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
>         [AssetID] [varchar](10) NULL,
>         [InstallYea] [int] NULL,
>         [LifeStatus] [varchar](50) NULL,
>         [LocDesc] [varchar](50) NULL,
>         [MaterialID] [varchar](50) NULL,
>         [SpatialKey] [varchar](10) NULL,
>         [SpatialSta] [varchar](50) NULL,
>         [AssetType] [varchar](50) NULL,
>         [Backfill] [varchar](50) NULL,
>         [BedMat] [varchar](50) NULL,
>         [Capacity] [decimal](38, 18) NULL,
>         [Diameter] [int] NULL,
>         [DoStreamIn] [decimal](38, 18) NULL,
>         [HasLining] [bit] NULL,
>         [Height] [int] NULL,
>         [Length] [decimal](38, 18) NULL,
>         [LiningYear] [int] NULL,
>         [LiningLen] [decimal](38, 18) NULL,
>         [PipeClass] [varchar](50) NULL,
>         [PipeFunc] [varchar](50) NULL,
>         [PipeShape] [varchar](50) NULL,
>         [UpStreamIn] [decimal](38, 18) NULL,
>         [Width] [varchar](50) NULL,
>  CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
> (
>         [GeometryIndex] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
>
> SET ANSI_PADDING OFF
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269) OR
> [GeometryItem] IS NULL))
> GO
>
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
> [Sanit_GM_GeometryItem_srid]
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_type] CHECK
> (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR [GeometryItem]
> IS NULL))
> GO
>
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
> [Sanit_GM_GeometryItem_type]
> GO
>
>
>
>
> VIEW:
> SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea, LifeStatus,
> LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill,
> BedMat, Capacity, Diameter,
>                       DoStreamIn, HasLining, Height, Length, LiningYear,
> LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
> FROM         dbo.Sanit_GM AS new
>
> I am able to see layer pointing to this view. AND I am able to select
> features on the map.
>  <http://n2.nabble.com/file/n2356197/1.Selecting.png>
> <http://n2.nabble.com/file/n2356197/2.Selected.png>
>
> If I modify View to include any JOIN I will be able to see layer
> pointing to view, BUT would NOT be able to select features on the map:
>  <http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png>
> <http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png>
>
> VIEW:
> SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID,
> new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID,
> new.SpatialKey, new.SpatialSta, new.AssetType,
>                       new.Backfill, new.BedMat, new.Capacity,
> new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length,
> new.LiningYear, new.LiningLen, new.PipeClass,
>                       new.PipeFunc, new.PipeShape, new.UpStreamIn,
> new.Width
> FROM         dbo.Sanit_GM AS new INNER JOIN
>                       dbo.Asset AS a ON a.DisplayId = new.AssetID
>
>
> Now, MYSTERY  <http://n2.nabble.com/images/smiley/anim_crazy.gif>
>
> If I will not refresh Dataconnection after adding join to the view - I
> am still able to select features on the map.
>
> Since Map, Layer and FeatureSource XML looks exactly the same in both
> cases I wonder what is the difference? What is changing when FDO detects
> JOIN in view?
> Can I override this change? Can I force FDO to ignore it? Is it FDO,
> which prevents selection or something else?
>
> Is this right forum to post? Is this a bug?
>
>
> Thank you,
>
>
>
> ________________________________
>
> This email is a reply to your post @
> http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
> res-on-the-layer.-tp2356197p2356328.html
> You can reply by email or by visting the link above.
>
>
>
>
>
> ----Notice Regarding Confidentiality----
> This email, including any and all attachments, (this "Email") is intended
> only for the party to whom it is addressed and may contain information
> that is confidential or privileged.  Sierra Systems Group Inc. and its
> affiliates accept no responsibility for any loss or damage suffered by any
> person resulting from any unauthorized use of or reliance upon this Email.
> If you are not the intended recipient, you are hereby notified that any
> dissemination, copying or other use of this Email is prohibited.  Please
> notify us of the error in communication by return email and destroy all
> copies of this Email.  Thank you.
>
>

--
View this message in context: http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-features-on-the-layer.-tp2356197p2356967.html
Sent from the FDO Users mailing list archive at Nabble.com.

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[miansi]

Thank you Brent!

I suspected something like that.

Well, for now I decided to give up on the issue.

I created one SQL View, which returns information (along with Geo
attribute) and use Filter option on the Layer to create specific layers.
As you can see - this View will return ALL records from the database. I
did it that way, so one day, when Views with JOINs will start working it
will be easy for me to adopt it :-).

At the same time I am thinking about the way of manually overriding
Class Definition. If it is possible at all.
I know I can get Identity Properties:
http://n2.nabble.com/Get-the-key-field-of-ODBC-table-td2389826.html#a239
2835

I wonder if I can SET them... That can help me.
I will post message in MGOS forum for that.

I created Views with SCHEMABINDING and index (nonclustered) - result was
exactly the same. I was able to create a view, saw it in the list of
available datasources for the Layer, but wasn't able to select features
on the map after setting Layer to use this view.


Thank you!



-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Brent Robinson
Sent: Friday, February 27, 2009 8:48 AM
To: FDO Users Mail List
Subject: RE: [fdo-users] SQL 2008. View with JOINS. Not able to
selectfeatures on the layer.

Hi,

When the SQLServerSpatial provider generates a class for a view, it
derives the identity properties from the primary key (or unique index if
primary key is not present) of the base table. However, this is only
done if the view is only based on one table. If the view joins 2 tables,
it ends up without identity. It's features can be drawn in MapGuide but
are not selectable. I looked at a couple of options but some limitations
prevent them from being used until modifications are done to the
provider.

One way to specify the identity properties is through a config document,
which can be attached to the feature source. Unfortunately, the
SQLServerSpatial advertises that it does not support config documents
even though it does. I've logged a trac ticket
(http://trac.osgeo.org/fdo/ticket/469) for this defect.

SQL Server allows clustered indexes to be set on deterministic views but
the provider does not currently look at indexes on views. A ticket has
also been logged for this issue (http://trac.osgeo.org/fdo/ticket/470).
In another e-mail, you mentioned trying the deterministic view (with
schemabinding). Did you encounter a problem setting up the view, or did
you get it created successfully but found that the features were still
not selectable?  

Unfortunately, none of these lead to an immediate solution for FDO 3.4.
I'll continue to investigate to see if there is another alternative.

Brent.
 
-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Jackie Ng
Sent: Thursday, February 19, 2009 10:30 PM
To: [hidden email]
Subject: RE: [fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.


A Feature Source describes a FDO connection, so get the XML content for
that
feature source, and look at each <Name> and <Value> element between the
<Parameter> elements.

When you connect via the generic dialog, select the SQL Server Spatial
provider. The connection property grid should be filled with the
required
parameters (which should match your <Name> elements). Fill in the blanks
using the matching <Value> elements in the feature source XML.

You shouldn't need to use the ConnectionString property, but if you do I
think you have to surround the value with quotes (like the ODBC
provider)

- Jackie


miansi wrote:

>
> Thank you Jackie.
>
>
>
> Can you help me with connection string? Seems like FDO toolbox missing
> simple example of connection string for SQL 2008.
>
>
>
>
>
> Thank you,
>
>
>
> From: Jackie Ng (via Nabble)
> [mailto:[hidden email]]
> Sent: Thursday, February 19, 2009 5:15 PM
> To: Sinelnikov, Andrei
> Subject: Re: [fdo-users] SQL 2008. View with JOINS. Not able to select
> features on the layer.
>
>
>
> Maybe try looking at this data source through FDO Toolbox
> (http://fdotoolbox.googlecode.com), and take a look at the feature
> classes, and see if the view (with the join) is structurally different
> from the view (without the join).
>
> For example, I could foresee some problem if the view (without the

join)

> has an Identity Property and the view (with the join) doesn't.
>
> - Jackie
>
> miansi wrote:
>
> Hello,
>
> Please advise what, where and how should I poke
> <http://n2.nabble.com/images/smiley/anim_confused.gif>
>
> Here is the issue:
>
> I have SQL 2008 table with geospatial data. I created Dataconnection,
> Layer, Map and am able to see data from view, which selecting

everything

> from my geospatial table:
>
> TABLE:
> /****** Object:  Table [dbo].[Sanit_GM]    Script Date: 02/19/2009
> 16:14:56 ******/
> SET ANSI_NULLS ON
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
>
> SET ANSI_PADDING ON
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM]') AND type in (N'U'))
> BEGIN
> CREATE TABLE [dbo].[Sanit_GM](
>         [GeometryItem] [geometry] NULL,
>         [GeometryIndex] [int] IDENTITY(1,1) NOT NULL,
>         [AssetID] [varchar](10) NULL,
>         [InstallYea] [int] NULL,
>         [LifeStatus] [varchar](50) NULL,
>         [LocDesc] [varchar](50) NULL,
>         [MaterialID] [varchar](50) NULL,
>         [SpatialKey] [varchar](10) NULL,
>         [SpatialSta] [varchar](50) NULL,
>         [AssetType] [varchar](50) NULL,
>         [Backfill] [varchar](50) NULL,
>         [BedMat] [varchar](50) NULL,
>         [Capacity] [decimal](38, 18) NULL,
>         [Diameter] [int] NULL,
>         [DoStreamIn] [decimal](38, 18) NULL,
>         [HasLining] [bit] NULL,
>         [Height] [int] NULL,
>         [Length] [decimal](38, 18) NULL,
>         [LiningYear] [int] NULL,
>         [LiningLen] [decimal](38, 18) NULL,
>         [PipeClass] [varchar](50) NULL,
>         [PipeFunc] [varchar](50) NULL,
>         [PipeShape] [varchar](50) NULL,
>         [UpStreamIn] [decimal](38, 18) NULL,
>         [Width] [varchar](50) NULL,
>  CONSTRAINT [Sanit_GM_pkey] PRIMARY KEY CLUSTERED
> (
>         [GeometryIndex] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY

=

> OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> END
> GO
>
> SET ANSI_PADDING OFF
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id

=
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_srid] CHECK  (([GeometryItem].[STSrid]=(4269)
OR
> [GeometryItem] IS NULL))
> GO
>
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_srid]') AND parent_object_id
=
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
> [Sanit_GM_GeometryItem_srid]
> GO
>
> IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id
=
> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM]  WITH CHECK ADD  CONSTRAINT
> [Sanit_GM_GeometryItem_type] CHECK
> (([GeometryItem].[STGeometryType]()='MULTILINESTRING' OR
[GeometryItem]
> IS NULL))
> GO
>
> IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id =
> OBJECT_ID(N'[dbo].[Sanit_GM_GeometryItem_type]') AND parent_object_id
=

> OBJECT_ID(N'[dbo].[Sanit_GM]'))
> ALTER TABLE [dbo].[Sanit_GM] CHECK CONSTRAINT
> [Sanit_GM_GeometryItem_type]
> GO
>
>
>
>
> VIEW:
> SELECT     GeometryItem, GeometryIndex, AssetID, InstallYea,

LifeStatus,
> LocDesc, MaterialID, SpatialKey, SpatialSta, AssetType, Backfill,
> BedMat, Capacity, Diameter,
>                       DoStreamIn, HasLining, Height, Length,
LiningYear,

> LiningLen, PipeClass, PipeFunc, PipeShape, UpStreamIn, Width
> FROM         dbo.Sanit_GM AS new
>
> I am able to see layer pointing to this view. AND I am able to select
> features on the map.
>  <http://n2.nabble.com/file/n2356197/1.Selecting.png>
> <http://n2.nabble.com/file/n2356197/2.Selected.png>
>
> If I modify View to include any JOIN I will be able to see layer
> pointing to view, BUT would NOT be able to select features on the map:
>  <http://n2.nabble.com/file/n2356197/1.Selecting%2B-%2BJOIN.png>
> <http://n2.nabble.com/file/n2356197/2.Selected%2B-%2BJOIN.png>
>
> VIEW:
> SELECT     new.GeometryItem, new.GeometryIndex, new.AssetID,
> new.InstallYea, new.LifeStatus, new.LocDesc, new.MaterialID,
> new.SpatialKey, new.SpatialSta, new.AssetType,
>                       new.Backfill, new.BedMat, new.Capacity,
> new.Diameter, new.DoStreamIn, new.HasLining, new.Height, new.Length,
> new.LiningYear, new.LiningLen, new.PipeClass,
>                       new.PipeFunc, new.PipeShape, new.UpStreamIn,
> new.Width
> FROM         dbo.Sanit_GM AS new INNER JOIN
>                       dbo.Asset AS a ON a.DisplayId = new.AssetID
>
>
> Now, MYSTERY  <http://n2.nabble.com/images/smiley/anim_crazy.gif>
>
> If I will not refresh Dataconnection after adding join to the view - I
> am still able to select features on the map.
>
> Since Map, Layer and FeatureSource XML looks exactly the same in both
> cases I wonder what is the difference? What is changing when FDO

detects

> JOIN in view?
> Can I override this change? Can I force FDO to ignore it? Is it FDO,
> which prevents selection or something else?
>
> Is this right forum to post? Is this a bug?
>
>
> Thank you,
>
>
>
> ________________________________
>
> This email is a reply to your post @
>

http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
> res-on-the-layer.-tp2356197p2356328.html
> You can reply by email or by visting the link above.
>
>
>
>
>
> ----Notice Regarding Confidentiality----
> This email, including any and all attachments, (this "Email") is
intended
> only for the party to whom it is addressed and may contain information
> that is confidential or privileged.  Sierra Systems Group Inc. and its
> affiliates accept no responsibility for any loss or damage suffered by
any
> person resulting from any unauthorized use of or reliance upon this
Email.
> If you are not the intended recipient, you are hereby notified that
any
> dissemination, copying or other use of this Email is prohibited.
Please
> notify us of the error in communication by return email and destroy
all
> copies of this Email.  Thank you.
>
>

--
View this message in context:
http://n2.nabble.com/SQL-2008.-View-with-JOINS.-Not-able-to-select-featu
res-on-the-layer.-tp2356197p2356967.html
Sent from the FDO Users mailing list archive at Nabble.com.

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users


----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended only for the party to whom it is addressed and may contain information that is confidential or privileged.  Sierra Systems Group Inc. and its affiliates accept no responsibility for any loss or damage suffered by any person resulting from any unauthorized use of or reliance upon this Email.  If you are not the intended recipient, you are hereby notified that any dissemination, copying or other use of this Email is prohibited.  Please notify us of the error in communication by return email and destroy all copies of this Email.  Thank you.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Jason Birch]

Brent,
 
Do you think that the capabilities reporting change for #469 is non-invasive enough that it could be implemented prior to 3.4 release?  
 
If the provider already supports configuration, it looks to me like it would only require a couple lines in FdoRdbmsSqlServerConnectionCapabilities.h and FdoRdbmsSqlServerConnectionCapabilities.cpp.  I don't know enough about C++ to know whether overriding these inherited properties in the sql server connection capabilities class would break anything though.
 
This question has come up quite a few times on the mailing lists now, and it would be nice to at least have a workaround for supporting SQL Server views.
 
Jason

________________________________

From: Brent Robinson
Sent: Fri 2009-02-27 7:47 AM
To: FDO Users Mail List
Subject: RE: [fdo-users] SQL 2008. View with JOINS. Not able to selectfeatures on the layer.

One way to specify the identity properties is through a config document, which can be attached to the feature source. Unfortunately, the SQLServerSpatial advertises that it does not support config documents even though it does. I've logged a trac ticket (http://trac.osgeo.org/fdo/ticket/469) for this defect.


_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users