DOCUMENT:Q140532  11-MAY-2001  [odbc]
TITLE   :INF: How to Use SQLSetPos in Updatable Joins w/ SQL Server
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER:WINDOWS:2.5; winnt:
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Open Database Connectivity, version 2.5 
 - The Microsoft Foundation Classes (MFC), included with:
    - Microsoft Visual C++, versions 1.52,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>

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

Additional query words: MFC VC++ sql 6.00 2.00 2.10 2.20 4.00

======================================================================
Keywords          :  
Technology        : kbAudDeveloper kbMFC kbODBCSearch
Version           : WINDOWS:2.5; winnt:
Issue type        : kbinfo

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.