Q135109: HOWTO: Use Updatable Joins in an MFC ODBC Application
Article: Q135109
Product(s): Microsoft C Compiler
Version(s): 1.51,1.52,2.0,2.1,2.2,4.0,4.1,4.2
Operating System(s):
Keyword(s): kbProgramming kbDatabase kbMFC kbODBC kbVC
Last Modified: 18-FEB-2002
-------------------------------------------------------------------------------
The information in this article applies to:
- The Microsoft Foundation Classes (MFC), included with:
- Microsoft Visual C++ for Windows, 16-bit edition, versions 1.51, 1.52
- Microsoft Visual C++, 32-bit Editions, versions 2.0, 2.1, 2.2, 4.0, 4.1
- Microsoft Visual C++, 32-bit Enterprise Edition, version 4.2
- Microsoft Visual C++, 32-bit Professional Edition, version 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. 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
Using snapshots forces joins non-updatable. MFC snapshots by default loads the
cursor library (see appendix G of the ODBC 2.0 Programmer's Reference for more
information about the cursor library) which doesn't support updatable joins. The
alternative to using snapshots is to use dynasets which require you to
explicitly inhibit the loading of the cursor library.
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]
The Microsoft Desktop Drivers are unable to process SQLSetPos updates on a
recordset opened using a join in its select statement. This means that a dynaset
won't allow updatable joins unless you use a predefined query as mentioned
above. If the SELECT statement contains a join, the driver will reject attempts
to perform updates. This is a limitation of the drivers.
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"
Additional query words:
======================================================================
Keywords : kbProgramming kbDatabase kbMFC kbODBC kbVC
Technology : kbAudDeveloper kbMFC
Version : :1.51,1.52,2.0,2.1,2.2,4.0,4.1,4.2
Issue type : kbhowto
=============================================================================
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 1986-2002.