<!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><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></BODY></HTML>