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

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: This error occurs if the application does not set the pcbValue argument in SQLBindCol to SQL_IGNORE for columns that will not be updated. For more information on SQLSetPos and SQL_IGNORE, please refer to the Microsoft "ODBC 2.0 Programmer's Reference and SDK Guide."

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:
Create table test1 (col1 int, col2 char(10))
Create table test2 (col1 int, col2 int, col3 char(10))*/ 

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,
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,(UCHAR *)"select * from
test1,test2 where test1.col1=test2.col1",SQL_NTS);
   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 resultset and position the cursor
   retcode = SQLExtendedFetch(hstmt1, SQL_FETCH_FIRST, 1, &r,
&rgfRowStat);

// 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);
}