Embedding connection xml in mapfile

[David Shorthouse]

Solved!
 
In case anyone is interested, I just changed the embedded xml within the
mapfile as follows:
 
 <SrcSQL>EXEC storedproc "%parameter%"</SrcSQL>
 
This substantially speeds up the generation of a map if you happen to be
using a parameterized stored procedure in SQL Server 2000.
 
Dave



  _____  

From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of David Shorthouse
Sent: Friday, July 22, 2005 2:07 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] Embedding connection xml in mapfile


Hello folks,
 
    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.
 
Here's what I have in the separate xml to handle the connection:
 
<OGRVRTDataSource>
    <OGRVRTLayer name="SpiderData">
        <SrcDataSource>ODBC:[connectionhere]</SrcDataSource> 
  <SrcLayer>SpeciesMapServer</SrcLayer>
 <SrcSQL>{CALL SpeciesMapServer}</SrcSQL>
 <GeometryType>wkbPoint</GeometryType>
 <LayerSRS>WGS84</LayerSRS>         
 <GeometryField encoding="PointFromColumns" x="UTMx" y="UTMy"/> 
    </OGRVRTLayer>
</OGRVRTDataSource>
 
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.
 
So, here is what I tried in the mapfile:
 
 LAYER
    NAME spiders
    CONNECTIONTYPE OGR
    CONNECTION '<OGRVRTDataSource>
    <OGRVRTLayer name="SpiderData"
        <SrcDataSource>ODBC:[connectionhere]</SrcDataSource> 
  <SrcLayer>SpeciesMapServer</SrcLayer>
 <SrcSQL>{CALL SpeciesMapServer ('%FullName%')}</SrcSQL>
 <GeometryType>wkbPoint</GeometryType>
 <LayerSRS>WGS84</LayerSRS>         
 <GeometryField encoding="PointFromColumns" x="UTMx" y="UTMy"/> 
    </OGRVRTLayer>
</OGRVRTDataSource>'
    DATA SpiderData
    STATUS DEFAULT
    TYPE POINT
     etc.
 
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.
 
Anyone know how to get this to work? Is it just not possible to pass a
mapfile variable to a stored procedure's parameter?
 
Thanks,
 
Dave

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20050722/4fa77c5a/attachment.htm>