The SQLSetPos function in the SQL Server ODBC driver returns SQL_SUCCESS when you use an updatable join and an update fails (919863)

MORE INFORMATION

Steps to reproduce the behavior

1. Connect to an instance of SQL Server by using SQL Query Analyzer in SQL Server 2000 or SQL Server Management Studio in Microsoft SQL Server 2005.
2. Run the following Transact-SQL statements in a new query:

   use master
   if exists(select * from sys.databases where name='Repro')
   	drop database Repro
   else
   	create database Repro
   go
   
   use Repro
   go
   
   if exists(select * from sys.tables where name='test1')
   drop table test1
   go
   
   if exists(select * from sys.tables where name='test2')
   drop table test2
   go
   
   Create table test1 (col1 int constraint pk_test1  PRIMARY KEY NONCLUSTERED 
   , col2 char(10) )
   go
   Create table test2 (col1 int constraint pk_test2 PRIMARY KEY NONCLUSTERED
   , col2 int
   , col3 char(10) constraint u_test2 UNIQUE CLUSTERED)
   go
   
   insert into test1 values (1, 'test1')
   insert into test1 values (2, 'test2')
   
   insert into test2 values (1, 1, 'value1')
   insert into test2 values (2, 2, 'value2')
   
   select * from test1
   select * from test2
   
   select * from	test1,test2 where test1.col1=test2.col1
   

3. Create a data source name (DSN) to connect to the newly created database. In this example, the DSN name is ReproDB.
4. Create a new Microsoft Visual C++ Win32 console application by using Microsoft Visual Studio, and then paste the following code example into the source file:

   #include "stdafx.h"
   #include "windows.h"
   #include "sql.h"
   #include "sqlext.h"
   
   void HandleError(SQLHANDLE	hHandle, SQLSMALLINT hType, RETCODE RetCode)
   {
   	SQLSMALLINT	iRec = 0;
   	SQLINTEGER	iError;
   	TCHAR		szMessage[1000];
   	TCHAR		szState[SQL_SQLSTATE_SIZE+1];
   
   
   	if (RetCode == SQL_INVALID_HANDLE)
   	{
   		fprintf(stderr,"Invalid handle!\n");
   		return;
   	}
   
   
   	while (SQLGetDiagRec(hType,
   						 hHandle,
   						 ++iRec,
   						 (SQLWCHAR *)szState,
   						 &iError,
   						 (SQLWCHAR *)szMessage,
   						 (SQLSMALLINT)(sizeof(szMessage) / sizeof(TCHAR)),
   						 (SQLSMALLINT *)NULL) == SQL_SUCCESS)
   	{
   		fprintf(stderr,TEXT("[%5.5s] %s (%d)\n"),szState,szMessage,iError);
   	}
   
   	if (iRec == 1)
   	{
   		fprintf(stdout, "No errors found\n");
   	}
   
   }
   
   	void RunTest(void)
   	{
   	   HENV       henv;
   	   HDBC       hdbc1;
   	   HSTMT      hstmt1;
   	   RETCODE    retcode;
   	   HWND       hWnd = NULL;
   	   SWORD        swStrLen;
   	   SQLWCHAR       Col2[50], Info[50], szConnStrOut[30];
   	   int       try1=0,try2=0,try3=0;
   	   SDWORD    cbCol11 = 4, cbCol12=10, cbCol21=4, cbCol22=4, cbCol23=10;
   	   UDWORD r=0;
   	   UWORD rgfRowStat;
   
   	// Allocate the ODBC Environment handle.
   	   retcode = SQLAllocEnv (&henv);
   	
   	// Allocate the Connection handle.
   	   retcode = SQLAllocConnect(henv, &hdbc1);
   	
   	// Set the SQLDriverConnect that will generate a dialog prompt for the data source.
   	   retcode = SQLDriverConnect(hdbc1,hWnd,(SQLWCHAR *)"DSN=ReproDB",12,
   	      szConnStrOut,30, &swStrLen,SQL_DRIVER_NOPROMPT);
   	
   	// Allocate the statement handle.
   	   retcode=SQLAllocStmt(hdbc1, &hstmt1);
   	
   	// Set the statement options.
   	   retcode = SQLSetStmtOption(hstmt1, SQL_CONCURRENCY, SQL_CONCUR_VALUES);
   	   retcode = SQLSetStmtOption(hstmt1, SQL_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN);
   	   retcode = SQLSetStmtOption(hstmt1, SQL_ROWSET_SIZE, 1);
   	
   	// Perform the join, and bind the columns in the result set.
   	   retcode = SQLExecDirect(hstmt1,(SQLWCHAR *)"select * from test1,test2 where test1.col1=test2.col1",SQL_NTS);
   	   if (retcode != SQL_SUCCESS)
   			HandleError(hstmt1, SQL_HANDLE_STMT, retcode);
   
   	   retcode = SQLBindCol(hstmt1, 1, SQL_C_LONG, &try1, sizeof(try1), &cbCol11);
   	   retcode = SQLBindCol(hstmt1, 2, SQL_C_CHAR, Col2,  sizeof(Col2), &cbCol12);
   	   retcode = SQLBindCol(hstmt1, 3, SQL_C_LONG, &try2, sizeof(try2), &cbCol21);
   	   retcode = SQLBindCol(hstmt1, 4, SQL_C_LONG, &try3, sizeof(try3), &cbCol22);
   	   retcode = SQLBindCol(hstmt1, 5, SQL_C_CHAR, Info,  sizeof(Info), &cbCol23);
   	
   	// Fetch the result set, and position the cursor.
   	   retcode = SQLExtendedFetch(hstmt1, SQL_FETCH_FIRST, 1, &r, &rgfRowStat);
   	
   	   cbCol11=SQL_COLUMN_IGNORE;
   	   cbCol12=SQL_COLUMN_IGNORE;
   	   cbCol21=SQL_COLUMN_IGNORE;
   	   cbCol22=SQL_COLUMN_IGNORE;
   	   strcpy((CHAR*) Info, "New Value");
   	
   	// Update the values. Updating the unique constraint column in the correct table
   	// succeeds. However, the row is actually deleted or inserted.
   	   retcode = SQLSetPos(hstmt1, 0, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
   
   	   if (retcode != SQL_SUCCESS)
   			HandleError(hstmt1, SQL_HANDLE_STMT, retcode);
   
   	   	if (SQL_ROW_ERROR == rgfRowStat)
   			printf("Row status is: SQL_ROW_ERROR\n");
   		else if (SQL_ROW_UPDATED == rgfRowStat)
   			printf("Row status is: SQL_ROW_UPDATED\n");
   		else
   			printf("Row status is: %i\n", rgfRowStat); 
   	   
   
   	   strcpy((CHAR*) Col2, "New Test");
          cbCol12=SQL_NTS;
   	   cbCol23=SQL_COLUMN_IGNORE;
   
   	   // The second update fails, but this information is not returned.
   	   retcode = SQLSetPos(hstmt1, 0, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
   	   //if (retcode != SQL_SUCCESS)
   			HandleError(hstmt1, SQL_HANDLE_STMT, retcode);
   	
   		if (SQL_ROW_ERROR == rgfRowStat)
   			printf("Row status is: SQL_ROW_ERROR\n");
   		else if (SQL_ROW_UPDATED == rgfRowStat)
   			printf("Row status is: SQL_ROW_UPDATED\n");
   		else
   			printf("Row status is: %i\n", rgfRowStat); 
   	
   	// Free the handles.
   	   retcode = SQLFreeStmt(hstmt1, SQL_DROP);
   	   retcode = SQLDisconnect(hdbc1);
   	   retcode = SQLFreeConnect (hdbc1);
   	   retcode = SQLFreeEnv(henv);
   	}
   
   
   int _tmain(int argc, _TCHAR* argv[])
   {
   	RunTest();
   	return 0;
   }
   
   

5. Build the project.
6. Run the newly created application at the command prompt.