<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2668" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=975065020-22072005><FONT
face=Arial>Solved!</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=975065020-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=975065020-22072005><FONT face=Arial>In case
anyone is interested, I just changed the embedded xml within the mapfile as
follows:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=975065020-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=975065020-22072005><FONT
face=Arial> <SrcSQL>EXEC storedproc
"%parameter%"</SrcSQL></FONT></SPAN></DIV>
<DIV><SPAN class=975065020-22072005><FONT face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=975065020-22072005><FONT face=Arial>This substantially speeds
up the generation of a map if you happen to be using a parameterized stored
procedure in SQL Server 2000.</FONT></SPAN></DIV>
<DIV><SPAN class=975065020-22072005><FONT face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=975065020-22072005><FONT face=Arial>Dave</DIV>
<DIV dir=ltr align=left><BR></DIV></FONT></SPAN><BR>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> UMN MapServer Users List
[mailto:MAPSERVER-USERS@LISTS.UMN.EDU] <B>On Behalf Of </B>David
Shorthouse<BR><B>Sent:</B> Friday, July 22, 2005 2:07 PM<BR><B>To:</B>
MAPSERVER-USERS@LISTS.UMN.EDU<BR><B>Subject:</B> [UMN_MAPSERVER-USERS]
Embedding connection xml in mapfile<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>Hello
folks,</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005> <FONT face=Arial>I am
making use of an OGR virtual data ODBC connection to SQL Server 2000. I have a
separate xml file to handle the query (actually it's a call to a stored
procedure). Ideally, I'd like to pass a variable from the mapfile to this
query and make use of a parameterized stored procedure. I discovered that one
can embed the xml within the mapfile, which should allow this. I can
format the separate xml file for the call to the stored procedure to include a
parameter and it works great. In this case however, I have no hope of passing
a variable as the parameter. The xml must be in the map file. When I attempt
to do this however, the layer just doesn't generate because the connection
chokes.</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>Here's what I have in the
separate xml to handle the connection:</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial><OGRVRTDataSource><BR> <OGRVRTLayer
name="SpiderData"><BR>
<SrcDataSource>ODBC:[connectionhere]</SrcDataSource>
<BR> <SrcLayer>SpeciesMapServer</SrcLayer><BR> <SrcSQL>{CALL
SpeciesMapServer}</SrcSQL><BR> <GeometryType>wkbPoint</GeometryType><BR> <LayerSRS>WGS84</LayerSRS>
<BR> <GeometryField encoding="PointFromColumns" x="UTMx" y="UTMy"/>
<BR>
</OGRVRTLayer><BR></OGRVRTDataSource></FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>I can change the stored
procedure to include a parameter and the SrcSQL above to {CALL
SpeciesMapServer ('parameter')} and it works great. But of course, this
doesn't permit passing a variable such as %parameter% from the
mapfile.</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>So, here is what I tried
in the mapfile:</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial> LAYER<BR> NAME
spiders<BR> CONNECTIONTYPE OGR<BR>
CONNECTION '<OGRVRTDataSource><BR> <OGRVRTLayer
name="SpiderData"<BR>
<SrcDataSource>ODBC:[connectionhere]</SrcDataSource>
<BR> <SrcLayer>SpeciesMapServer</SrcLayer><BR> <SrcSQL>{CALL
SpeciesMapServer
('%FullName%')}</SrcSQL><BR> <GeometryType>wkbPoint</GeometryType><BR> <LayerSRS>WGS84</LayerSRS>
<BR> <GeometryField encoding="PointFromColumns" x="UTMx" y="UTMy"/>
<BR>
</OGRVRTLayer><BR></OGRVRTDataSource>'<BR> DATA
SpiderData<BR> STATUS DEFAULT<BR> TYPE
POINT<BR> etc.</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>It's the SrcSQL above
that chokes. Notice the required, either single or double quote around
%FullName%. This is obviously results in a truncation of the remainder of the
connection string in the case of a single quote or failure of a layer being
returned in the case of a double quote.</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT face=Arial>Anyone know how to get
this to work? Is it just not possible to pass a mapfile variable to a
stored procedure's parameter?</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=338221019-22072005><FONT
face=Arial>Dave</FONT></SPAN></DIV></BLOCKQUOTE></BODY></HTML>