HOWTO: Use Updatable Joins in an MFC ODBC Application

• The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++ for Windows, versions 1.51, 1.52 - Microsoft Visual C++, 32-bit Edition, versions 2.0, 2.1, 2.2,

       4.0, 4.1, 4.2
  

SUMMARY

One way to use updatable joins with MFC ODBC applications utilizing the Microsoft Desktop ODBC Drivers is to use dynaset recordsets based on a predefined query stored in the database. In order to use this approach, your application must do all of the following:

   - Use unique indexes on the primary key and all matching
     foreign key columns in all tables in the join.

   - Create a predefined query (a.k.a. stored procedure) which
     performs the join and returns all fields you will reference
     in your application.

   - Use dynasets in your MFC application. To use dynasets with a
     16-bit Visual C++ application, see article Q124915,"SAMPLE: Using
     Dynasets with the 16-Bit MFC Database Classes". VC++ 2.x directly
     supports the use of dynasets.

   - If you open the predefined query using the "{CALL QueryName}"
     syntax you must force the CRecordset-derived class members
     m_bUpdatable and m_bAppendable TRUE to enable updatability.

MORE INFORMATION

Background

A join links records from multiple tables matching one or more key fields in each table to create a "view." When a join is updated, the fields in the view and the corresponding fields in the tables are modified.

Generally, a join is performed with a SQL statement of this nature:

SELECT Table1.colA, Table2.colB FROM Table1, Table2 WHERE Table1.primary_key=Table2.foreign_key

You can specify such SQL in your recordset's SELECT statement, but the resulting join will not be updatable for reasons to be discussed.

When are joins not updatable?

The Microsoft desktop ODBC drivers do not support updatable joins of the form just described in the following two cases:

   - Using snapshots.
   - Executing a SQL SELECT statement with multiple tables

If you attempt to use snapshots with joins, you will first encounter the fact that MFC will mark the recordset read only. Even if you set m_bUpdatable and m_bAppendable members of CRecordset TRUE yourself, you will receive the following error:

  In VC++ 1.5x:
    Positioned request cannot be performed because result set was
    generated by a join condition
    State:SL002[Microsoft][ODBC Cursor Library]

  In VC++ 2.x:
    Positioned request cannot be performed because result set was
    generated by a join condition
    State:SL002,Native:0,Origin:[Microsoft][ODBC Cursor Library]

If you specify a join in the SQL SELECT statement of your recordset Open() call or in the GetDefaultSQL() call and are using dynasets, you will receive the following error:

  In VC++ 1.5x:
    Warning: multi-table recordset not updatable
    Warning: ODBC Success With Info, Error in row
    State:01S01[Microsoft][ODBC Microsoft Access 2.0 Driver]
    Can't update.  Database or object is read-only.
    State:42000[Microsoft][ODBC Microsoft Access 2.0 Driver]

  In VC++ 2.x:
    Error: failure updating record.
    Can't update.  Database or object is read-only.
    State:42000,Native:-1809,Origin:[Microsoft]
    [ODBC Microsoft Access 2.0 Driver]

How to perform a join that will be updatable

To perform updatable joins based on a predefined query, you must meet the criteria laid out in the summary section of this article. Additional information about using predefined queries in MFC ODBC applications is provided below.

When using a predefined query in MFC employing dynasets you can utilize two methods of opening the recordset:

• Use the "{CALL predefined_query}" syntax in the recordset open to explicitly execute a predefined query and use its result set in the recordset.

  For example:

       m_pSet->Open(CRecordset::dynaset, "{CALL QueryName}");
  

  Note: If you use the "CALL" syntax, you must force m_bUpdatable

           and m_bAppendable member variables of your recordset class
           to TRUE since they will be FALSE by default since predefined
           queries are assumed read-only.  Do this in a member function
           of your CRecordset-derived class since these are protected
           members.
  
  

• Open the view that a predefined query creates just as if it were a regular table in the database.

  For example:

       m_pSet->Open(CRecordset::dynaset);
  

  where the GetDefaultSQL() member of your recordset derived class is:

       {return("QueryName");}
  

  Note: When using predefined queries, regardless of how you

            open the recordset, you refer to the tables specified in the
            query, not to the query itself, in your RFX functions.
  
            For example, if Table1 is a table referenced in predefined
            query QueryName, a representative RFX function would be:
  
              RFX_Text(pFX, "Table1.primary_key", m_member);
  
  
  

REFERENCES

In the "MFC Encyclopedia" which came with Visual C++, there is an article titled "Recordset: Declaring a Class for a Predefined Query"

Keywords          : MfcDatabase kbprg kbprg
Technology        : kbMfc
Version           : 1.51 1.52 2.0 2.1 2.2 4.0
Platform          : NT WINDOWS
Issue type        : kbhowto