Severe pitfall when using OracleCommandBuilder

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Dennis Wanke
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Severe pitfall when using OracleCommandBuilder

Post by Dennis Wanke » Tue 06 May 2014 17:47

If OracleCommandBuilder instance is created using a parameterless constructor, its property Quoted is false and QuotePrefix and QuoteSuffix are both empty. Thus calling QuoteIdentifier() for that instance doesn't actually quote the supplied identifier. When the (unquoted) result is than used for constructing a query or used as a table name for OracleLoader, the error "ORA-00911: invalid character" can be raised. It means the following (very obvious) construct has a severe pitfall:

Code: Select all

new OracleLoader(new OracleCommandBuilder().QuoteIdentifier("_log"));
If, however, a command builder instance is obtained from a provider factory, its property Quoted is true and QuotePrefix/QuoteSuffix are both set. It means the following code will work without errors:

Code: Select all

new OracleLoader(new OracleProviderFactory().CreateCommandBuilder().QuoteIdentifier("_log"));
Please eliminate this pitfall by setting Quoted property as true per default.

By the way: OracleLoader could be more intelligent and fool-proof by quoting its table name itself (if the name is not quoted already). SqlClient.SqlBulkCopy does offer such comfort.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Severe pitfall when using OracleCommandBuilder

Post by Pinturiccio » Thu 08 May 2014 13:50

We will investigate the possibility to change the default value for OracleCommandBuilder.Quoted property.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Severe pitfall when using OracleCommandBuilder

Post by Pinturiccio » Thu 15 May 2014 12:49

Devart dotConnect for Oracle is developed since 2002 and includes lots of legacy solutions. Some behavior aspects can be changed without affecting backward compatibility, and some cannot. Unfortunately, the default value of the OracleCommandBuilder.Quoted property cannot be changed.

Dennis Wanke
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Severe pitfall when using OracleCommandBuilder

Post by Dennis Wanke » Thu 15 May 2014 12:59

Then I would suggest to explicitly document these peculiarities, at least.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Severe pitfall when using OracleCommandBuilder

Post by Pinturiccio » Fri 16 May 2014 14:29

Thank you for your suggestions. We will investigate them, but there is no timeframe at the moment.

Post Reply