<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns2="http://schemas.microsoft.com/office/2004/12/omml">
<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--a:link
{mso-style-priority:99;}
span.MSOHYPERLINK
{mso-style-priority:99;}
a:visited
{mso-style-priority:99;}
span.MSOHYPERLINKFOLLOWED
{mso-style-priority:99;}
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:Arial;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:Calibri;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 89.85pt 72.0pt 89.85pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-CA link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Hello Gavin,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Thus there seem to be a problem with the
provider.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Just to be sure and as far as I know,
views can’t have a primary key on SQL Server. However, my source table
have one.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>My main table SQL is:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=blue
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:blue'>CREATE</span></font><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> <font color=blue><span
style='color:blue'>TABLE</span></font> [dbo]<font color=gray><span
style='color:gray'>.</span></font>[Tab_XY]<font color=gray><span
style='color:gray'>(<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [ID] [smallint] <font
color=gray><span style='color:gray'>NOT</span></font> <font color=gray><span
style='color:gray'>NULL,<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [X] [float] <font
color=gray><span style='color:gray'>NOT</span></font> <font color=gray><span
style='color:gray'>NULL,<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [Y] [float] <font
color=gray><span style='color:gray'>NOT</span></font> <font color=gray><span
style='color:gray'>NULL,<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [Test] [float] <font
color=gray><span style='color:gray'>NOT</span></font> <font color=gray><span
style='color:gray'>NULL,<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [Z] [float] <font
color=gray><span style='color:gray'>NOT</span></font> <font color=gray><span
style='color:gray'>NULL,<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> <font color=blue><span
style='color:blue'>CONSTRAINT</span></font> [PK_Tab_XY] <font color=blue><span
style='color:blue'>PRIMARY</span></font> <font color=blue><span
style='color:blue'>KEY</span></font> <font color=blue><span style='color:blue'>CLUSTERED</span></font>
<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=gray
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:gray'>(<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> [ID] <font color=blue><span
style='color:blue'>ASC<o:p></o:p></span></font></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=gray
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:gray'>)</span></font><font size=2 color=blue face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New";color:blue'>WITH</span></font><font
size=2 face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New"'>
<font color=gray><span style='color:gray'>(</span></font><font color=blue><span
style='color:blue'>PAD_INDEX</span></font> <font color=gray><span
style='color:gray'>=</span></font> <font color=blue><span style='color:blue'>OFF</span></font><font
color=gray><span style='color:gray'>,</span></font> <font color=blue><span
style='color:blue'>STATISTICS_NORECOMPUTE</span></font> <font color=gray><span
style='color:gray'>=</span></font> <font color=blue><span style='color:blue'>OFF</span></font><font
color=gray><span style='color:gray'>,</span></font> <font color=blue><span
style='color:blue'>IGNORE_DUP_KEY</span></font> <font color=gray><span
style='color:gray'>=</span></font> <font color=blue><span style='color:blue'>OFF</span></font><font
color=gray><span style='color:gray'>,</span></font> <font color=blue><span
style='color:blue'>ALLOW_ROW_LOCKS</span></font> <font color=gray><span
style='color:gray'>=</span></font> <font color=blue><span style='color:blue'>ON</span></font><font
color=gray><span style='color:gray'>,</span></font> <font color=blue><span
style='color:blue'>ALLOW_PAGE_LOCKS</span></font> <font color=gray><span
style='color:gray'>=</span></font> <font color=blue><span style='color:blue'>ON</span></font><font
color=gray><span style='color:gray'>)</span></font> <font color=blue><span
style='color:blue'>ON</span></font> [PRIMARY]<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=gray
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:gray'>)</span></font><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> <font color=blue><span
style='color:blue'>ON</span></font> [PRIMARY]<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>My view SQL is:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=blue
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:blue'>CREATE</span></font><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> <font color=blue><span
style='color:blue'>VIEW</span></font> [dbo]<font color=gray><span
style='color:gray'>.</span></font>[New_View]<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=blue
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:blue'>AS<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=blue
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:blue'>SELECT</span></font><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> ID <font color=blue><span
style='color:blue'>AS</span></font> vID<font color=gray><span style='color:
gray'>,</span></font> X <font color=blue><span style='color:blue'>AS</span></font>
vX<font color=gray><span style='color:gray'>,</span></font> <st1:City w:st="on">Y</st1:City>
<st1:State w:st="on"><font color=blue><span style='color:blue'>AS</span></font></st1:State>
vY<font color=gray><span style='color:gray'>,</span></font> <st1:City w:st="on">Test</st1:City>
<st1:State w:st="on"><font color=blue><span style='color:blue'>AS</span></font></st1:State>
vTest<font color=gray><span style='color:gray'>,</span></font> <st1:place
w:st="on"><st1:City w:st="on">Z</st1:City> <st1:State w:st="on"><font
color=blue><span style='color:blue'>AS</span></font></st1:State></st1:place>
vZ<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 color=blue
face="Courier New"><span style='font-size:10.0pt;font-family:"Courier New";
color:blue'>FROM</span></font><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> dbo<font
color=gray><span style='color:gray'>.</span></font>Tab_XY<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>The output in MapGuide Studio after a show
table is:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>[Table] [Key
Fields]<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Tab_XY ID<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>New_View vID, vX, vY, vTest,
vZ<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>So, I assume that the provider can’t
detect that vID is a primary key. <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Note that I tested that if the X, Y and Z
columns are nullable, the provider does not choose them as keys in New_View but
my customer can’t (or don’t want to) change the structure of his tables.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Regards,<o:p></o:p></span></font></p>
<p class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'>Pierre</span></font></st1:place></st1:City><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span lang=EN-US style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span lang=EN-US
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font></b><font
size=2 face=Tahoma><span lang=EN-US style='font-size:10.0pt;font-family:Tahoma'>
fdo-users-bounces@lists.osgeo.org [mailto:fdo-users-bounces@lists.osgeo.org] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Gavin Cramer<br>
<b><span style='font-weight:bold'>Sent:</span></b> June 11, 2008 12:03<br>
<b><span style='font-weight:bold'>To:</span></b> <st1:PersonName w:st="on">fdo-users@lists.osgeo.org</st1:PersonName><br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [fdo-users] FDO OSGeo
for ODBC</span></font><span lang=EN-US><o:p></o:p></span></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span lang=EN-US
style='font-size:11.0pt;font-family:Calibri;color:#1F497D'>Yes, it does look
for views. Does the view lack a primary key? The FDO ODBC Provider
has a number of ways to find identifier properties, with the main one
being primary keys. After that there are a number of fallbacks, with the
last one being to grab any non-NULLable columns and treat them as identifier
properties. There has been discussion in the past of removing this last
fallback, but it has not happened yet. In the meantime, if the view can
be given a primary key (anything expressly denoted as such, or anything that is
autogenerated), ODBC Provider will use it instead of the columns that you want
for ordinates.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span lang=EN-US
style='font-size:11.0pt;font-family:Calibri;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span lang=EN-US
style='font-size:11.0pt;font-family:Calibri;color:#1F497D'>Gavin<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span lang=EN-US
style='font-size:11.0pt;font-family:Calibri;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span lang=EN-US
style='font-size:11.0pt;font-family:Calibri;color:#1F497D'><o:p> </o:p></span></font></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><font size=2 face=Tahoma><span lang=EN-US
style='font-size:10.0pt;font-family:Tahoma;font-weight:bold'>From:</span></font></b><font
size=2 face=Tahoma><span lang=EN-US style='font-size:10.0pt;font-family:Tahoma'>
fdo-users-bounces@lists.osgeo.org [mailto:fdo-users-bounces@lists.osgeo.org] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Pierre Gauthier<br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, June 10, 2008 11:54
AM<br>
<b><span style='font-weight:bold'>To:</span></b> <st1:PersonName w:st="on">fdo-users@lists.osgeo.org</st1:PersonName><br>
<b><span style='font-weight:bold'>Subject:</span></b> [fdo-users] FDO OSGeo for
ODBC<o:p></o:p></span></font></p>
</div>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span lang=EN-US
style='font-size:12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hello,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I’m using the FDO ODBC provider with MapGuide Studio
2009 to connect to a SQLServer database and I wonder if views are supported
when generating the list of tables.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>The problem I have is that if my view is created from a
table where my X and Y columns does not allow nulls, MapGuide Studio list my X
and Y columns in the view as if they are key fields and I can`t select them as
X and Y.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I found a way to workaround this problem by using the
mapagent and editing the xml but this is a bit annoying for myself and I can
imagine how it can be for my customer.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Anybody else has seen this issue?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Regards,<o:p></o:p></span></font></p>
<p class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Pierre</span></font></st1:place></st1:City><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face=Arial><span lang=FR-CA style='font-size:
12.0pt;font-family:Arial'>--</span></font><span lang=FR-CA><o:p></o:p></span></p>
<p class=MsoNormal><strong><b><font size=3 face=Arial><span lang=FR-CA
style='font-size:12.0pt;font-family:Arial'>Pierre Gauthier</span></font></b></strong><span
lang=FR-CA><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=FR-CA style='font-size:
10.0pt;font-family:Arial'>Analyste programmeur sénior</span></font><span
lang=FR-CA><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><a href="mailto:pierrega@consortech.com"><span lang=FR-CA>pierrega@consortech.com</span></a></span></font><span
lang=FR-CA><o:p></o:p></span></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Solutions Consortech inc.</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Tél: 450-676-1555 x217</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Fax: 450-676-8851</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>