[fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.
miansi
AndreiSinelnikov at SierraSystems.com
Thu Feb 19 22:01:08 EST 2009
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:ml-user+64137-1002950291 at n2.nabble.com]
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.-tp2356197p2356875.html
Sent from the FDO Users mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-users/attachments/20090219/45f1282f/attachment-0001.html
More information about the fdo-users
mailing list