[fdo-trac] #507: SQLServerSpatial: infinite loop when insert to table with trigger.

[FDO]

#507: SQLServerSpatial: infinite loop when insert to table with trigger.
--------------------------------------------------------------------------------+
 Reporter:  brentrobinson                                                       |         Owner:  brentrobinson
     Type:  defect                                                              |        Status:  closed       
 Priority:  major                                                               |     Milestone:  3.5.0        
Component:  SQLServer Spatial                                                   |       Version:  3.5.0        
 Severity:  2                                                                   |    Resolution:  fixed        
 Keywords:  SQL Server 2008 SQLServerSpatial insert trigger hang infinite loop  |   External_id:               
--------------------------------------------------------------------------------+
Changes (by brentrobinson):

  * status:  assigned => closed
  * resolution:  => fixed

Comment:

 Revision: 4552
 Author: brentrobinson
 Date: 11:02:53 AM, Thursday, April 16, 2009
 Message:
 Ticket#507: Fixed infinite loop that occurs when inserting into a table
 fires a trigger that inserts into another table. Also fixed a problem
 where resulting FdoIFeatureReader was retrieving the autoincremented
 column value for the other table.

 The hanging problem was resolved by modifying odbcdr_execute to call
 SQLMoreResults() after executing the insert statement.

 The wrong autoincremented value problem was solved by using
 SCOPE_IDENTITY(), instead of @@IDENTITY, to retrieve the value. @@IDENTITY
 gets the last autoincremented value for the current session, so it ends up
 getting the value for the row inserted by the trigger. SCOPE_IDENTITY() is
 limited to the current scope or batch. To get SCOPE_IDENTITY() to work, it
 had to be used in the same batch as the Table A insert statement. This was
 done by the following changes:

 - in odbcdr_sql(), detect whether the sql statement is an insert. If it
 is, tack on a "; select SCOPE_IDENTITY()" before preparing the statement.

 - in odbcdr_execute(), retrieve the results of the select, when the
 statement is above insert-select batch of statements. When the statement
 batch as executed, 2 results sets (one for the insert and one for the
 select) are set up. SQLMoreResults() is now use to navigate to the
 select's result set so the autoincremented value can be retrieved. The
 value is stored in the odbcdr context.

 - in odbcdr_get_gen_id(), changed the global value retrieval to get the
 value from the odbcdr context instead of doing a select.
 ----
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/context.h
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/crt_cursor.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/est_cursor.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/execute.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/fre_cursor.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/get_gen_id.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/rdbi_init.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/sql.c
 Modified :
 /trunk/Providers/GenericRdbms/Src/SQLServerSpatial/ODBCDriver/structs.h
 Modified :
 /trunk/Providers/GenericRdbms/Src/UnitTest/SQLServerSpatial/SqlServerFdoInsertTest.cpp
 Modified :
 /trunk/Providers/GenericRdbms/Src/UnitTest/SQLServerSpatial/SqlServerFdoInsertTest.h

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/507#comment:2>
FDO <http://fdo.osgeo.org/>
Feature Data Objects