Microsoft KB Archive/919863

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

Article ID: 919863

Article Last Modified on 6/14/2006

-

APPLIES TO


 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



Bug #: 296413 (SQLBUDT)



SYMPTOMS
Consider the following scenario. You develop an ODBC application that uses the SQLSetPos function in the Microsoft SQL Server ODBC driver API for an updatable join. You can update one table at a time by setting the appropriate column status values to SQL_COLUMN_IGNORE. A column is updated first in one table that has a clustered index on it. A second column in the other table is updated. In this scenario, the second update will fail. However, the SQLSetPos function returns SQL_SUCCESS. The SQLGetDiagRec function returns no error information. The row status array element is SQL_ROW_ERROR. This behavior is the only sign that the update failed.



CAUSE
This issue occurs because the server-side cursor does not enable the positioned updates when the delete or insert operation occurs.



WORKAROUND
To work around this issue, you can use the row status array to determine that an error occurred. The row status array returns SQL_ROW_ERROR when an error occurs.



STATUS
This behavior is by design.



Steps to reproduce the behavior
 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.  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  Create a data source name (DSN) to connect to the newly created database. In this example, the DSN name is ReproDB.  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:
 * 1) include &quot;stdafx.h&quot;
 * 2) include &quot;windows.h&quot;
 * 3) include &quot;sql.h&quot;
 * 4) include &quot;sqlext.h&quot;

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,&quot;Invalid handle!\n&quot;); return; }

while (SQLGetDiagRec(hType, hHandle, ++iRec, (SQLWCHAR *)szState, &iError, (SQLWCHAR *)szMessage, (SQLSMALLINT)(sizeof(szMessage) / sizeof(TCHAR)), (SQLSMALLINT *)NULL) == SQL_SUCCESS) {       fprintf(stderr,TEXT(&quot;[%5.5s] %s (%d)\n&quot;),szState,szMessage,iError); }

if (iRec == 1) {       fprintf(stdout, &quot;No errors found\n&quot;); }

}

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 *)&quot;DSN=ReproDB&quot;,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 *)&quot;select * from test1,test2 where test1.col1=test2.col1&quot;,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, &quot;New Value&quot;); // 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(&quot;Row status is: SQL_ROW_ERROR\n&quot;); else if (SQL_ROW_UPDATED == rgfRowStat) printf(&quot;Row status is: SQL_ROW_UPDATED\n&quot;); else printf(&quot;Row status is: %i\n&quot;, rgfRowStat);

strcpy((CHAR*) Col2, &quot;New Test&quot;); 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(&quot;Row status is: SQL_ROW_ERROR\n&quot;); else if (SQL_ROW_UPDATED == rgfRowStat) printf(&quot;Row status is: SQL_ROW_UPDATED\n&quot;); else printf(&quot;Row status is: %i\n&quot;, 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; }  Build the project.</li> Run the newly created application at the command prompt.</li></ol>

In this example, the second update fails. However, the SQLSetPos function returns SQL_SUCCESS. This application uses the row status array to catch the error. When error occurs, a row status of SQL_ROW_ERROR is returned.

<div class="references_section">