Microsoft KB Archive/186380

= BUG: Error When Using Table Aliases with Oracle ODBC Driver =

Article ID: 186380

Article Last Modified on 12/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.5
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q186380



SYMPTOMS
When you try to use table aliases in SELECT statements with the Microsoft Oracle ODBC driver and you are using the Keyset cursor type, you receive the following error:

[Microsoft][ODBC driver for Oracle]Cannot use Keyset-driven cursor on join, with union, intersect or minus or on read only result set"



CAUSE
When the driver re-writes the SQL statement to send to the Oracle server it doesn't create the correct syntax.

Internally, Oracle returns the following error message:

ORA-00964: table name not in FROM list

In turn, The Microsoft Oracle ODBC driver maps that error message to the message listed in the SYMPTOMS section of this article.



RESOLUTION
The solution depends on the nature of the application. If the application needs to use Keyset cursors, it may not use table aliases. If the application must use table aliases, the cursor type must be changed to FORWARD-ONLY or STATIC.



STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.



Steps to Reproduce Behavior using ODBCTest
 Make a full connection to an Oracle DataSource. ODBC Behavior is set to 2.0, and Cursor Library is set to "Use Driver". From the Attr menu select SQLSetStmtAttr. Set the attribute SQL_ATTR_CURSOR_TYPE=6 to SQL_CURSOR_KEYSET_DRIVEN=1, and then click OK. Set the attribute SQL_ATTR_CONCURRENCY=7 to SQL_CONCUR_VALUES=4, and then click OK.  Type in the following statement in the "henv" window. SELECT * FROM demo.customer myalias </li> Click on the toolbar button for SQLExecDirect (the button with exclamation point), or select the Stmt menu and chose SQLExecDirect. The error message appears.</li></ol>

NOTE: This error occurs regardless of the program's front end. This error has been reported in Visual Basic and Visual C++.

Additional query words: kbDSupport kbOracle kbDatabase kbODBC kbDriver odbcOracle

Keywords: kbbug kboracle kbdatabase kbdriver kbpending KB186380

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.