[Fdo-trac] [fdo-trac] #861: table-valued inline functions in SpatialContextReader

[FDO]

#861: table-valued inline functions in SpatialContextReader
---------------------------------+------------------------------------------
   Reporter:  gBecker            |       Owner:  danstoica
       Type:  defect             |      Status:  new      
   Priority:  major              |   Milestone:  3.8.0    
  Component:  SQLServer Spatial  |     Version:  3.7.0    
   Severity:  3                  |    Keywords:           
External_id:                     |  
---------------------------------+------------------------------------------
 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 ).[[BR]]


 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:[[BR]]


 ''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'''[[BR]]

 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:[[BR]]

 {{{
 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.[[BR]]

 '''Solution'''[[BR]]

 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 :[[BR]]


 {{{
 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.

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/861>
FDO <http://fdo.osgeo.org/>
Feature Data Objects