Unable to update MS SQL Server tables with geometry

[Davis-Klavins]

Read through similar ticket, but can't get to work updating MS SQL Server 2016 tables that have geometry column. Without it, works fine both with FreeTDS driver on Ubuntu 24.04, PostgreSQL 16 and even ODBC Driver 17 for SQL Server on Centos 7, PostgreSQL 12.

On table created in MS SQL Server with

CREATE TABLE dbo.test (
  ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,CODE VARCHAR(14) NOT NULL
  ,x DECIMAL(9, 3) NULL
  ,y DECIMAL(9, 3) NULL
  );

query

UPDATE schema_name.dbo_test
SET x = 1;

in PostgreSQL works fine.

But on table created in MS SQL Server with

CREATE TABLE dbo.test_geom (
  ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,CODE VARCHAR(14) NOT NULL
  ,geom geometry NOT NULL
  ,x DECIMAL(9, 3) NULL
  ,y DECIMAL(9, 3) NULL
  );

query

UPDATE schema_name.dbo_test_geom
SET x = 1;

in PostgreSQL returns error:

ERROR:  foreign table "dbo_test_geom" does not allow updates 

SQL state: 55000

[pramsey]

What if you use FID as the name of your PK?

[Davis-Klavins]

Tested, unfortunately the same.

[pramsey]

Unless you can get a debugger on it and step through, this ticket will probably languish, as I do not have an environment to test against. It's possible if @robe2 can replicate your situation that she could debug it.

[pramsey]

(Actually there is one thing I can do which is verify that update works on a loop-back connection to postgres, but it probably does. What GDAL version do you have, that's another variable that might have changed over time.)

[Davis-Klavins]

On Ubuntu where OGR FDW was installed via apt (postgresql-16-ogr-fdw), SELECT ogr_fdw_version(); reports GDAL version 3.8.4.
On Centos where it was compiled, respective gdal-config --version reports GDAL version 3.0.4.

[robe2]

Sorry have my github notifications turned off so just seeing this now. @Davis-Klavins which driver are you using? The ODBC or MSSpatial? From my note I have this:

CREATE SERVER mssql_test FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'MSSQL:server=myserver,1433;database=mydb;UID=user;PWD=password;driver=FreeTDS;Tables=Clientdb,Table2,Table3', format 'MSSQLSpatial');

I do have an old CentOS with ogr_fdw and TDS I can try that is running SQL Server 2016 or 2017 on Linux, can't recall.

But as I recall I think I had issues updating using the ODBC driver (could only use the MSSQLSpatial) I think because the ODBC one creates a fake FID that doesn't exist in SQL Server so can't use it as a key.

[robe2]

@Davis-Klavins ,

I'm thinking this might be ODBC driver specific. I checked my current use of MSSQLSpatial on my CentOS box, and I'm using ODBC Driver 13 for SQL Server that was provided by Microsoft rather than FreeTDS against a SQL Server on the same box running

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64)   Jul 30 2023 15:31:58   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

On my sql server database, I created the table as follows and added a record:

CREATE TABLE dbo.test_geom (
  ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,CODE VARCHAR(14) NOT NULL
  ,geom geometry NOT NULL
  ,x DECIMAL(9, 3) NULL
  ,y DECIMAL(9, 3) NULL
  );

  INSERT INTO dbo.test_geom(code, geom, x, y)
  VALUES ('test', geometry::STGeomFromText('POINT(1 5)', 4326), 2, 5);

On PostgreSQL side I did this:

Syntax of my connection looks like this:

CREATE SERVER mssql_test FOREIGN DATA WRAPPER ogr_fdw OPTIONS ( datasource 'MSSQL:server=tcp:localhost,1433;database=somedb;UID=someuser;PWD=somepassword;driver=ODBC Driver 13 for SQL Server;Tables=dbo.test_geom', format 'MSSQLSpatial');

IMPORT FOREIGN SCHEMA ogr_all FROM SERver mssql_test INTO public;

On PostgreSQL, this works just fine for me:

UPDATE public.test_geom
SET x = 1;

and I confirmed I can see the changed data on both the PostgreSQL side and SQL Server side.