ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL

VOTE: If this is an important issue for you too, Mark this post as helpful.
(Click the 'Yes' button if you are using the browser based reader)

Warning, using the Query Builder in VS2005 to join two Oracle (9i or 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
it will work fine 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 statement, install Oracle Developer Tools for .NET and don't touch the
integrated MS Oracle Provider. If you think this is important mark this post
as helpful!

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
.