Microsoft KB Archive/190934

= FIX: Degree of Derived Table Error When Updating Oracle Data =

Article ID: 190934

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.0

-



This article was previously published under Q190934



SYMPTOMS
When trying to UPDATE a recordset with the Microsoft Foundation Classes (MFC) ODBC classes you may get the following error:

[Microsoft][ODBC driver for Oracle]Degree of derived table does not match column list.



CAUSE
The Microsoft Oracle ODBC driver is not looking for quoted column names when trying to match them for parsed column names.



RESOLUTION
The new Microsoft Oracle ODBC driver version 2.573.2927 or higher fixes this problem. The latest Microsoft Oracle ODBC can be downloaded as part of the Microsoft Data Access Components from the following Web address:

http://msdn.microsoft.com/dataaccess



STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug has been corrected in Microsoft's Oracle ODBC driver, version 2.573.2927 or higher.



MORE INFORMATION
MFC ODBC is using SQLSetPos for the update. This error has also been seen with ActiveX Data Objects (ADO) for the same reasons.

Steps to Reproduce Behavior
Run the three following DDL statements in SQL*Plus or ODBCTest against the Oracle server: CREATE TABLE zeric (CNTR NUMBER(4),                      FNAME VARCHAR2(10),                       LNAME VARCHAR2(20)); CREATE INDEX zeric_cntr ON zeric (CNTR); INSERT INTO zeric VALUES (1, 'Eric', 'Burgess'); The above code is written for SQL*Plus. If you want to do this in ODBCTest, remove the semicolons.

In ODBCTest, run the following code. Only the parameters that are manually selected are shown here. Leave all other parameters alone. 1. Full Connect: Full Connect(Use Driver)

2. SQLSetStmtAttr: Attribute = SQL_ATTR_CURSOR_TYPE=6 ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1

3. SQLSetStmtAttr: Attribute = SQL_ATTR_CONCURRENCY=7 ValuePtr = SQL_CONCUR_VALUES=4

4. Type the following SQL Statement in the Input window. Make sure to use the double quotes around the field names.

SELECT "CNTR", "FNAME", "LNAME" FROM "ZERIC"

5. Call SQLPrepare.

6. Call SQLExecute.

7. Select Bind Col All.

8. Select SQLExtendedFetch: FetchOrientation = SQL_FETCH_NEXT=1 FetchOffset = 1

9. Select SQLExtendedFetch: FetchOrientation = SQL_FETCH_FIRST=2 FetchOffset = 1

10. Change the FNAME field contents. This should be done through the grid that opens.

11. Call SQLSetPos: Operation = SQL_UPDATE=2, LockType = SQL_LOCK_NO_CHANGE=0 SQLSetPos call causes the error.

SqlState = "21S02" MessageText = "[Microsoft][ODBC driver for Oracle]Degree of derived table does not match column list"

NOTE: The classes in Visual C++ MFC ODBC call these ODBC APIs when they attempt to update a record, and that causes the same error message.

