GetSchemaTable bug with inline views

GetSchemaTable bug with inline views

Postby Haddyb » Fri 21 May 2010 14:47

Hi there!

There is a need in our applications to use DbReader.GetSchemaTable to find the BaseTableName and BaseColumnName of a complex query in order to enforce some security policies that are defined in our application only. There is a know bug in .NET provider for Oracle, it cannot return this information in queries that use inline views or some other more complex queries.

We need a provider capable of returning the complete schema information for virtually any query. I've downloaded and tested Devart's provider for oracle and although it does not raise an exception when we call GetSchemaTable on a complex query (as DbProviderFactory does) it still can't return the complete schema information. BaseTableName and BaseColumnName comes blank for every column in the query.

It is not possible to simplify our queries as they are created by our users and they claim that "in sql plus they work fine".

Can dotConnect solve this problem? Is there any other way to get schema information aside of UniDataReader.GetSchemaTable ()? If not, is there any plans to solve this situation?

Below is an example of a simple query in witch GetSchemaTable can't return the desired information:

"SELECT NOME, CI FROM (SELECT NOME, CI, ENDERECO FROM FUNCIONARIO) A"

Thanks in advance.
Haddyb
 
Posts: 1
Joined: Fri 21 May 2010 14:08

Postby StanislavK » Wed 26 May 2010 11:06

dotConnect for Oracle performs simplified parsing of SQL commands only, and for nested queries the base table cannot be determined. This is the standard ADO.NET data provider behaviour; for example, System.Data.SqlClient doesn't retrieve base table name either. We can only recommend you to parse statements independently.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle