table-valued inline functions in SpatialContextReader

[gBecker]

I have quite some problems with "table-valued function" in SQL Server that return tables which contains geometry-columns. I don't think that "table-valued Function" are supported by FDO or MapGuide OpenSource and therefore I do not need them to be accessed by MapGuide ( I use them alongside SQL Server feature CDC - ChangeDataCapture ).

The problem is that when I try to create a FeatureSource in Maestro or Infrastructure MapServer 2013 I get an error and therefore cannot save the FeatureSource. I think the root problem occurs when trying to access the spatial context. The error says that no parameters where send to the Table-Valued Funtion xxx. So it seems that MapGuide/FDO try to access these function to gather information even though these funtions are not supported. When preview the FeatureSource I get this message:

An exception occurred in FDO component. Error occurred in Feature Source ( ... ): RDBMS: [Microsoft][SQL Server Native Client 10.0][SQL Server] An insufficient number of arguments were supplied for the procedure or function 'FFM_Fes.cdc.fn_cdc_get_all_changes_dbo_fes_Objects

I have turned on trace logging for the FDO SQL Server Provider! Like I'm assuming the table-valued function is queried for retrieving the sptial reference system. This throws the error since there are now parameters send to the function:

select top 1 [Geometrie].STSrid as srid from "FFM_Fes"."cdc"."fn_cdc_get_all_changes_dbo_fes_Objects"

Just before a query was executed which returns a table with all objects in the database that contains a geometry column. The table also has a column "object_type" that determine the type of the returned objects. The table-valued function is of type "if" which means it is a "SQL inline table-valued function". When the FDO SQL Server Provider loops over this table to determine the SRID for every object like above the error is thrown.

Solution

I think the proper way would be that FDO/MapGuide recognize these functions and pass by while gathering the required data. It would be easy to extend the existing query in ​http://trac.osgeo.org/fdo/browser/trunk/Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/Rd/SpatialContextReader.cpp line 284 with a filter in WHERE-clause to not return table-valued inline functions (lower(a.type) <> 'if') line 303 :

select 0 as srid, 
 c.name collate latin1_general_bin as geomusername,
 a.name collate latin1_general_bin as geomtablename,
 b.name as geomcolumnname,
 e.bounding_box_xmin as xmin, 
 e.bounding_box_ymin as ymin, 
 e.bounding_box_xmax as xmax, 
 e.bounding_box_ymax as ymax, 
 f.name as type, 
 lower(a.type) as object_type, 
 g.name as indexname 
 from "FFM_Fes".sys.objects  a
  INNER JOIN "FFM_Fes".sys.columns b ON ( a.object_id = b.object_id ) 
  INNER JOIN "FFM_Fes".sys.schemas c ON ( a.schema_id = c.schema_id ) 
  LEFT OUTER JOIN "FFM_Fes".sys.index_columns d ON ( a.object_id = d.object_id and b.column_id = d.column_id ) 
  LEFT OUTER JOIN "FFM_Fes".sys.spatial_index_tessellations e ON ( d.object_id = e.object_id and d.index_id = e.index_id ) 
  INNER JOIN "FFM_Fes".sys.types  f ON ( b.user_type_id = f.user_type_id ) 
  LEFT OUTER JOIN "FFM_Fes".sys.indexes g ON ( a.object_id = g.object_id and d.index_id = g.index_id ) 
 where (f.name in ( 'geometry','geography' ) or e.object_id is not null) and lower(a.type) <> 'if'  
 order by c.name collate latin1_general_bin asc, a.name collate latin1_general_bin asc, b.column_id asc

I don't know if there are other places in code where to change this query.

[gBecker]

Duplicate of 862: ​http://trac.osgeo.org/fdo/ticket/862