MFC 2.5 Database Class Frequently Asked Questions

• The Microsoft Foundation Classes (MFC), included with:
  • Microsoft Visual C++ for Windows, 16-bit edition, version 1.5

SUMMARY

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:

FAQ INDEX TO QUESTIONS:

-or-

-or-

-or-

-or-

-or-

-or-

-or-

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:

• The "Select a table" dialog displays without any tables and then quickly disappears.



• After selecting a table from the list of tables for a WATCOM SQL data source, a messagebox is displayed with the following error:

  SQLGetInfo on SQL_QUALIFIER_NAME_SEPARATOR failed

• Verify that the ODBC.DLL which comes with VC++ 1.5 is being used rather than the one shipped and installed by WATCOM. Note that, currently, WATCOM SQL version 3.2b has an older ODBC.DLL than Visual C++, version 1.5. This is the cause of the disappearing table list. Future versions of their product may contain newer versions in which case you should not need to use the ODBC.DLL which is supplied with Visual C++. If uncertain about which ODBC.DLL is newer, run the Visual C++ setup again. Select "Custom Installation" and "MFC Database" to tell the setup program to copy over the ODBC.DLL and other drivers. The setup program will only copy over DLLs such as ODBC.DLL if they are newer.



• To avoid the SQLGetInfo error shown above, select two checkboxes, "Microsoft Applications" and "Prevent Driver not Capable Errors", when configuring the WATCOM SQL data source with the ODBC Administrator program.

-or-

-or-

• AppWizard and ClassWizard generate an incorrect GetDefaultSQL() function for an Oracle data source table.



• A stack overflow may occur when connecting to an Oracle data source.

   CString CMyRecordSet::GetDefaultSQL()
   {
       return "Dan@Customer";
   } 

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

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

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.

    myDB.ExecuteSQL("CREATE TABLE OFFICES (OfficeID TEXT(10)"
                    ", OfficeName TEXT(10))") 

-or-

-or-

-or-

• An older version of ODBC.DLL than that which is shipped with Visual C++, version 1.5 is used.



• The ODBCCURS.DLL file cannot be found. This DLL should be located in the \WINDOWS\SYSTEM directory. If the ODBCCURS.DLL is missing, you will see the following message in the Output window when running the application with the debugger when MFC DB Tracing is enabled:

  Cursor library not used. Load failed
  State:IM006,Native:0,Origin:[Microsoft][ODBC DLL]
  State:S1C00,Native:0,Origin:[Microsoft][ODBC Single-Tier Driver]
  State:01000,Native:0,Origin:[Microsoft][ODBC DLL]

File dbcore.cpp, Line 549, Assertion Failed!

   #ifdef _DEBUG
       m_bTransactionPending = TRUE;
   #endif // _DEBUG 

   #ifdef DEBUG
       m_bTransactionPending = FALSE;
   #endif // DEBUG 

-or-

   SQLGetInfo, SQL_ODBC_VER must return > "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. 

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.

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.

   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
   } 

   SELECT name, phonenum from customertable where name = ? 

   Select name, phonenum from customertable where name = 'Dan' 

   Select name, phonenum from customertable where name = 'Dan' and zipcode=97439 

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).

-or-

Not enough memory to complete operation.

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.

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]

   AFX_SQL_SYNC(::SQLSetConnectOption(m_hdbc, SQL_LOGIN_TIMEOUT,
         m_dwLoginTimeout)); 

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