[fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.
miansi
AndreiSinelnikov at SierraSystems.com
Fri Feb 20 12:06:47 EST 2009
Am I missing something?
http://n2.nabble.com/file/n2360050/FDO%2BToolbox%2Bconnection%2B-%2BFeature%2BSource.png
http://n2.nabble.com/file/n2360050/FDO%2BToolbox%2Bconnection.png
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: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.-tp2356197p2360050.html
Sent from the FDO Users mailing list archive at Nabble.com.
More information about the fdo-users
mailing list