Truncation of integer ID fields when connecting to SQL Server database via ODBC connection

[kates]

If you have a layer connecting to a SQL Server database via ODBC, MapServer will not use the ID field correctly (set in ODBC_OGR_FID) if it is an integer field and has values of more then 999. The layer is drawn correctly, but GetFeatureInfo may not return the correct rows. ID <= 999 are handled correctly and GetFeatureInfo returns the correct rows.

Values > 999 are truncated to the first character (i.e. 1234 becomes 1, 2567 becomes 2 etc), which means you do not get the correct rows returned and/or you get no data back if the truncated IDs don’t match a row.

Please see attached files for mapfile example of how I am setting up the connection & the view from which it gets the data. In the SQL server database, UID is an integer primary key. (If there is a better way of connecting to a SQL Server database, I would appreciate you telling me)

Sending a GetFeatureInfo request results in queries like the following (found via Profiler) SELECT * FROM vieWSRoute WHERE XMAX > 310490.00000000 AND XMIN < 310590.00000000 AND YMAX > 230798.00000000 AND YMIN < 230898.00000000 which returns rows with IDs of: 1396, 1395, 1394, 1864 However following queries are all SELECT * FROM vieWSRoute WHERE UID = 1, instead of the actual ID values.

Queries which return IDs of 2xxx, result in SELECT * FROM vieWSRoute WHERE UID = 2

Queries which return IDs of less than 999 result in the correct ID sent in the following queries i.e SELECT * FROM vieWSRoute WHERE UID = 789;SELECT * FROM vieWSRoute WHERE UID = 23; etc.

You can see this on our test website at: ​http://camsweb.no-ip.org/map.aspx?act=Walking If you click on the paths in & to the north of Brecon, you get the correct information back, as you move further east either no information is returned (as we do not have a row with ID=1 in the database, or UID=2 is returned e.g. all the paths around Talgarth have id's in the 2000s, but ID=2 is always sent)

[kates]

mapfile with example layer & connection info (username/password redacted)

[assefa]

Is it possible to provide DB backup that I can upload to sql server. I have sql server 2005 setup. If that was possible, It will help me to replicate the problem described. You can either attach it to the bug or send it to me directly at yassefa@….

[assefa]

I was able to setup the db/map and try to reproduce the problem. I used a recent build of MapSrver and gdal/ogr (from svn) and I would consistently get the correct output when doing a getfeatureinfo I also used an ms4w build (​http://www.maptools.org/ms4w/index.phtml?page=downloads.html) and was also getting the correct results. Both these builds use a version of gdal/ogr which is >= 1.6 and Mapserver >=5.4

It is specified in the bug the your are using MapServer 5.2, do you use the ms4w package on windows, or any other package (or build binaries yourself)? If it is an option, I would suggest upgrading to more recent version of MapServer/OGR and do the tests. I am attaching a small OpenLayers test file and my ma file, that allows to display the wms layer (the paths) and click on them to generate and get the result back from GetFeatureInfo request. Using this test I was getting back correctly UID > 999 (note that I labeled the paths with their UID to verify consistency)

Let us know.

[assefa]

open layer sample/getfeatureinfo

[assefa]

closing this. There hasn't been a comment on this and was working for me with recent versions of Mapserver/OGR.