[MFC/ODBC]: Problem with big tabled, Help!!!
Kevin Yan -- yanlei@public.szptt.net.cn Saturday, February 24, 1996 Environment: VC++ {2.0}/MFC 3.0 / Win95 ODBC driver: ODBC 32bit driver for Access Problem: I need to handel a BIG Access database which has at least 120 fields, 1) I create a CRecordView base on this big table, I got an error "Command execute failed." whenever I change one or two data on the view Editbox and CLICK [NEXT RECORD] icon. 2) I try to use my own code to handel the data, the code like this: ///CMySet is a CRecordset-derived class, contain 120 fields CMySet m_pSet; m_pSet.FiltStr = "MainID = ?"; m_pSet.Open(); m_pSet.Edit(); m_pSet.m_Field1 = "KKKK"; m_pSet.m_Field2 = "bbbb"; TRY { m_pSet->Update(); ::MessageBox(NULL,"record update ok",NULL,MB_OK); } CATCH(CDBException, e) { AfxMessageBox(e->m_strError, MB_ICONEXCLAMATION); } AND_CATCH_ALL(e) { TRACE0("Warning: Failure update \n"); } END_CATCH_ALL .... I got an Error "Query is too complex." when executing Update(). But if I use m_pSet.AddNew() to add a new record, I can Update()then. I check the Msjeterr.hlp, it says "reduce the fields". Using VC++, we can not handel BIG tables??? Some options I miss? I have tried to find answers on VC++/MFC Online books and MFC Faq&A..., but no result. Any help will be appreciated. Kevin
Vincent Mascart -- 100425.1337@compuserve.com Wednesday, February 28, 1996 >From: Kevin Yan >Sent: lundi 26 fevrier 1996 9:47 >To: INTERNET:MFC-L@NETCOM.COM >Subject: [MFC/ODBC]: Problem with big tabled, Help!!! > > Environment: VC++ {2.0}/MFC 3.0 / Win95 > ODBC driver: ODBC 32bit driver for Access > Problem: > I need to handel a BIG Access database which has at least 120 fields, > 1) I create a CRecordView base on this big table, I got an error > "Command execute failed." > whenever I change one or two data on the view Editbox and CLICK [NEXT > RECORD] icon. > .... > > I got an Error "Query is too complex." when executing Update(). But if I >use m_pSet.AddNew() to add a new record, I can Update()then. I check the >Msjeterr.hlp, it says "reduce the fields". Using VC++, we can not handel BIG >tables??? Some options I miss? > > I have tried to find answers on VC++/MFC Online books and MFC Faq&A..., >but no result. > > Any help will be appreciated. > >Kevin Just look in the knowledge base for PSS ID Number: Q125651. For speed, You will find its content below. Vincent Mascart Little Indian sprl 100425.1337@compuserve.com SYMPTOMS ======== When trying to update or delete a record of a CRecordset object, the following message occurs: - With 16-bit MFC database classes and 16-bit ODBC desktop drivers: DBMS: ACCESS, Version: 2.0 Query is too complex. State:S1000[Microsoft][ODBC Microsoft Access 2.0 Driver] - With 32-bit MFC database classes and 32-bit ODBC desktop drivers: DBMS: ACCESS , Version: 2.0 Query is too complex. State:S1000,Native:-3071,Origin:[Microsoft] [ODBC Microsoft Access 2.0 Driver] The examples above show "Microsoft Access 2.0" but could be any of the Microsoft desktop drivers. CAUSE ===== The error occurs when the cursor library is loaded and the recordset retrieved by the CRecordset is opened as a snapshot object that contains more than 40 bound columns. By default, the cursor library is loaded when a CRecordset is opened. MFC snapshots perform positioned updates and deletes by using the ODBC SQL WHERE CURRENT OF syntax. The cursor library changes the WHERE CURRENT OF clause to a full WHERE clause using all the columns in the recordset. For example, the MFC Database classes might create the following SQL statement when updating a record: Update table1 SET column1=? WHERE CURRENT OF XXXXX The cursor library knows which record the application is currently positioned at and converts the WHERE CURRENT OF clause into a WHERE clause that will update the current record only. For example, assume the recordset has three columns. The cursor library changes the SQL statement to: Update table1 SET column1=? WHERE column1=AND column2= AND column3 = Here represents the value of that column before the update is performed. You can see that more than 40 columns in a CRecordset will cause more than 40 AND predicates in the WHERE clause. The helpfile for the ODBC desktop drivers (ODBCJET.HLP) states in the SQL limitations section that no more than 40 AND predicates are supported. Thus, the SQL statement is too complex. NOTE: Long binary columns (those using RFX_LongBinary) are not bound and do not count against the 40-column limit. RESOLUTION ========== To work around this behavior, do one of the following: - Reduce the number of columns in your recordset. If you are using the CRecordset object that AppWizard/ClassWizard created for you, you can reduce the number of columns returned in the recordset by removing record field exchange(RFX) functions from the CRecordset's DoFieldExchange() function. - Use dynasets. See the online books for more information about the differences between snapshots and dynasets. To use dynasets with the 16-bit MFC Database classes, please see the following article in the Microsoft Knowledge Base: ARTICLE-ID: Q124915 TITLE :SAMPLE: Using Dynasets with the 16-Bit MFC Database Classes Special code must be acquired to use dynasets with the 16-bit MFC Database classes. CRecordsets which use a dynaset use the SQLSetPos() ODBC 2.0 functionality to perform positioned updates and deletes. This means the MFC database classes do not need to construct an SQL statement to send to the ODBC driver. A call to the SQLSetPos() function of the driver is all that is necessary to perform the update or delete. STATUS ====== This behavior is by design.
Joe Isaacs -- jisaacs@morgan.com Tuesday, March 26, 1996 Try using ODBC API call SQLSetPos. This will allow you to perform positioned updates and inserts of BIG queries. The problem you are experencing is an ODBC 2.0 limitation. If too many operators(~100 or so) are used(commas used to seperate fields are considered operators) ODBC blows up with the error you are experiencing. Hope this helps! Joseph Isaacs Morgan Stanley On Feb 24, 9:05pm, Kevin Yan wrote: > Subject: [MFC/ODBC]: Problem with big tabled, Help!!! > Environment: VC++ {2.0}/MFC 3.0 / Win95 > ODBC driver: ODBC 32bit driver for Access > Problem: > I need to handel a BIG Access database which has at least 120 fields, > 1) I create a CRecordView base on this big table, I got an error > "Command execute failed." > whenever I change one or two data on the view Editbox and CLICK [NEXT > RECORD] icon. > 2) I try to use my own code to handel the data, the code like this: > ///CMySet is a CRecordset-derived class, contain 120 fields > CMySet m_pSet; > m_pSet.FiltStr = "MainID = ?"; > m_pSet.Open(); > m_pSet.Edit(); > m_pSet.m_Field1 = "KKKK"; > m_pSet.m_Field2 = "bbbb"; > TRY > { > m_pSet->Update(); > ::MessageBox(NULL,"record update ok",NULL,MB_OK); > } > CATCH(CDBException, e) > { > AfxMessageBox(e->m_strError, MB_ICONEXCLAMATION); > } > AND_CATCH_ALL(e) > { > TRACE0("Warning: Failure update \n"); > } > END_CATCH_ALL > .... > I got an Error "Query is too complex." when executing Update(). But if I > use m_pSet.AddNew() to add a new record, I can Update()then. I check the > Msjeterr.hlp, it says "reduce the fields". Using VC++, we can not handel BIG > tables??? Some options I miss? > > I have tried to find answers on VC++/MFC Online books and MFC Faq&A..., > but no result. > > Any help will be appreciated. > > Kevin > > > >-- End of excerpt from Kevin Yan
| Вернуться в корень Архива |