PRB: SQLExtendedFetch with SQL_FETCH_RELATIVE Fails to Return all Records after a Delete and Rollback using Keyset Cursors

ID: Q232991

The information in this article applies to:

SYMPTOMS

SQLExtendedFetch, with SQL_FETCH_RELATIVE, may not return all the records after a Delete operation followed by a rollback transaction for a Keyset cursor.


CAUSE

This is due to the design of the cursor worktable to be in it's own private transaction. Commits and rollbacks on the main transaction do not affect the worktable, and the row is deleted from the worktable prior to the rollback.


RESOLUTION

The records are not deleted because of the rollback. The same will not occur with a dynamic cursor. Due to the nature of a dynamic cursor, the row will be seen when fetching relative after the rollback.


STATUS

This behavior is by design.


MORE INFORMATION

Microsoft SQL Server 7.0 causes all the serverside cursors, except static, to be closed on a rollback. However SQL Server 7.0 Service Pack 1 (SP1) was modified to retain all the cursors even on a rollback.

Steps to Reproduce Behavior

From ISQL create the following table with the index and trigger for a delete operation: 

if exists (select * from sysobjects where id = object_id(N'dbo.ADOTest_Delete') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger dbo.ADOTest_Delete
GO

if exists (select * from sysobjects where id = object_id(N'dbo.ADOTest') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.ADOTest
GO

CREATE TABLE dbo.ADOTest (
	fld_id smallint NULL ,
	fld_dscr varchar (50) NULL ,
	fld_test smallint NULL 
) 
GO

 CREATE  UNIQUE  INDEX ADOTest1 ON dbo.ADOTest(fld_id) 
GO

 CREATE  UNIQUE  INDEX ADOTest2 ON dbo.ADOTest(fld_dscr) 
GO

GRANT  REFERENCES ,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON dbo.ADOTest  TO guest
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE TRIGGER ADOTest_Delete ON ADOTest 
FOR  DELETE 
AS
DECLARE @Fld_Test smallint
    Begin 
      select @Fld_Test = Fld_Test
      from Deleted
      if @Fld_Test = 0
        begin				/* If they're trying to delete a 
non-test entry, give them an error and rollback the transaction */ 
          RaisError ("You can not delete Non-Test records, this delete will not be performed",11,-1)
          RollBack Transaction
        End
   end
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

Insert into ADOTest values (1, "First Record",-1)
Insert into ADOTest values (2, "Second Record",0)
Insert into ADOTest values (3, "3rd Record",-1)
Insert into ADOTest values (4, "4th Record",0)

Information from ODBC Test


FullConnect() 
SQLSetConnectAttr: -- Set the SQL_COPT_SS_PRESERVE_CURSORS to ON
In:	ConnectionHandle = 0x00D42414, Attribute = SQL_COPT_SS_PRESERVE_CURSORS, ValuePtr = 1, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_ULONG=-18
	Return:	SQL_SUCCESS=0
SQLSetConnectAttr: -- Set AUTO_COMMIT to OFF.
In:	ConnectionHandle = 0x00D42414, Attribute = Conn: SQL_ATTR_AUTOCOMMIT=102, ValuePtr = SQL_AUTOCOMMIT_OFF=0, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16
	Return:	SQL_SUCCESS=0

SQLSetStmtAttr:  -- Set the rowset size for the cursor
In:	StatementHandle = 0x00D42AC4, Attribute = SQL_ROWSET_SIZE=9, ValuePtr = 100, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16
	Return:	SQL_SUCCESS=0
SQLSetStmtAttr: -- Set the cursor type to Keyset driven
In:	StatementHandle = 0x00D42AC4, Attribute = SQL_ATTR_CURSOR_TYPE=6, ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16
	Return:	SQL_SUCCESS=0
SQLSetStmtAttr: -- Set the concurrency to Optimistic based on rowversioning
In:	StatementHandle = 0x00D42AC4, Attribute = SQL_ATTR_CONCURRENCY=7, ValuePtr = SQL_CONCUR_ROWVER=3, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16
	Return:	SQL_SUCCESS=0
SQLExecDirect: --Execute the select stmt that fetches the row.
In:	hstmt = 0x00D42AC4, szSqlStr = "select * from adotest order by fld_id", cbSqlStr = -3
	Return:	SQL_SUCCESS=0
Bind Col All: -- Bind all the columns to their resp datatypes.
	icol, fCType, cbValueMax, pcbValue, rgbValue
	1, SQL_C_SSHORT=-15, 2, 0, 0
	2, SQL_C_CHAR=1, 51, 0, ""
	3, SQL_C_SSHORT=-15, 2, 0, 0
SQLExtendedFetch: -- Fetch the first set of records
In:	StatementHandle = 0x00D42AC4, FetchOrientation = SQL_FETCH_FIRST=2, FetchOffset = 1, RowCountPtr = 0x00157C70, RowStatusArray = 0x00157090
	Return:	SQL_SUCCESS=0
	Out:	*RowCountPtr = 4, *RowStatusArray = 0
SQLSetPos: -- Do the delete on the cursor that causes a rollback and generate the error (due to the code in the trigger).
In:	StatementHandle = 0x00D42AC4, RowNumber = 2, Operation = SQL_DELETE=3, LockType = SQL_LOCK_NO_CHANGE=0
	Return:	SQL_ERROR=-1
	stmt:	szSqlState = "37000", *pfNativeError = 50000, *pcbErrorMsg = 117
		szErrorMsg = "[Microsoft][ODBC SQL Server Driver][SQL Server]You can not delete Non-Test records, this delete will not be performed"
SQLExtendedFetch: -- Call with SQL_FETCH_RELATIVE. Record 2 is missing.
In:	StatementHandle = 0x00D42AC4, FetchOrientation = SQL_FETCH_RELATIVE=6, FetchOffset = 0, RowCountPtr = 0x00158A48, RowStatusArray = 0x00157090
	Return:	SQL_SUCCESS=0
	Out:	*RowCountPtr = 4, *RowStatusArray = 0
The Delete operation causes a rollback in the trigger. SQLExtendedFetch is called with SQL_FETCH_RELATIVE with a 0 offset and record number 2 vanishes from the cursor.


REFERENCES

For more information on Cursor behavior with rollbacklease, please refer to the following article in the Microsoft Knowledge Base:

Q199294 INF: Cursors Remain Open when CURSOR_CLOSE_ON_COMMIT Set OFF
Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference; topic: "Using ODBC API"; Microsoft Press; ISBN: 1572315164, February 1997

Additional query words: SQL_PRESERVE_CURSORS SQLEndTran SQLTransact CURSOR_CLOSE_ON_COMMIT 


Keywords          : kbODBC kbSQLServ700sp1 
Version           : WINDOWS:3.5,3.6,3.7; winnt:7.0
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: July 30, 1999