Re: ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL

Hi,

Can you enlighten me; you appear to be saying there are three different ways to run queries against Oracle;

a) System.Data.OracleClient
b) Something called ODP.NET?
c) What ever you were using before??

Can you briefly explain these three with merits/limitations?

Valkyrie-MT wrote:
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 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 becuase 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. They clearly are not interested in maintaining it.

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


--
Gerry Hickman (London UK)
.

Relevant Pages

  • Re: ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL
    ... Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle ... When trying to use the Query Builder with Oracle, ...
    (microsoft.public.vsnet.general)
  • Re: ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL
    ... For what it's worth, none of this surprises me; Microsoft create all kinds of "providers" based on fashions and fads of the day, the press release reads "fully supports Oracle", then they lose interest and you are stuck with something that doesn't work anymore. ... Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET ... 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. ...
    (microsoft.public.vsnet.general)
  • Re: SQL Injection
    ... > I am currently pen-testing a web app and I am stuck in trying to execute ... two queries sequentially in Oracle. ... You won't have luck with this in Oracle. ... the end query build by a cgi-script being ...
    (Pen-Test)
  • ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL
    ... it will work fine using the conversion wizard, but if you try to view the SQL ... go to generate the command objects with the dreaded "Syntax Error: ... large projects that use Oracle table joins. ... When trying to use the Query Builder with Oracle, ...
    (microsoft.public.data.ado)
  • ORACLE CLIENT USERS BEWARE: Bug in Query Builder breaks SQL
    ... it will work fine using the conversion wizard, but if you try to view the SQL ... go to generate the command objects with the dreaded "Syntax Error: ... large projects that use Oracle table joins. ... When trying to use the Query Builder with Oracle, ...
    (microsoft.public.vsnet.general)