[gdal-dev] mssql append fails for non-geometry layers

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] mssql append fails for non-geometry layers

Martin Landa
Hi all,

let's assume sample data:

$ ogrinfo sample.gpkg

1: SOBR (Point)
2: VLA (None)

Append for geometry layers seems to work:

1) ogr2ogr -f MSSQLSpatial MSSQL:database=kn sample.gpkg sobr

->

1> select count(*) from sobr
2> go

-----------
       5437

2) ogr2ogr -f MSSQLSpatial -append MSSQL:database=kn sample.gpkg sobr

->

1> select count(*) from sobr
2> go

-----------
      10874

The same procedure fails for non-geometry layers:

1) ogr2ogr -f MSSQLSpatial MSSQL:database=kn sample.gpkg vla

->

1> select count(*) from vla
2> go

-----------
        111

2) ogr2ogr -f MSSQLSpatial -append MSSQL:database=kn sample.gpkg vla
ERROR 1: Error creating layer: [Microsoft][SQL Server Native Client 11.0][SQL Se
rver]There is already an object named 'vla' in the database. When using the over
write option and the layer doesn't contain geometry column, you might require to
 use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer de
leted before creating the new one.
ERROR 1: Terminating translation prematurely after failed
translation of layer VLA (use -skipfailures to skip errors)

OK, let's try with MSSQLSPATIAL_LIST_ALL_TABLES

3) ogr2ogr -f MSSQLSpatial -append --config
MSSQLSPATIAL_LIST_ALL_TABLES YES MSSQL:database=kn sample.gpkg vla

Same error:

ERROR 1: Error creating layer: [Microsoft][SQL Server Native Client 11.0][SQL Se
rver]There is already an object named 'vla' in the database. When using the over
write option and the layer doesn't contain geometry column, you might require to
 use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer de
leted before creating the new one.
ERROR 1: Terminating translation prematurely after failed
translation of layer VLA (use -skipfailures to skip errors)

Number of records unchanged:

1> select count(*) from vla
2> go

-----------
        111

It seems to me as a bug, or is there anything I miss? Thanks for
pointers, Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql append fails for non-geometry layers

Tamas Szekeres
Hi Martin,

For some reason the table hasn't been found and the driver wanted to create a new table. 
I've tried to reproduce this with a shapefile with no geometry uploaded to MSSQL, but I couldn't.
Can I have your sample data? Which GDAL version has been used?
You could also check what happens by disabling bulk insert by adding --config MSSQLSPATIAL_USE_BCP NO


Thanks,

Tamas


2018-05-03 19:31 GMT+02:00 Martin Landa <[hidden email]>:
Hi all,

let's assume sample data:

$ ogrinfo sample.gpkg

1: SOBR (Point)
2: VLA (None)

Append for geometry layers seems to work:

1) ogr2ogr -f MSSQLSpatial MSSQL:database=kn sample.gpkg sobr

->

1> select count(*) from sobr
2> go

-----------
       5437

2) ogr2ogr -f MSSQLSpatial -append MSSQL:database=kn sample.gpkg sobr

->

1> select count(*) from sobr
2> go

-----------
      10874

The same procedure fails for non-geometry layers:

1) ogr2ogr -f MSSQLSpatial MSSQL:database=kn sample.gpkg vla

->

1> select count(*) from vla
2> go

-----------
        111

2) ogr2ogr -f MSSQLSpatial -append MSSQL:database=kn sample.gpkg vla
ERROR 1: Error creating layer: [Microsoft][SQL Server Native Client 11.0][SQL Se
rver]There is already an object named 'vla' in the database. When using the over
write option and the layer doesn't contain geometry column, you might require to
 use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer de
leted before creating the new one.
ERROR 1: Terminating translation prematurely after failed
translation of layer VLA (use -skipfailures to skip errors)

OK, let's try with MSSQLSPATIAL_LIST_ALL_TABLES

3) ogr2ogr -f MSSQLSpatial -append --config
MSSQLSPATIAL_LIST_ALL_TABLES YES MSSQL:database=kn sample.gpkg vla

Same error:

ERROR 1: Error creating layer: [Microsoft][SQL Server Native Client 11.0][SQL Se
rver]There is already an object named 'vla' in the database. When using the over
write option and the layer doesn't contain geometry column, you might require to
 use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer de
leted before creating the new one.
ERROR 1: Terminating translation prematurely after failed
translation of layer VLA (use -skipfailures to skip errors)

Number of records unchanged:

1> select count(*) from vla
2> go

-----------
        111

It seems to me as a bug, or is there anything I miss? Thanks for
pointers, Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql append fails for non-geometry layers

Martin Landa
Hi Tamas,

2018-05-03 22:47 GMT+02:00 Tamas Szekeres <[hidden email]>:

first of all thanks for quick feedback!

> For some reason the table hasn't been found and the driver wanted to create
> a new table.

Right, seems to be like that.

> I've tried to reproduce this with a shapefile with no geometry uploaded to
> MSSQL, but I couldn't.
> Can I have your sample data? Which GDAL version has been used?

Here it is: http://geo102.fsv.cvut.cz/~landa/tmp/sample.gpkg

Using GDAL master + SQL Server 2014

BTW,

There are bunch of errors related to vla (non-geometry layer) when
importing sobr (geometry), but import itself doesn't fail.

$ ogr2ogr -f MSSQLSpatial -append --config
MSSQLSPATIAL_LIST_ALL_TABLES yes MSSQL:database=kn... sample.gpkg sobr
ERROR 1: No column definitions found for table 'dbo.vla', layer not usable.
ERROR 1: No column definitions found for table 'dbo.geometry_columns', layer not
 usable.
ERROR 1: No column definitions found for table 'dbo.spatial_ref_sys', layer not
usable.

> You could also check what happens by disabling bulk insert by adding
> --config MSSQLSPATIAL_USE_BCP NO

Same behaviour,

$ ogr2ogr -f MSSQLSpatial -append --config
MSSQLSPATIAL_LIST_ALL_TABLES yes --config MSSQLSPATIAL_USE_BCP NO
MSSQL:database=kn;... sample.gpkg vla
ERROR 1: No column definitions found for table 'dbo.vla', layer not usable.
ERROR 1: No column definitions found for table 'dbo.geometry_columns', layer not
 usable.
ERROR 1: No column definitions found for table 'dbo.spatial_ref_sys', layer not
usable.
ERROR 1: Error creating layer: [Microsoft][SQL Server Native Client 11.0][SQL Se
rver]There is already an object named 'vla' in the database. When using the over
write option and the layer doesn't contain geometry column, you might require to
 use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer de
leted before creating the new one.
ERROR 1: Terminating translation prematurely after failed
translation of layer VLA (use -skipfailures to skip errors)

Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: mssql append fails for non-geometry layers

Martin Landa
Hi,

2018-05-04 14:50 GMT+02:00 Martin Landa <[hidden email]>:
> ERROR 1: No column definitions found for table 'dbo.vla', layer not usable.

it's source of problems. Client fails to get column definitions [1].
Then GDAL tries to overwrite layer which fails [2].

Running SQL Server 2014.

Ma

[1] https://github.com/OSGeo/gdal/blob/master/gdal/port/cpl_odbc.cpp#L1488
[2] https://github.com/OSGeo/gdal/blob/master/gdal/ogr/ogrsf_frmts/mssqlspatial/ogrmssqlspatialtablelayer.cpp#L205

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev