[mapserver-dev] ODBC Connection...

[Brent Fraser]

Christian,

  You'll need to add some kind of user-interface widget (a textbox or combo box, etc) to have the user set the value.  Then it needs to be passed as a CGI keyword=value (like "ID=53377") to the URL that does a mapserver request.  Maybe a Chameleon expert can comment on how to add a custom CGI variable.   I just finished going through that process using GeoMoose as a client.

  As for mapserver, you need to get it to do its Variable Substitution (http://mapserver.org/mapfile/variable_sub.html) in the CONNECTION string.  To do that, don't use a .OVF file; instead move the connection definition into your .MAP file.  Mapserver will parse the .MAP file and do substitution of the value for the keyword (it doesn't do parsing of .OVF files).  For example, if you pass ID=53377 in the URL, your LAYER definition in the .MAP file should look like:

CONNECTIONTYPE OGR
CONNECTION "<OGRVRTDataSource>
    <OGRVRTLayer name='cartracks'>
        <SrcDataSource>ODBC:trak/trakrm at webtrak</SrcDataSource>
        <SrcSQL>SELECT TOP 1 ID FROM Guardar WHERE ID='%ID%'</SrcSQL>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding='PointFromColumns' x='Lon' y='Lat'/>
    </OGRVRTLayer>
</OGRVRTDataSource>"
DATA 'cartracks'
STATUS DEFAULT # or ON if handled by the client


What will get passed to the ODBC connection is:
    SELECT TOP 1 ID FROM Guardar WHERE ID='53377'


  While this explains how use Variable Substitution, it doesn't answer your first question of why you don't see anything.  I would strongly suggest you confirm the connection is working (without the %ID%) using GDAL's ogrinfo with your .OVF file.  Then use Maperver's shp2img command line program to see if there are any mapserver related problems.  Enable the mapserver logging by putting a DEBUG 5 statement in the LAYER definition in your .MAP file and set the log file with 
    CONFIG "MS_ERRORFILE" "/tmp/ms_error.txt"

 Then finally use mapserv with your web server.  

Best Regards,
Brent Fraser


Christian Canales wrote:
> Thanks for you answer Brent...
>  
> I have another answer...
>  
> I found this page :
>  
> http://www.online-archaeology.co.uk/wordpress/?p=10
>  
> But in this page appear something about pass a parameter to the SQL 
> Server store procedure :
>  
> CONNECTION    "<OGRVRTDataSource>
>       <OGRVRTLayer name='POW'>
>           <SrcDataSource>ODBC:id/pwd at MyDSN</SrcDataSource>
>    <SrcSQL>EXEC NameofStoredProcedure '%param%'</SrcSQL>
>   <GeometryType>wkbPoint</GeometryType>
>           <LayerSRS>WGS84</LayerSRS>
>   <GeometryField encoding='PointFromColumns' x='Longitude' y='Latitude'/>
>       </OGRVRTLayer>
>   </OGRVRTDataSource>"
>   STATUS      ON
>   DATA      POW
> .
> .
> .
>  
> But how can I pass the parameter to the store procedure from my page? I 
> supose that I have to create a fields for the data entry, but somebody 
> knows how to code the parameter pass to the mapfile?
>  
> Best Regards...
> 
>  
> 
> ------------------------------------------------------------------------
> *De:* Brent Fraser <bfraser at geoanalytic.com>
> *Para:* Christian Canales <ccanales29 at yahoo.es>
> *CC:* mapserver-dev at lists.osgeo.org; szekerest at gmail.com
> *Enviado:* lunes, 15 de junio, 2009 21:49:56
> *Asunto:* Re: [mapserver-dev] ODBC Connection...
> 
> Christian,
> 
>   Assuming your ODBC datasource is connecting to your database, you may
> need to specify the spatial reference system of your data, either in the
> VRT spec below using <LayerSRS>, or in the LAYER object of your map file
> with a PROJECTION object.
> 
> You could try adding "DEBUG 5" to the layer definition and using shp2img
> to see if it reports any errors.
> 
> Best Regards,
> Brent Fraser
> 
> 
> 
> 
>>  Hi All...
>>
>>  I'm trying to connect my map with some MS SQL Server data...
>>
>>  So, I'm trying to do it using an ODBC connection via OGR in a *.OVF
>>  file...
>>
>>  This is the code in the file:
>>
>>  <OGRVRTDataSource>
>>    <OGRVRTLayer name="cartracks">
>>        <SrcDataSource>ODBC:trak/trakrm at webtrak</SrcDataSource>
>>        <SrcSQL>SELECT TOP 1 ID FROM Guardar WHERE ID='53377'</SrcSQL>
>>        <GeometryType>wkbPoint</GeometryType>
>>        <GeometryField encoding="PointFromColumns" x="Lon" y="Lat"/>
>>    </OGRVRTLayer>
>>  </OGRVRTDataSource>
>>
>>  But I cannot see anything...
>>
>>  Somebody knows what is wrong?
>>
>>  I'm in a Linux environment, with Mapserver 5.4.0 and Chameleon...
>>
>>  Thanks...
>>
>>
>>
>>
>>  _______________________________________________
>>  mapserver-dev mailing list
>>  mapserver-dev at lists.osgeo.org <mailto:mapserver-dev at lists.osgeo.org>
>>  http://lists.osgeo.org/mailman/listinfo/mapserver-dev
>>
>>
> 
> 
>