[fdo-users] SQL 2008. View with JOINS. Not able to selectfeatures
on the layer.
Sinelnikov, Andrei
AndreiSinelnikov at SierraSystems.com
Fri Feb 27 15:35:32 EST 2009
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: fdo-users-bounces at lists.osgeo.org
[mailto:fdo-users-bounces at lists.osgeo.org] 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: fdo-users-bounces at lists.osgeo.org
[mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Jackie Ng
Sent: Thursday, February 19, 2009 10:30 PM
To: fdo-users at lists.osgeo.org
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: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-featu
res-on-the-layer.-tp2356197p2356967.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
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.
More information about the fdo-users
mailing list