<div>Hi Dave,</div>
<div> </div>
<div>Have you tried using double quotes for the start and end of the CONNECTION, and single quotes for all of the quoted values within the xml, not just around the variable? or do the xml values require the double quotes you have given them?
</div>
<div> </div>
<div>Cheers,<br>Ken Lord</div>
<div>Vancouver BC</div>
<div><br><br> </div>
<div><span class="gmail_quote">On 7/22/05, <b class="gmail_sendername">David Shorthouse</b> <<a href="mailto:dps1@ualberta.ca">dps1@ualberta.ca</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<div><span><font face="Arial">Hello folks,</font></span></div>
<div><span><font face="Arial"></font></span> </div>
<div><span> <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><font face="Arial"></font></span> </div>
<div><span><font face="Arial">Here's what I have in the separate xml to handle the connection:</font></span></div>
<div><span><font face="Arial"></font></span> </div>
<div><span><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><font face="Arial"></font></span> </div>
<div><span><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><font face="Arial"></font></span> </div>
<div><span><font face="Arial">So, here is what I tried in the mapfile:</font></span></div>
<div><span><font face="Arial"></font></span> </div>
<div><span><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><font face="Arial"></font></span> </div>
<div><span><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><font face="Arial"></font></span> </div>
<div><span><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><font face="Arial"></font></span> </div>
<div><span><font face="Arial">Thanks,</font></span></div>
<div><span><font face="Arial"></font></span> </div>
<div><span><font face="Arial">Dave</font></span></div></blockquote></div><br><br clear="all">