pramsey
commented
2 years ago Possible that the latest fixes also fix your problem, as they were a result of Pg14 changes to update/delete support.
Closed Jnb2387 closed 1 year ago
pramsey
commented
2 years ago Possible that the latest fixes also fix your problem, as they were a result of Pg14 changes to update/delete support.
I am having the same issues as #171 and I am wondering if updating or deleting be any different for Microsoft SQL Azure 12? I am able to connect and read the tables just fine, but I am getting 'Foreign table does not allow updates' for updates and then "cannot find 'fid' column in table" for delete. I do have an integer primary key in the table and is read as the fid. I set the client to debug and do not see any errors
UPDATE dforce_mssql_fdw_test.fed_lands_test SET "type"='National Wilderness Area' WHERE fid=7223 bind to
ogrAddForeignUpdateTargets
GDAL config option 'CPL_DEBUG' set to 'ON'
GDAL None [0] MSSQLSpatial: Use COPY/BCP: 0
GDAL None [0] ODBC: SQLDriverConnect(DRIVER={SQL Server};server=server,1433;database=dbname;UID=user;PWD=password;)
GDAL None [0] GDAL: GDALOpen(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0) succeeds as MSSQLSpatial.
GDAL None [0] OGR_MSSQLSpatial: Using column ogr_fid as FID for table fed_lands_test.
ogrReadColumnData matched 1 FID, 1 GEOM, 7 FIELDS out of 9 PGSQL COLUMNS
ogrOperatorIsSupported got operator '='
OGR SQL: ("ogr_fid" = 7223)
GDAL None [0] ODBC: SQLDisconnect()
GDAL None [0] GDAL: GDALClose(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0)
ogrIsForeignRelUpdatable
GDAL config option 'CPL_DEBUG' set to 'ON'
GDAL None [0] MSSQLSpatial: Use COPY/BCP: 0
GDAL None [0] ODBC: SQLDriverConnect(DRIVER={SQL Server};server=server,1433;database=dbname;UID=user;PWD=password;)
GDAL None [0] GDAL: GDALOpen(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0) succeeds as MSSQLSpatial.
GDAL None [0] ODBC: SQLDisconnect()
GDAL None [0] GDAL: GDALClose(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0)
I am able to insert new rows without error.
I am using Postgres 14 Postgis 3.2.1 OGR_FDW 1.1 and GDAL 3.4.2
MSSQL Table CREATE TABLE [dbo].[fed_lands_test]( [ogr_fid] [int] IDENTITY(1,1) NOT NULL, [sp_geography] [geography] NULL, [name] nvarchar NULL, [type] nvarchar NULL, [type_abbrev] nvarchar NULL, [website] nvarchar NULL, [fed_land_id] [int] NULL, [layer_id] [int] NULL, [area_sq_mi] [float] NULL, CONSTRAINT [PK_fed_lands_test] PRIMARY KEY CLUSTERED ( [ogr_fid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
Postgres Foreign table CREATE FOREIGN TABLE dforce_mssql_fdw.fed_lands_test ( fid int8 NULL, sp_geography public.geometry(geometry, 4326) NULL, "name" varchar(250) NULL, "type" varchar(50) NULL, type_abbrev varchar(10) NULL, website varchar(250) NULL, fed_land_id int4 NULL, layer_id int4 NULL, area_sq_mi float8 NULL ) SERVER dforce_mssql_test OPTIONS (layer 'fed_lands_test', updateable 'true');