[mapserver-users] Mapserver - connection to MSSQL

[Robert Sanson]

Hi

I retyped the connection string into my Mapfile and I still get the same error.

The exact same connection string works fine via ogrinfo on the command line:

ogrinfo -sql "select * from par_table where par_no = 6777887" "MSSQL:driver=FreeTDS;server=myserver,1433;uid=xx;pwd=xxx;database=mydb;trusted_connection=no;tables=par_table(geom)"

The only error message is:

ERROR 1: Error initializing the metadata tables

However, it then proceeds to give me the answer correctly.

INFO: Open of `MSSQL:driver=FreeTDS;server=myserver,1433;uid=xx;pwd=xxx;database=mydb;trusted_connection=no;tables=par_table(geom)'
      using driver `MSSQLSpatial' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
PROJCS["NZGD2000 / New Zealand Transverse Mercator 2000",
    GEOGCS["NZGD2000",
        DATUM["New_Zealand_Geodetic_Datum_2000",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6167"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4167"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",0],
    PARAMETER["central_meridian",173],
    PARAMETER["scale_factor",0.9996],
    PARAMETER["false_easting",1600000],
    PARAMETER["false_northing",10000000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AUTHORITY["EPSG","2193"]]

etc.

Regards,

Robert

From: Rahkonen Jukka (MML) [mailto:jukka.rahkonen at maanmittauslaitos.fi]
Sent: Wednesday, 11 May 2016 7:28 p.m.
To: geographika at gmail.com; Robert Sanson <Robert.Sanson at asurequality.com>; mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Mapserver - connection to MSSQL

Hi,

The connection string in the mapfile does look perfect but something that happens for me is that if I copy and paste text the copy may  contain invisible characters which make troubles. I would rewrite the CONNECTION again by hand from keyboard and try if it helps.

-Jukka Rahkonen-

Seth G wrote:

Hi,

The error message indicates there is a problem with your connection string:

'MSSQL:driver=FreeTDS;server=db-agribase-p.asurequality.com' does not contain the 'database' portion

If the database part is not missing, maybe there is a missing semi-colon, or special character ending the database connection string?

Regards,

Seth

On 10/05/2016 23:54, Robert Sanson wrote:
Hi

I am trying to create a Mapserver (v 6.4.1 on Ubuntu) layer based on a MSSQL table with native geometry.

I have tested the connection via ogrinfo (based on gdal v 2.0.2) and it works:

ogrinfo -sql "select * from par_table where par_no = 6777887" "MSSQL:driver=FreeTDS;server=myserver,1433;uid=xx;pwd=xxx;database=mydb;trusted_connection=no;tables=par_table(geom)"

This is my layer section:

LAYER
  NAME par_direct
  GROUP "CRS"
  STATUS ON
  CONNECTIONTYPE ogr
  CONNECTION "MSSQL:driver=FreeTDS;server=myserver,1433;uid=xx;pwd=xxx;database=mydb;trusted_connection=no;tables=par_table(geom)"
  FILTER "WHERE date_dropped is null"
  TYPE POLYGON
  PROCESSING 'CLOSE_CONNECTION=DEFER'
  MAXSCALE 275000
  CLASS
    STYLE
      WIDTH 0.25
      OUTLINECOLOR 0 0 255
    END
  END
  METADATA
    "ows_title" "par_direct"
    "wms_group_title" "CRS"
    "wms_exceptions_format" "application/vnd.ogc.se_xml"
    "wms_abstract"    "This is the LINZ parcel layer in NZTM"
    "wms_keywordlist"    "LINZ,CRS"
    "wms_extent" "1114412.940 4793178.168 2089186.204 6190213.601"
    "gml_include_items"     "all"
    "gml_exclude_items"     ""
    "ows_include_items"     "all"
    "ows_exclude_items"     ""
  END
END

When I try a WMS getmap call I get this error message:

<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM "http://schemas.opengis.net/wms/1.1.1/exception_1_1_1.dtd"<http://schemas.opengis.net/wms/1.1.1/exception_1_1_1.dtd>>
<ServiceExceptionReport version="1.1.1">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named 'par_direct'.
msOGRFileOpen(): OGR error. Open failed for OGR connection in layer `par_direct'.
'MSSQL:driver=FreeTDS;server=db-agribase-p.asurequality.com' does not contain the 'database' portion
</ServiceException>
</ServiceExceptionReport>

Any clues?

Many thanks,

Robert L. Sanson



_______________________________________________

mapserver-users mailing list

mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org>

http://lists.osgeo.org/mailman/listinfo/mapserver-users


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