INF: How to Use SQLSetPos in Updatable Joins w/ SQL Server

The information in this article applies to:

• Microsoft Open Database Connectivity, version 2.50
• The Microsoft Foundation Classes (MFC) included with:
• Microsoft Visual C++ for Windows, version 1.52
• Microsoft Visual C++, 32-bit Edition, versions 2,0, 2.1, 2.2, 4.0

SUMMARY

This article discusses how to use an updatable join using SQLSetPos and the SQL Server ODBC driver version 2.50.0121.

MORE INFORMATION

An updatable join is what you need when you intend to either update, insert, or delete data in the underlying tables on which the join is based. The most frequently encountered error with the SQL Server driver when you use an updatable join is:

   Invalid column name <column-name>

It is worth noting that you can only update one table of a join in any single SQLSetPos operation. Consider a two-table updatable join: If you want to update one or more columns in the first table, you must set pcbValue for all the columns from the second table to SQL_IGNORE, and do the same for any columns in the first table that you do not want to be updated.

Similarly, if you want to update one or more columns in the second table, you must set pcbValue for all the columns from the first table to SQL_IGNORE, and do the same for any column in the second table that you do not want to be updated. If you want to update columns in both tables, you have to use SQLSetPos twice, wrapping in a transaction if needed.

The following C code illustrates how to use an updatable join:

Code Sample

/* Before running the following code do this on SQL Server:

void RunTest(void)

   HENV       henv;
   HDBC       hdbc1;
   HSTMT      hstmt1;
   RETCODE    retcode;
   extern HWND       hWnd;
   SWORD        swStrLen;
   UCHAR       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);

// SQLDriverConnect that will generate a dialog prompt for the datasource
   retcode = SQLDriverConnect(hdbc1,hWnd,(UCHAR *)"",12,
      szConnStrOut,30, &swStrLen,SQL_DRIVER_PROMPT);

// Allocate the statement handle
   retcode=SQLAllocStmt(hdbc1, &hstmt1);

// Set the statement options
   retcode = SQLSetStmtOption(hstmt1, SQL_CONCURRENCY,

   retcode = SQLSetStmtOption(hstmt1,

   retcode = SQLSetStmtOption(hstmt1, SQL_ROWSET_SIZE, 1);

// Perform the join and bind the columns in the result set
   retcode = SQLExecDirect(hstmt1,(UCHAR *)"select * from

   retcode = SQLBindCol(hstmt1, 1, SQL_C_LONG, &try1,

   retcode = SQLBindCol(hstmt1, 2, SQL_C_CHAR, Col2,

   retcode = SQLBindCol(hstmt1, 3, SQL_C_LONG, &try2,

   retcode = SQLBindCol(hstmt1, 4, SQL_C_LONG, &try3,

   retcode = SQLBindCol(hstmt1, 5, SQL_C_CHAR, Info,

// Fetch the resultset and position the cursor
   retcode = SQLExtendedFetch(hstmt1, SQL_FETCH_FIRST, 1, &r,

// Set the pcbValues so that Col1 of Test1 can be updated.
// Notice that all the columns that won't be updated are set to
// SQL_IGNORE

   try1=600;
   cbCol12=SQL_IGNORE;
   cbCol21=SQL_IGNORE;
   cbCol22=SQL_IGNORE;
   cbCol23=SQL_IGNORE;

// Update the values
   retcode = SQLSetPos(hstmt1, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);

// Free the handles
   retcode = SQLFreeStmt(hstmt1, SQL_DROP);
   retcode = SQLDisconnect(hdbc1);
   retcode = SQLFreeConnect (hdbc1);
   retcode = SQLFreeEnv(henv);