Microsoft KB Archive/114938

{|
 * width="100%"|

MFC 2.5 Database Class Frequently Asked Questions

 * }

Q114938

-

The information in this article applies to:


 * The Microsoft Foundation Classes (MFC), included with:
 * Microsoft Visual C++, version 1.5

-

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:

 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



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:


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

-

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:


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

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


 * 1) include 

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

void CMyRecordView::DoDataExchange(CDataExchange* pDX) {   CRecordView::DoDataExchange(pDX); //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):

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.
 * 1) Open the ODBC.INI file.
 * 2) Find the section marked by [ ] where 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.

-

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:

 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]

</li></ul>

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:

<pre class="CODESAMP">  #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:

<pre class="CODESAMP">  #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:

<pre class="FIXEDTEXT">  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:

<pre class="CODESAMP">  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:

<pre class="CODESAMP">  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:

<pre class="CODESAMP">  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:

<pre class="CODESAMP">  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:

<ol> Identify another table, the proxy, which contains fewer columns in the same data source, or create one.</li> 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.</li> Open ClassWizard (CTRL+W) and switch to the "Member Variables" tab.</li> For the "Class Name," select the CRecordset derived class (it should be a name that ends with "Set").</li> 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."</li> Now click "Update Columns." Choose "Yes" when prompted to save current changes.</li> Select the data source that has been used during this process and choose OK.</li> Select the table with the large number of columns and choose OK.</li> Click "Bind All" to add variables for all of the columns in the new table.</li> Choose OK to leave ClassWizard.</li> Edit the implementation file (.CPP) for the CRecordset derived class (the filename should be of the form xxxxSET.CPP).</li>  Change the "GetDefaultSQL" for the CRecordset derived class provided by AppWizard. Change the code to read <pre class="CODESAMP">      return "XXXXX"; where XXXXX is the name of the table with many columns. </li> Save changes by choosing Save from the File menu.</li></ol>

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:

<pre class="CODESAMP">  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

Issue type :

Technology : kbAudDeveloper kbMFC