Severe pitfall when using OracleCommandBuilder

Severe pitfall when using OracleCommandBuilder

Postby 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.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Severe pitfall when using OracleCommandBuilder

Postby 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: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Severe pitfall when using OracleCommandBuilder

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: Severe pitfall when using OracleCommandBuilder

Postby Dennis Wanke » Thu 15 May 2014 12:59

Then I would suggest to explicitly document these peculiarities, at least.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Severe pitfall when using OracleCommandBuilder

Postby Pinturiccio » Fri 16 May 2014 14:29

Thank you for your suggestions. We will investigate them, but there is no timeframe at the moment.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle