SQLServerSpatial: infinite loop when insert to table with trigger.

[brentrobinson]

An infinite loop occurs in the following situation:

• datastore has two tables, each with an autoincremented primary key column

• Table A has a trigger that fires on row insertion. The trigger adds a row to Table B

• The SQLServerSpatial provider adds a feature to Table A via FdoIInsert.

The SQLServerSpatial provider then hangs with an infinite loop in the SQL Server driver (below the ODBC interface).

[brentrobinson]

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