This is a reposting from another Forum. I felt it would be appropriate here. If you are using Visual Studio 2005 with the Microsoft integrated Oracle Provider (System.Data.OracleClient) and you have an Oracle 9i or 10g database, keep reading:
Warning, using the Query Builder in VS2005 to join two Oracle (9i and 10g) tables is broken and will not be fixed. If you have to move projects from VS2003 that include such SQL queries, you have to fix it all by hand (by creating views or switching to ODP.NET completely). The scary thing is that your solution will work fine after using the conversion wizard, but if you try to view the SQL Query, you will immediately break it without another click because it will immediately reformat the SQL using syntax that the MS OracleClient provider does not support! To be even more malicious, your query will actually work if you test it in Query Builder, but the wizard will fail everytime when you go to generate the command objects with the dreaded "Syntax Error: Expecting '.', identifier or quoted identifier."!
Well, unfortunately Microsoft is not going to fix this. I partly blame myself. I saw this bug in the Betas, but was too lazy to submit the issue. I still think this is incredibly important, but I can't seem to convince some at Microsoft of this. I can't port my tools from VS2003 (which contain joins) now without an extensive refactor! Doing joins in VS2003 worked and now it's broken in VS2005. I think I should be pretty pissed. I have 3 large projects that use Oracle table joins. Since I have to move these to VS2005 and I can't use the MS System.Data.OracleClient provider, I will have to rewrite all of this using Oracle's ODP.NET Provider -- forcing me to throw out all of my DataAdapter objects and rewrite by hand!!! I can't believe this!
Words of Advice... if you believe you will have to join two tables in your select statements, install Oracle Developer Tools for .NET and don't touch the integrated MS Oracle Provider.
Here are the details from Microsoft on this:
PROBLEM:
___________________
When trying to use the Query Builder with Oracle, queries that create INNER JOIN syntax result in an error in Query Builder when trying to generate your schemas.
CAUSE:
___________________
The OracleClient FillSchema method that is called by the Query Builder cannot handle INNER JOIN syntax
RESOLUTION:
___________________
When we updated the Query Builder in VS2005 to generate INNER JOIN syntax when the underlying database can handle it, we did not update the OracleClient to handle the INNER JOIN. Therefore, when the Query Builder tries to call the FillSchema method to generate the INSERT, UPDATE, and DELETE statements, it encounters an error. You would also see this error if you called the FillSchema method directly in code.
Unfortunately, the risk associated with modifying OracleClient.FillSchema to handle the INNER JOIN syntax is too risky to do at this time given, so this change has been postponed until at least Orcas. We are also working with Oracle to try to get them to modify their database to return the schema information automatically instead of forcing the provider to interpolate the schema based on the query. This would eliminate the need for the FillSchema method.
"Kjetil" wrote:
Thanks a lot for the input.
I really hope the MS will do something about this. If it is not possible to
work with joined queries at all, I think this is a very serious issue.
Another possible woraround may be to load the single tables and do all the
joining on the client side using the ADO.net relations. Depending on what you
want to do, this may also be inefficient and cumbersome, of course.
"Valkyrie-MT" wrote:
Finally! I thought I was the only person who had ever seen this problem!
Shortly after writing this e-mail I started working with Microsoft to
reproduce the issue and after some work, I was able to repeat it and so were
they. I was told the "dev team" would be consulted on this issue and they
may release a hotfix or wait for a serivice pack or not fix it at all if it
isn't considered serious.
The only semi-workable work around for this issue that I have found is to
create a View for every query join you want to do. This is extremely
cumbersome for me since it is very difficult for me to change the views since
I don't have permission to change them.
Right now there is no working tool in VS2005 to do Oracle table joins via a
graphical interface. This is very disappointing to me. This is fundamental
to using VS2005 for me and it's such a waste. The query builder does such a
nice job with joins and building such queries and it's all for nothing
because of this issue...
I am hoping either MS will recognize the importance of this or Oracle will
pick up the slack with their Oracle Developer Tools for VS2005 which should
be released VERY soon.
http://www.oracle.com/technology/tech/dotnet/tools/index.html
Either solution would work for me, but right now, I'm using a lot of
views... and once this is all fixed, I'll have to go back and clean up the
mess and remove all this view stuff...
Oh well... that's the cutting edge for ya...
"Kjetil" wrote:
I have the same problem. Can anyone suggest a solution, or is it impossible
to work with the TableAdapter and joined queries?
Kjetil
"Valkyrie-MT" wrote:
I am using Visual Studio 2005 Release Version 2.0.50727.
Whenever I try to develop a query (with a join) using the Query Builder (via
the DataAdapter or TableAdapter Wizards) the SQL generated works and returns
rows, but when the wizard goes off to generate the Select Commands, I get a
syntax Error every time. I am using System.Data.OracleClient to query an
Oracle Database. I have also reproduced this behavior on a coworker's
computer.
Instead of giving me the happy check marks stating that the Select Commands
have been generated, I get this:
Window Title: TableAdapter Configuration Wizard
The wizard detected the following problems when configuring the
TableAdapter: "Fill":
Details:
(A little yellow warning triangle) Generated Select statement.
Syntax Error: Expecting '.', identifier or quoted identifier.
-----------------------------
I generated this simple query (using a join) using the Query Builder UI and
without typing any SQL. The Query Generates this (which works and returns
the correct rows when executed), but fails when the commands get generated by
the wizard:
SELECT MATRIX.EA_RT_ALIGNMENT.*, MATRIX.EA_MAP.*
FROM MATRIX.EA_MAP INNER JOIN
MATRIX.EA_RT_ALIGNMENT ON MATRIX.EA_MAP.EAMP_MAP_ID =
MATRIX.EA_RT_ALIGNMENT.ERTA_EXPERIMENT_MAP_ID
If I back up in the wizard to the "Enter SQL Statement" window, and change
the "INNER JOIN" statement to a "WHERE" clause like this:
SELECT MATRIX.EA_RT_ALIGNMENT.*, MATRIX.EA_MAP.*
FROM MATRIX.EA_MAP, MATRIX.EA_RT_ALIGNMENT
WHERE
MATRIX.EA_MAP.EAMP_MAP_ID = MATRIX.EA_RT_ALIGNMENT.ERTA_EXPERIMENT_MAP_ID
Then if I continue from that point (without opening the Query Builder), the
commands are generated successfully. So, the Query Builder is generating
queries that are not being accepted by whatever is checking the syntax. The
syntax is valid, so I believe the code in VS 2005 that is checking the syntax
here contains a bug.
Overall this is exceptionally inconvenient because, it breaks my SQL if I
ever open the Query Builder.
Help,
Valkyrie-MT