MFC 2.5 Database Class Frequently Asked Questions

ID: Q114938


The information in this article applies to:


SUMMARY


Microsoft Foundation Class Library, Version 2.5
Database Classes
Frequently Asked Questions and Answers
Last revised: April 29, 1994

The text below presents a list of 13 frequently asked questions regarding the Microsoft Foundation Class Library, version 2.5, Database Classes and the answers to these questions. For additional information regarding the Microsoft Foundation Class Library, please refer to the Microsoft Knowledge Base and to the Microsoft Software/Data Library.

The Microsoft Knowledge Base contains technical articles about every Microsoft product.

The Microsoft Software Library contains many complete sample applications. Each file has a corresponding article in the Knowledge Base that describes its purpose. To find information in the Library, first search the Knowledge Base to determine if an appropriate sample is available.

The Visual C++ product also contains extensive MFC documentation in Windows help files. The 'MFC Help' file (MFC.HLP) documents the Microsoft Foundation Classes API. The 'MFC Tech Notes' files (mfcnotes.hlp) contains a collection of technical articles regarding the Microsoft Foundation Classes. The 'MFC Samples Help' file (mfcsamp.hlp) contains information about the sample applications shipped with Visual C++ that use the Microsoft Foundation Classes.
INFORMATION PROVIDED IN THIS DOCUMENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. The user assumes the entire risk as to the accuracy and the use of this document. This document may be copied and distributed subject to the following conditions: 1) All text must be copied without modification and all pages must be included and 2) This document may not be distributed for profit.

Copyright (C) 1994 Microsoft Corporation. All Rights Reserved. Microsoft and MS-DOS are registered trademarks and Windows and Windows NT are trademarks of Microsoft Corporation.

FAQ TERMS:

API: Application Programming Interface
DDX: Dialog Data Exchange
DLL: Dynamic Link Library
MFC: Microsoft Foundation Classes
ODBC: Open DataBase Connectivity

FAQ INDEX TO QUESTIONS:

The following questions have answers provided within this document. The order of the questions is random and has no significance.

FAQ_1. What problems can be encountered when using AppWizard or ClassWizard and a WATCOM SQL ODBC data source?

FAQ_2. Why do I get the message: "SQLColumns failed." immediately after selecting a table in my ODBC text data source while using AppWizard to generate my database application?
-or-
Why does Windows hang while using AppWizard to generate my database application which uses an ODBC text data source?
-or-
Why do I get a General Protection Fault (GPF) while using AppWizard to generate my database application which uses an ODBC text data source?

FAQ_3. What could be causing problems when I try to use an Oracle data source?

FAQ_4. How do I write a DDX routine to associate an edit control in my CRecordView with a CTime variable in my CRecordset object?

FAQ_5. How can I programmatically configure ODBC data sources from my application?

FAQ_6. How can I programmatically create a table for a data source from my application?

FAQ_7. Why are the Previous, First, and Last buttons on the toolbar for my database application grayed out, even after moving past the first record?
-or-
Why are the menu items "Record.Previous Record", "Record.First Record", and "Record.Last Record" grayed out, even after moving past the first record?
-or-
Why is a database exception being thrown when I call CRecordset::MovePrev(), CRecordset::MoveFirst(), or CRecordset::MoveLast()?
-or-
Why do I see "Internal application error." in a messagebox when I call CRecordset::MovePrev(), CRecordset::MoveFirst(), or CRecordset::MoveLast()?

FAQ_8. Why do I get the following Assertion when I run my database application: "File dbcore.cpp, Line 549, Assertion Failed!"?

FAQ_9. Why do I see "Internal application error." in a messagebox when I try to create a dynaset CRecordset object?
-or-
What does it mean when I see "ODBC driver does not support dynasets." in the Output window for the Visual Workbench?

FAQ_10. When I change the sort (m_strSort) or filter (m_strFilter) for my query and call CRecordset::Requery(), why do I still see the records from the original result set?

FAQ_11. How do I figure out what which system files I need to ship with my database application?

FAQ_12. Why do I get the message: "Not enough memory to complete operation" when I try to use AppWizard to generate a database application?
-or-
Why does the Visual Workbench hang when I try to use AppWizard to generate a database application?

FAQ_13. Why do I see the following messages in the Output window for the Visual Workbench when CDatabase::Open() or CRecordset::Open() is called?
Warning: ODBC Success With Info, Driver's SQLSetConnectOption failed
State:IM006,Native:0,Origin:[Microsoft][ODBC DLL]
Driver not capable.
State:IM006,Native:0,Origin:[Microsoft][ODBC DLL]
State:S1C00,Native:0,Origin:[Microsoft][ODBC Single-Tier Driver]

FAQ ANSWERS:


FAQ_1. What problems can be encountered when using AppWizard or ClassWizard and a WATCOM SQL ODBC data source? The following problems can occur when connecting to a WATCOM SQL data source with AppWizard or ClassWizard:

If you are using WATCOM SQL with Visual C++, you must use version 3.2b or higher. Newer versions of WATCOM SQL are available by calling WATCOM Internatioal at 1-800-265-4555. Once you have version 3.2b or higher, make sure the following two actions are taken:


FAQ_2. Why do I get the message: "SQLColumns failed." immediately after selecting a table in my ODBC text data source while using AppWizard to generate my database application?
-or-
Why does Windows hang while using AppWizard to generate my database application which uses an ODBC text data source?
-or-
Why do I get a General Protection Fault (GPF) while using AppWizard to generate my database application which uses an ODBC text data source? This may happen because the ODBC SQLColumns() function, called by AppWizard, is failing. The function is failing because AppWizard is passing it an invalid table name. To work around this problem, rename the related files in the data source directory so that there are no extensions in the filenames. For example, as a workaround the file 'DATASRC.CSV' can be renamed to 'DATASRC'.

When AppWizard queries for a filename, it incorrectly uses the dot (".") in the filename as a separator between the table owner and the table name. For example, if the file DATASRC.CSV is specified as the initial table, AppWizard will look for a table called CSV, which does not exist. The SQLColumns call is failing because AppWizard is passing an invalid table name by using this technique.

Why is AppWizard incorrectly handling the table name only when using the text ODBC driver? The text ODBC driver accepts text filenames with different extensions for each table. The text ODBC driver, therefore, reports the full filenames for table names. This is in conflict with the ODBC SQL specification, which defines the "." as the table-owner name/table-identifier separator. Other ODBC drivers typically have a very specific filename extension and do not return table names with filename extensions, and therefore there is not a problem with AppWizard incorrectly handling the table name.
FAQ_3. What could be causing problems when I try to use an Oracle data source? An application built to access an Oracle data source using MFC version 2.5 may crash or throw a CDBException exception.

Two known problems specific to the Oracle driver are:

There is a bug in the wizards that causes them to generate an incorrect GetDefaultSQL() function when using an Oracle data source. Looking at the CRecordset's GetDefaultSQL() function you will see that an at sign ("@") has been placed between the table owner and the table name. For example, you may see:

   CString CMyRecordSet::GetDefaultSQL()
   {
       return "Dan@Customer";
   } 
Change the "@" to a period (.).

As mentioned above, an application may also crash because of a stack overflow. The Oracle drivers use a large amount of stack space. If the stack overflows, it will write into the main data segment and cause undefined behavior after returning to the application. If you experience problems when using an Oracle data source, try increasing your application's stack size.
FAQ_4. How do I write a DDX routine to associate an edit control in my CRecordView with a CTime variable in my CRecordset object? There is no predefined DDX routine for CTime objects. The overloaded DDX_FieldText() function does not support CTime objects. This is because a CTime object represents both a date and a time of day and there is no simple conversion that can represent both of these values in an edit control. DDX_FieldText() DDX routines can be written for any data type. New editions of this function can be created to provide the same functionality for any data type. As there are already overloaded versions of this function for many data types this is a common and necessary technique for an application to support the use of date fields.

To support date fields, implement a user-defined DDX routine for CTime objects. A call to this function can be included in the DoDataExchange() method of your CRecordView derived class. A sample implementation of such a function is included with this answer. More information on DDX routines can be found in the MFC Technical Note #26, "DDX and DDV Routines," and in the online documentation for the CDataExchange class.

NOTE: DDX_FieldText is for use with CRecordView derived objects, but the following routine can easily be modified for use with CFormView and CDialog derived classes.

Sample Code


/* Compile options needed:  Default AppWizard MFC Options
*/ 

///////////////////////////////////////////////////////////////////// 
// Example RecordView Data Exchange

#include <ctype.h>

void DDX_FieldText( CDataExchange *pDX, UINT nID, CTime& datevar,
                    CRecordset *pSet );

void CMyRecordView::DoDataExchange(CDataExchange* pDX)
{
    CRecordView::DoDataExchange(pDX);
    //{{AFX_DATA_MAP(CTranView)

    // ... ( class wizard / app wizard generated code )

    //}}AFX_DATA_MAP

    // date DDX
    DDX_FieldText(pDX, IDC_EDIT_TRAN_DATE,
                  m_pSet->m_Database_Date, m_pSet);
}

///////////////////////////////////////////////////////////////////// 
// Example Date DDX_FieldText function

void DDX_FieldText( CDataExchange *pDX, UINT nID, CTime& datevar,
                    CRecordset *pSet )
{
    if ( pDX->m_bSaveAndValidate )
    {
            // Move date from edit control to CTime
        CString strDate;
        pDX->PrepareEditCtrl( nID );
        pDX->m_pDlgWnd->GetDlgItem( nID )->GetWindowText( strDate );
        int nMonth, nDay, nYear;
        int nLen = strDate.GetLength();

        // Parse the date string for mm/dd/yy format.
        BOOL status = FALSE;
        while ( !status )
        {
            int i = 0;
            int nMarker = 0;

            // Note that sscanf() could be used to read
            // these formated strings.

            // find first slash
            while( i < nLen && strDate[i++] != '/' );
            if ( i == nLen )
                break;

            // month
            nMonth = atoi( strDate.Left( i ) );
            if ( nMonth < 1 || nMonth > 12 )
                break;

            // find next slash
            nMarker = i++;
            while( i < nLen && strDate[i++] != '/' );
            if ( i == nLen )
                break;

            // day
            nDay = atoi( strDate.Mid( nMarker, i - nMarker ) );
            if ( nDay < 1 || nDay > 31 )
                break;

            // year
            if ( nLen - i < 2 || ! isdigit( (int) strDate[i] )
                 || ! isdigit( (int) strDate[i+1] ) )
                break;
            nYear = atoi( strDate.Right( nLen - i ) );
            nYear += ( nYear < 37 ? 2000 : 1900 );
            // valid years for CTime object
            if ( nYear < 1970 || nYear > 2036 )
                break;

            CTime tTemp( nYear, nMonth, nDay, 0, 0, 0 );
            datevar = tTemp;
            status = TRUE;
        }

        if ( !status )
        {
            AfxMessageBox( "Incorrect date field format",
                    MB_OK | MB_ICONEXCLAMATION );
            pDX->Fail();
        }
    }
    else
    {
            // Move date from CTime to edit control
        // NOTE: no internationalization: mm/dd/yy format only

        CString strDate = datevar.Format( "%m/%d/%y" );
        pDX->m_pDlgWnd->GetDlgItem( nID )->SetWindowText( strDate );
    }
} 

FAQ_5. How can I programmatically configure ODBC data sources from my application? Your application may setup and configure any of the data sources it needs through the use of ODBC API's. This means that your application need not require its user to explicitly use the ODBC Administrator program (typically installed in the Windows Control Panel) to perform these tasks. To make use of the feature, use the SQLConfigDataSource() function in your program. For additional information on creating tables, see question FAQ_6 in this document.

The ODBC Administrator program creates a new data source by adding entries to the ODBC.INI file. This file is queried by the ODBC Driver Manager to obtain the required information about each data source. It is important to know what information needs to be placed in the ODBC.INI because you will need to supply it with the call to SQLConfigDataSource().

Although this information could be written directly to the ODBC.INI file, without using SQLConfigDataSource(), any application that does so is relying on the current technique that the Driver Manager uses to maintain its data. If a later revision to the ODBC Driver Manager implements record keeping for data sources in a different way, then any application that manually modified the ODBC.INI file instead of calling DQLConfigDataSource() would be broken. It is generally advisable to use an API function when one is provided.

The following example uses SQLConfigDataSource() to create a new Excel data source called "New Excel Data Source":

   SQLConfigDataSource(NULL,ODBC_ADD_DSN,
     (LPSTR) "Excel Files (*.xls)",
     (LPSTR) "DSN=New Excel Data Source\0"
     "Description=New Excel Data Source\0"
     "FileType=Excel\0"
     "DataDirectory=C:\\EXCELDIR\0"
     "MaxScanRows=20\0"); 
Note that the data source is actually represented as a directory (C:\EXCELDIR). The Excel driver uses directories as its data sources, and files as the individual tables (one table per .XLS file).

The information below discusses the parameters that need to be passed to SQLConfigDataSource(). To use the SQLConfigDataSource() function, you must include the ODBCINST.H header file and use the ODBCINST.LIB import library. Much of this information is taken from the ODBC API Programmer's Reference supplied with Visual C++, version 1.5.
Function prototype:

   BOOL SQLConfigDataSource(HWND hwndParent, UINT fRequest,
                            LPCSTR lpszDriver,
                            LPCSTR lpszAttributes); 
hwndParent - This is the window that will be used as the owner of any dialog boxes which are created by either the Driver Manager or the specific ODBC Driver to obtain additional information from the user about the new data source. If there is not enough information provided in the lpszAttributes parameter, a dialog box will appear. This parameter may be NULL, see the reference for specifics.

lpszDriver - Driver description. As the documentation mentions, this is the name presented to the users rather than the physical driver (the DLL). You can determine the description of a driver using the ODBC Administrator program as follows:

  1. Run the ODBC Administrator program.


  2. Choose Add. This will give you a list of installed drivers.


The list contains driver descriptions. It is this description that you will use as the lpszDriver parameter. Note that the ENTIRE description is used [for example, "Excel Files (*.xls)"] including the file extension and parentheses if they exist in the description.

Optionally, you can examine the file ODBCINST.INI, which contains a list of all driver entries and descriptions in the section [ODBC Drivers].

lpszAttributes - List of attributes in the form "keyname=value". These strings are separated by null terminators with two consecutive null terminators at the end of the list. These attributes will primarily be default driver-specific entries, which go into the ODBC.INI file for the new data source. One important key, which is not mentioned in the ODBC API reference for this function, is "DSN" which specifies the name of the new data source. The rest of the entries are specific to the driver for the new data source. Often times it is not necessary to supply ALL of the entries because the driver can prompt the user (if hwndParent is not NULL) with dialog boxes for the new values. You might want to explicitly supply default values so that the user is not prompted. One way to find the keynames and their values is to examine the ODBC.INI file for an already configured data source (perhaps one that has been configured by the ODBC Administrator program):

  1. Open the ODBC.INI file.


  2. Find the section marked by [<data source name>] where <data source name> is a data source that has already been configured with the desired settings for the driver you intend to use. The lines following this line will be of the form "keyname=value". These are exactly the entries you will want to use in your lpszAttributes parameter.


You might also want to examine the documentation for the specific driver you are going to use. Useful information may be found in the online help for the driver. Instructions to view this help are contained in the answer to FAQ_11.
FAQ_6. How can I programmatically create a table for a data source from my application? To create a table for the data source, use the CDatabase::ExecuteSQL() function and pass it a string that contains the 'CREATE TABLE' SQL statement.

MFC version 2.5 uses the ODBC API functions to access and manipulate data. Before a table can be created, ODBC requires that a "data source" be configured to allow access to data. A data source is a label by which information may be retrieved without regard to how, or by whom, the information is managed. The information in a data source is typically managed by one of a variety of ODBC driver programs such as FoxPro, Access, or SQL Server. The information in a data source may be stored in various locations such as locally or on a network. Once a data source is configured/named, the programmer need only specify the name of the data source to connect to the data.

The ODBC Administrator program (typically installed in the Windows Control Panel) may be used to create a data source. Many data sources, for example: FoxPro, maintain a directory specification for databases. That is, a directory *is* the data source and each table within the data source is stored in a separate file within that directory (in the case of dBASE or FoxPro, each table is a .DBF file).

Other ODBC driver programs, such as Microsoft Access and SQL Server, require that some specific criteria be satisfied before a data source can be established. For example, when using the SQL Server ODBC driver you would need to have established a SQL server. The Microsoft Access ODBC driver requires that an .MDB file must exist before it can create a data source. When creating an Access ODBC data source through the ODBC Administrator program, you are given two choices: you can select an .MDB file or you can create an .MDB file. There is not a programmatic way of creating the .MDB file from your application. Therefore, if your application requires that you place data into an Access data source (.MDB file), you most likely will want to have an empty .MDB file that you can use or copy whenever you need it.

Once you have the data source established, creation of tables can be easily performed by using the CDatabase::ExecuteSQL() function and the 'CREATE TABLE' SQL statement. For example, if you had a database object called myDB in your MFC application, you could use the following code to create a table:

    myDB.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(10)"
                    ", OfficeName TEXT(10))") 
This code creates a table called "OFFICES" in the ACCESS data source connection maintained by myDB; the table contains two fields "OfficeID" and "OfficeName".

NOTE: The field types specified in the 'CREATE TABLE' SQL statement may vary according to the ODBC driver that you are using. For example, the Btrieve ODBC driver will require "STRING" in place of the "TEXT" type shown in the 'CREATE TABLE' statement above. The MSQUERY program is one way to discover what field types are available for a data source. In MSQUERY, select File, choose Table_Definition, select a table from a data source, and look at the type shown in the "Type" combo box.

For more information about data sources, see the "Database Classes Encyclopedia" in the online books.
FAQ_7. Why are the Previous, First, and Last buttons on the toolbar for my database application grayed out, even after moving past the first record?
-or-
Why are the menu items "Record.Previous Record", "Record.First Record", and "Record.Last Record" grayed out, even after moving past the first record?
-or-
Why is a database exception being thrown when I call CRecordset::MovePrev(), CRecordset::MoveFirst(), or CRecordset::MoveLast()?
-or-
Why do I see "Internal application error." in a messagebox when I call CRecordset::MovePrev(), CRecordset::MoveFirst(), or CRecordset::MoveLast()? The cause for this problem is a failure of the ODBC.DLL (the ODBC driver manager) to load the ODBCCURS.DLL file. The ODBCCURS.DLL file is the cursor library. This DLL gives scrollable cursor functionality to ODBC Level 1 drivers. Therefore, if this DLL is not loaded, scrollable cursor support is unavailable. In this case, a forward-only cursor will be used.

There are two reasons why the cursor library (ODBCCURS.DLL) might not be loaded:

Check to see that the file \WINDOWS\SYSTEM\ODBC.DLL is the same date or newer than the \MSVC\REDIST\ODBC.DLL file on the Visual C++ CD. If not, copy the file from the CD to your \WINDOWS\SYSTEM directory, replacing the older copy. As well, if the ODBCCURS.DLL is not located in the \WINDOWS\SYSTEM directory, copy it from the \MSVC\REDIST on the Visual C++ CD.

NOTE: To be absolutely sure you have correct versions of the ODBC DLLs, you should run the Visual C++ Setup program again and select "Custom Installation." Then, select only the "MFC Database (ODBC)" check box so that you copy only the needed ODBC components. You will also want to select the ODBC drivers you need by clicking the Drivers button next to the "MFC Database (ODBC)" check box. The setup program only installs files which are of a later version than those that already exist on the system.
FAQ_8. Why do I get the following Assertion when I run my database application: "File dbcore.cpp, Line 549, Assertion Failed!"? If CDatabase::BeginTrans() is called a second time for a CDatabase object and the debug version of the MFC Library is used, the following assertion will occur:
File dbcore.cpp, Line 549, Assertion Failed!
The message is displayed in a message box. When running under the debugger, the message is also displayed in the Output window.

This problem occurs even though CDatabase::CommitTrans() was called after the first CDatabase::BeginTrans() call.

The CDatabase class uses a variable called "m_bTransactionPending" for debug mode, which gets set to TRUE in CDatabase::BeginTrans() with the following code:

   #ifdef _DEBUG
       m_bTransactionPending = TRUE;
   #endif // _DEBUG 
The m_bTransactionPending variable should be assigned FALSE (but is not) in the CDatabase::CommitTrans() function. Here is the code:

   #ifdef DEBUG
       m_bTransactionPending = FALSE;
   #endif // DEBUG 
Note that "DEBUG" was used rather than "_DEBUG". This is a bug that occurs only when using the debug version of the MFC Library.

Modify the code in CommitTrans() by changing the DEBUG constant to _DEBUG, and rebuild the MFC library by following the directions in the README.TXT located in the \MSVC\MFC\SRC directory.

As an alternative, you could copy the code from CommitTrans() into a new member function of a CDatabase derived class, fix the code, and call the new function instead of CommitTrans(). However, if you are letting CRecordset create the database object (that is, not passing a pointer to a CDatabase object into the constructor), CDatabase will be used instead of your CDatabase derived class.
FAQ_9. Why do I see "Internal application error." in a messagebox when I try to create a dynaset CRecordset object?
-or-
What does it mean when I see "ODBC driver does not support dynasets." in the Output window for the Visual Workbench? The ODBC driver being used does not support dynasets. The ODBC drivers provided with Visual C++, version 1.5 do not support dynasets.

To use the dynaset attribute of a CRecordset object, you must acquire an ODBC driver that will support the features of a dynaset. MFC Technical Note 42 "ODBC Driver Developer Recommendations" lists the requirements for an ODBC driver if it is to be used for creating dynaset CRecordsets. They are:

   SQLGetInfo, SQL_ODBC_VER must return &gt; "01".
   SQLGetInfo, SQL_SCROLL_OPTIONS must support SQL_SO_KEYSET_DRIVEN.
   SQLGetInfo, SQL_ROW_UPDATES must return "Y".
   SQLGetInfo, SQL_POSITIONED_UPDATES must support
               SQL_PS_POSITIONED_DELETE and
               SQL_PS_POSITIONED_UPDATE. 
In addition, if pessimistic locking is requested, a call to SQLSetPos() with irow 1, fRefresh FALSE, and fLock SQL_LCK_EXCLUSIVE will be made.

The information below comes from the README.WRI file included with Visual C++, version 1.5, and discusses what needs to be done to enable dynaset support when using an ODBC driver that meets the requirements shown above.
CDatabase::Open() forces loading of ODBC's cursor library with the ::SQLSetConnection() SQL_ODBC_CURSORS call in dbcore.cpp, line 238. Forced loading of the cursor library ensures the SQL_SCROLL_STATIC scroll option is supported for snapshots, even if the underlying driver does not support scrolling.

In order to open a recordset as a dynaset, the driver must support SQL_SCROLL_KEYSET, and the forced loading of the cursor library must be changed to optional loading. This can be done by changing the scroll option argument in the ::SQLSetConnection() call mentioned above from SQL_CUR_USE_ODBC to SQL_CUR_USE_IF_NEEDED. This will cause the cursor library to NOT be loaded if the underlying driver supports SQLExtendedFetch(). Note that snapshots require either the cursor library to be loaded, or for the underlying driver to support SQL_SCROLL_STATIC.

FAQ_10. When I change the sort (m_strSort) or filter (m_strFilter) for my query and call CRecordset::Requery(), why do I still see the records from the original result set? The online help for the CRecordset::Requery() function incorrectly states the following:
For either a dynaset or a snapshot, call Requery() any time you want to rebuild the recordset using a new filter or sort, or new parameter values. Set the new filter or sort property by assigning new values to m_strFilter and m_strSort before calling Requery(). Set new parameters by assigning new values to parameter data members before calling Requery(). If the filter and sort strings are unchanged, you can reuse the query, which improves performance.
This is a documentation error. The Requery() function is not affected by changes in the sort (m_strSort) or filter (m_strFilter) variable of CRecordset. The Requery() function is affected by parameterized filters. The use of parameters is demonstrated in Chapter 4 of the Enroll database tutorial (see "Database Classes," "Part 1: Database Tutorial" in the Visual C++, version 1.5 Books Online).

Below is the code for the CRecordset::Requery() function:

   BOOL CRecordset::Requery()
   {
       RETCODE nRetCode;

       ASSERT_VALID(this);
       ASSERT(IsOpen());
       // Cannot requery until all pending Async operations have
       // completed.
       ASSERT(!m_pDatabase->InWaitForDataSource());

       TRY
       {
           // Shut down current query.
           AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_CLOSE));

           // Now try to reexecute the SQL query.
           AFX_SQL_ASYNC(this, ::SQLExecute(m_hstmt));
           if (!Check(nRetCode))
               ThrowDBException(nRetCode);

           m_nFieldsBound = 0;
           InitRecord();
       }
       CATCH_ALL(e)
       {
           Close();
           THROW_LAST();
       }
       END_CATCH_ALL

       return TRUE;    // all set
   } 
Note that the function merely does a SQLExecute() call; it relies on the ODBC API function SQLPrepare() to initialize the query earlier in the program in the CRecordset::Open() function. SQLPrepare() helps improve query speed by telling the ODBC driver that the query will always be of the same form; the structure of the SQL statement will not change. This is where SQL statement parameters are beneficial.

For example, suppose that you have the following SQL statement, which is used for an SQLPrepare() call:

   SELECT name, phonenum from customertable where name = ? 
The question mark (?) is an ODBC defined placement holder. It permits the program to specify a new filter using the ODBC API SQLSetParam() without changing or specifying a new SELECT statement later in the program. There is only one filter and the SELECT statement will not change. ODBC drivers can optimize performance because no reparsing needs to be done when SQLExecute() is called. The parsing of the SELECT statement is done once in the SQLPrepare() call.

In the SELECT line above, the parameterized filter would be handled by assigning m_strFilter = "name=?", and then using a variable to store the data for the parameter. For more information, see the section titled "Filtering and Parameterizing the Recordset" in "Database Classes", "Part 1: Database Tutorial", Chapter 4 "Step 2: Using a Second Recordset" of the Visual C++, version 1.5 Books Online.

See "Part 1: Database Tutorial" in the "Database Classes" reference and the "ODBC API Programmer's Reference" in the Visual C++, version 1.5 Books Online for more information about ODBC SQL statement parameters, SQLPrepare() and SQLExecute().

Because SQL parameters (for example, ? markers) are not permitted in SORT clauses, you cannot use the Requery() function if you need to change the sorting order.

In summary, if the structure of the SQL statement is not going to change, use ODBC SQL parameters. If the structure of the SELECT statement is changed in the program, you must requery by calling Close() and then Open() for the CRecordset.

Here is an example of a SELECT statement that changes structure during a program. Suppose the original query for the CRecordset is:

   Select name, phonenum from customertable where name = 'Dan' 
This is accomplished by setting m_strFilter to "name='Dan'". Now, later in the application, you want to change the query so that it shows all "Dan"'s in a certain zip code. You might have:

   Select name, phonenum from customertable where name = 'Dan' and zipcode=97439 
The structure of the query has changed because the WHERE clause now contains two conditions. In other words, the m_strFilter string would be "name = 'Dan' and zipcode=97439". To query, you would need to call CRecordset::Close() and then CRecordset::Open(), rather than Requery(). ODBC SQL parameters would not work because the SELECT statement cannot be written to accept one filter and then two filters at a later time.
FAQ_11. How do I figure out what which system files I need to ship with my database application? The REDISTRB.WRI file found in the \MSVC\REDIST subdirectory on the Visual C++ CD explains the main issues associated with determining which files to include. With respect to the files required due to the application's use of the database classes, the "ODBC API Programmer's Reference" in the Visual C++ Books Online is referenced as a resource for identifying ODBC related files. Specifically, Part 4 of this reference discusses the tools provided by Visual C++ to facilitate the installation of the ODBC related files. However, Part 4 provides only general techniques and examples of the process; it does not make clear the specific files required by each particular driver for ODBC support. These files may be determined using the driver help files (.HLP) provided with Visual C++, version 1.5.

To find a list of which drivers to ship for a particular type of data source (that is, Microsoft FoxPro, Microsoft Access, and so forth), use one of the following methods:

Method 1

  1. Run the ODBC Administrator program (usually in the Control Panel or the Microsoft ODBC Group of Program Manager).


  2. Highlight your data source by single-clicking it.


  3. Single-click the Setup button to view the Setup dialog box.


  4. Single-click the Help button to view the driver help file for that data source.


  5. Single-click the Contents button near the top-left corner to navigate to the main Help panel.


  6. Single-click "Overview" (the highlighted hyperlink) to view all of the associated files (.DLLs).


Method 2

  1. Use File Manager to list the DRV*.HLP files in the \MSVC\REDIST subdirectory on the Visual C++ CD (which may also have been installed to the \WINDOWS\SYSTEM or WINNT\SYSTEM subdirectory of your machine).


  2. Run the help file viewer by double-clicking the help file for the driver you are interested in. For example, the help file for the Microsoft Access ODBC driver is called DRVACCSS.HLP.


  3. Single-click the "Overview" hyperlink to view all of the associated files (.DLLs).


The Overview panel lists the files that must be shipped with the application in order to support the MFC database classes. In addition, one other file must be included in most cases: ODBCCURS.DLL. The ODBCCURS.DLL file is the ODBC Cursor Library. This library gives Level 1 drivers the capability of forward and backward scrolling, and also provides the capability of supporting snapshots. (For more information regarding the ODBC Cursor Library, see the Visual C++ Books Online).
FAQ_12. Why do I get the message: "Not enough memory to complete operation" when I try to use AppWizard to generate a database application?
-or-
Why does the Visual Workbench hang when I try to use AppWizard to generate a database application? AppWizard issues the following error message when trying to create a database application that uses a database table containing a large number of fields (number of fields varies depending on the field name lengths):
Not enough memory to complete operation.
AppWizard may also cause the Visual Workbench to hang in this situation.

The workaround for this problem is to use AppWizard to generate a "database enabled" application. This is accomplished by using another table within the same data source as the table with many columns. However, this other table, henceforth referred to as the "proxy," does not cause AppWizard to fail when generating the application. After the application has been successfully generated, ClassWizard is then used to modify the code to use the table with many columns instead of the proxy. One other change also requires the modification of a single line of code.

The following is a step-by-step approach to using this technique:

  1. Identify another table, the proxy, which contains fewer columns in the same data source, or create one.


  2. Using AppWizard, create a skeleton application selecting all of the originally desired options. When choosing the data source, use the proxy table identified in step 1.


  3. Open ClassWizard (CTRL+W) and switch to the "Member Variables" tab.


  4. For the "Class Name," select the CRecordset derived class (it should be a name that ends with "Set").


  5. Delete the CRecordset member variables associated with each of the column names in the proxy table. Do this for every member variable by selecting a "Column Name" and clicking "Delete Variable."


  6. Now click "Update Columns." Choose "Yes" when prompted to save current changes.


  7. Select the data source that has been used during this process and choose OK.


  8. Select the table with the large number of columns and choose OK.


  9. Click "Bind All" to add variables for all of the columns in the new table.


  10. Choose OK to leave ClassWizard.


  11. Edit the implementation file (.CPP) for the CRecordset derived class (the filename should be of the form xxxxSET.CPP).


  12. Change the "GetDefaultSQL" for the CRecordset derived class provided by AppWizard. Change the code to read
    
           return "XXXXX"; 
    where XXXXX is the name of the table with many columns.


  13. Save changes by choosing Save from the File menu.


At this point the code is identical to what AppWizard would have generated had it worked successfully using the table with many columns. Code development may proceed as usual using this code.
FAQ_13. Why do I see the following messages in the Output window for the Visual Workbench when CDatabase::Open() or CRecordset::Open() is called?
Warning: ODBC Success With Info, Driver's SQLSetConnectOption failed
State:IM006,Native:0,Origin:[Microsoft][ODBC DLL]
Driver not capable.
State:IM006,Native:0,Origin:[Microsoft][ODBC DLL]
State:S1C00,Native:0,Origin:[Microsoft][ODBC Single-Tier Driver]
The TRACE() messages shown above occur only if MFC DB Tracing is enabled.

The CDatabase::AllocConnect() function, located in DBCORE.CPP of the MFC sources, contains the following line of code:

   AFX_SQL_SYNC(::SQLSetConnectOption(m_hdbc, SQL_LOGIN_TIMEOUT,
         m_dwLoginTimeout)); 
Because ODBC does not provide a way to determine whether or not an ODBC driver uses a "login time-out," the database classes always try to set this option before trying to connect. If SQLDriverConnect() is called and the driver does not support login time-outs, the above ODBC warning messages will occur.

These are harmless warnings (that is, connecting was successful) and can be ignored. When you see the warnings, keep in mind that the login time-out has no affect on the data source.

Additional query words: 1.50 2.50 kbinf FAQ


Keywords          : kb16bitonly kbDatabase kbMFC kbODBC kbVC 
Version           : 1.50
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: July 8, 1999