0 rows when querying an MSSQL server through ogr_fdw

[grav]

I am trying to query an MSSQL server through ogr_fdw from a Linux machine. I can connect to the machine and issue sql queries, both through sqlcmd and ogrinfo.

But if I create a foreign server and query through psql, I get 0 rows.

The test script is here: https://gist.github.com/grav/859756dae0f5b6bed6d41051c6fe2b2f

The output of the script is below. One weird thing is this line from ogrinfo: OGR_MSSQLSpatial: Using column � as FID for table matrikel.

I am also not able to get features back from ogrinfo using the -fid option.

Also, odr_fdw works fine when connecting from a Windows machine to the same MSSQL server.

So it might not be an odr_fdw issue.

Any thoughts? :-)

$ source test.sh 

------------ sqlcmd: select from ms sql
id          guid                                                                                                                                                                                                                                                           ejerlav_id           mat_no                                   geom
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------------------------- ----
          1                                                         rows                                                                                                                                                                                                                       NULL NULL                                     0xE8

(1 rows affected)

------------ ogrinfo: try parsing the spatial data
OGR_MSSQLSpatial: EstablishSession(Connection:"driver=ODBC Driver 13 for SQL Server;server=geotesterapache.cloudapp.net;database=skm_test_db;UID=sa;PWD=xxx;")
ODBC: SQLDriverConnect(driver=ODBC Driver 13 for SQL Server;server=geotesterapache.cloudapp.net;database=skm_test_db;UID=sa;PWD=xxx;)
OGR_MSSQLSpatial: Using column � as FID for table matrikel.
OGR: OGROpen(MSSQL:driver=ODBC Driver 13 for SQL Server;server=geotesterapache.cloudapp.net;database=skm_test_db;UID=sa;PWD=xxx;tables=matrikel/0x1051ae0) succeeded as MSSQLSpatial.
INFO: Open of `MSSQL:driver=ODBC Driver 13 for SQL Server;server=geotesterapache.cloudapp.net;database=skm_test_db;UID=sa;PWD=xxx;tables=matrikel'
      using driver `MSSQLSpatial' successful.
MSSQLSpatial: ExecuteSQL(select top 1 * from matrikel) called.
OGR_MSSQLSpatial: Table SELECT has no identified FID column.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
id: Integer (10.0)
guid: String (254.0)
ejerlav_id: String (19.0)
mat_no: String (40.0)
geom: String (0.0)
OGR_MSSQLSpatial: Recreating statement.
OGRFeature(SELECT):0
  id (Integer) = 1
  guid (String) = 
  ejerlav_id (String) = (null)
  mat_no (String) = (null)
  geom (String) = E864000001041F000000F0A7C6CB76252541E5D0220BB07A57410AD7A37033242541A8C64B8F867A57413D0AD7E33A242541666666B63F7A5741AC1C5AA48C22254108AC1C9A377A5741A245B6B38D222541105839F4377A5741448B6C2796222541105839843C7A57418FC2F568A02225419EEFA796427A5741D9CEF793A8222541D9CEF75B467A574104560EEDAC222541D578E9D6497A5741C520B032B022254191ED7C5F4C7A57411F85EB51B22225417F6ABC244D7A574108AC1CDAB6222541B4C876664F7A57412B871619B72225414260E578507A57411F85EB91BB2225415A643B17577A574108AC1C9ABB22254106819583597A5741DF4F8D57B02225414C378941637A5741D9CEF7D3A922254152B81E7D6A7A57416891ED3CA5222541986E12DB6D7A57411B2FDDA4A22225410E2DB22D707A5741F2D24D229E2225418B6CE7AB757A5741E5D0225B95222541BA490CCA7D7A574177BE9F9A8F222541986E1213827A57415A643B9F8922254121B07248857A57415A643B1F8E22254148E17AB4877A57411D5A643B9522254177BE9F3A927A5741022B875696222541643BDF8F957A5741FA7E6A7C992225418195438B977A5741CDCCCCCC992225410E2DB24DA17A5741B4C876BE8D222541894160A5A57A5741F4FDD4788B22254104560E2DA77A5741F0A7C6CB76252541E5D0220BB07A574101000000020000000002000000FFFFFFFF0000000006000000000000000003

OGR_MSSQLSpatial: 2 features read on layer 'SELECT'.
OGR: GetLayerCount() = 1

ODBC: SQLDisconnect()

------------ ogr_fdw: try querying through postgresql
 * Starting PostgreSQL 9.5 database server                                                                                 [ OK ] 
CREATE DATABASE
CREATE EXTENSION
CREATE SERVER
CREATE FOREIGN TABLE
 fid | geom | id | guid | ejerlav_id | mat_no 
-----+------+----+------+------------+--------
(0 rows)

[robe2]

1) Have you tried using GDAL ODBC instead of MSSpatial? 2) Which GDAL version did you compile with? There was an issue with MSSPatial driver I reported a couple of months ago with how it was handling non-integer primary keys. This got fixed I think in GDAL 2.0.2

https://trac.osgeo.org/gdal/ticket/6235

3) As I recall at least on Linux (might be because Linux tends to standardize on older GDAL) I saw major issues with how it handled compound primary keys which didn't happen on windows.

[pramsey]

No answer, nothing to do.