[MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR VRT Layer

[Pascoe,Tim (ECCC)]

Seth,

                I can connect via OGRINFO using the MSSQL driver, after setting the environment variable for:

MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO

Since the user account for this application does not have CREATE TABLE permissions. The layer is a VRT taken from a non-spatial SQL table (just lat/long columns). Sadly, when accessing the layer via MapServer through the web application using the full map file, the Create Table error persists.

Timothy Pascoe

Please note the return to our departmental E-mail address below. My Canada.ca address will also continue to work for the time being/Veuillez noter le retour à notre adresse électronique départementale ci-dessous. Mon adresse Canada.ca continuera également à fonctionner pour le moment.

Environmental Scientist, Water Quality Monitoring & Surveillance
Environment & Climate Change Canada (ECCC)
tim.pascoe at ec.gc.ca<mailto:tim.pascoe at ec.gc.ca> T: 905-336-6239 / C: 289-253-7857

Scientifique de l'environement, Surveillance de la qualité de l'eau
Environnement et Changement climatique Canada (ECCC)
tim.pascoe at ec.gc.ca<mailto:tim.pascoe at ec.gc.ca> T: 905-336-6239 / C: 289-253-7857

[Environment Canada]

From: Seth G <sethg at geographika.co.uk>
Sent: Thursday, September 19, 2024 3:00 AM
To: Pascoe,Tim (ECCC) <Tim.Pascoe at ec.gc.ca>; MapServer Users <mapserver-users at lists.osgeo.org>
Subject: Re: [MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR VRT Layer

You don't often get email from sethg at geographika.co.uk<mailto:sethg at geographika.co.uk>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Hi Tim,

A couple of suggestions to try.


  1.  Can you connect with the same connection string details using ogrinfo?
  2.  Are you able to test with the MSSQL driver in GDAL? https://gdal.org/en/latest/drivers/vector/mssqlspatial.html
Seth

--
web:https://geographika.net<https://geographika.net/> & https://mapserverstudio.net<https://mapserverstudio.net/>
twitter: @geographika

On Wed, Sep 18, 2024, at 11:12 PM, Pascoe, Tim (ECCC) via MapServer-users wrote:

I've spent a fair amount of time trying to track down an ODBC connection issue after upgrading to Mapserver 8.2.2



Platform:

Windows Server 2016

IIS

MS SQL Server 2016

Mapserver 8.2.2 (binaries from GISInternals)



Creating an ODBC connection using the MS administrator works, and the connection test is successful.

The DSN is a System DSN using the ODBC Driver 17 for SQL Server

The user account for the connection is confirmed to have access to the SQL View being used for a VRT layer using OGR



Testing with OGRINFO on the following .ovf file:



<OGRVRTDataSource>

                                <OGRVRTLayer name='CABINsite'>

                                                <SrcDataSource>ODBC:****/***** @Cabinservice,qryWMSInternal</SrcDataSource>

                                                <SrcSQL>SELECT * FROM qryWMSInternal WHERE sitedetailsid = ****</SrcSQL>

                                    <FID>sitedetailsid</FID>

                                                <GeometryType>wkbPoint</GeometryType>

                                                <LayerSRS>WGS84</LayerSRS>

                                <GeometryField encoding='PointFromColumns' x='longitude' y='latitude'/>

                                </OGRVRTLayer>

</OGRVRTDataSource>



Generates the error "Failed to open datasource 'ODBC:****/****....."



The same test in my production environment, using Mapserver 7.x,  connects to the VRT layer and reports as expected.



Anyone have something else I can look for? I've seen a number of posts in various forums with similar issues, but they all seem to resolve with an error in the connection string. Since I'm sure the format/credentials is correct, I'm not sure where else to look. Is there a change in how OGR/GDAL operates in the 8.2.2 binaries I have (security changes?) that would result in this issue?



Thanks,



Tim


_______________________________________________
MapServer-users mailing list
MapServer-users at lists.osgeo.org<mailto:MapServer-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/mapserver-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240919/6d9060b4/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 2185 bytes
Desc: image001.gif
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240919/6d9060b4/attachment.gif>