[fdo-users] SQL 2008. View with JOINS. Not able to select
features on the layer.
Jackie Ng
jumpinjackie at gmail.com
Thu Feb 19 22:29:42 EST 2009
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.-tp2356197p2356967.html
Sent from the FDO Users mailing list archive at Nabble.com.
More information about the fdo-users
mailing list