[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
| Вернуться в корень Архива
|