OracleDataAdapter.SelectCommand and columns named "TYPE"

[magnus]

(I'm using dotConnect for Oracle version 6.60.283.0.)
(Edit: And the Oracle server I've been testing against is version 10.2.)

I should say up front that I don't have a lot of experience with working directly with ADO.NET, but I think I've discovered an issue related to the OracleDataAdapter class and table columns named "TYPE", although I'm not entirely sure if it's by design or not. I've sent you guys a project that illustrates the problem, but since it's a little complicated I figured I should explain the issue here as well.

To start with I've created a table with the following schema (I don't believe the data types and such should make any difference).

Code: Select all

create table TypeColumnTestTable
(
    pk int primary key,
    type varchar(10) not null
);

I also added a public synonym to ensure that the right table was being accessed.

If I'm not mistaken, the above statement will create a table where the column names are considered to be uppercase. However, if you don't put quotes around the column name, you can reference it using any casing. If you put quotes around the column name, you do have to use the right casing. In other words, the following queries work:

Code: Select all

SELECT type FROM TypeColumnTestTable;
SELECT TYPE FROM TypeColumnTestTable;
SELECT TyPe FROM TypeColumnTestTable;
SELECT "TYPE" FROM TypeColumnTestTable;

...but this one fails:

Code: Select all

SELECT "type" FROM TypeColumnTestTable;

Now I go on to create an OracleDataAdapter and assign a SelectCommand, but let the InsertCommand be handled by an OracleCommandBuilder. Then I call Update(). Roughly speaking:

Code: Select all

// Create an adapter and a command builder.
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommandBuilder commandBuilder = new OracleCommandBuilder(adapter);

// Create a connection, and a command to be used as a SelectCommand.
OracleConnection connection = new OracleConnection(*some connection string*);
OracleCommand command = connection.CreateCommand();
command.CommandText = *insert select statement here*
adapter.SelectCommand = command;

// Imagine some code that creates a data set and adds a new row to it here.

// Call Update on the adapter, passing it the data set.
adapter.Update(*the dataset*);

This is where I'm experiencing issues, depending on the specifics of the CommandText on the SelectCommand. The code runs fine if the SelectStatement is one of the following:

Code: Select all

command.CommandText = "SELECT pk, TYPE FROM TypeColumnTestTable";
command.CommandText = "SELECT pk, \"TYPE\" FROM TypeColumnTestTable";
command.CommandText = "SELECT * FROM TypeColumnTestTable";

Note how this works fine even though "pk" is lowercase. The following statements fail, as is to be expected:

Code: Select all

command.CommandText = "SELECT \"pk\", \"TYPE\" FROM TypeColumnTestTable";
command.CommandText = "SELECT pk, \"type\" FROM TypeColumnTestTable";

What I don't understand is why the following statement also fails:

Code: Select all

command.CommandText = "SELECT pk, type FROM TypeColumnTestTable";

The error I get is the following:

Code: Select all

Devart.Data.Oracle.OracleException was unhandled
  Message=ORA-01400: cannot insert NULL into ("INFOSOFT"."TYPECOLUMNTESTTABLE"."TYPE")
  Source=System.Data
  ErrorCode=-2147467259
  Code=1400
  Offset=0
  StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
       at ColumnNamedTYPEProject.Program.Main(String[] args) in c:\Users\magbakke\Documents\Visual Studio 2010\Projects\ColumnNamedTYPEProject\Program.cs:line 93
  InnerException: 

Using dbMonitor I can see that the generated insert statement is the following:

Code: Select all

INSERT INTO TYPECOLUMNTESTTABLE (PK) VALUES (:p1)

In other words, the "TYPE" column is missing.

As I understand it, "type" is only a reserved keyword in PL/SQL -- not in Oracle in general (http://docs.oracle.com/cd/B10501_01/app ... 25/apb.htm) -- but it still seems like dotConnect is treating it in a special manner for some reason. I've tested that:
(1) The last statement passes if the column is called e.g. "FOO" instead of "TYPE".
(2) The last statement passes if I use System.Data.OracleClient instead of Devart.Data.Oracle.

[Pinturiccio]

The situation when the following code works:

Code: Select all

SELECT type FROM TypeColumnTestTable;
SELECT TYPE FROM TypeColumnTestTable;
SELECT TyPe FROM TypeColumnTestTable;
SELECT "TYPE" FROM TypeColumnTestTable;

and the following one does not work:

Code: Select all

SELECT "type" FROM TypeColumnTestTable;

is a normal behaviour for any Oracle ADO.NET provider.

(1) The last statement passes if the column is called e.g. "FOO" instead of "TYPE".

(2) The last statement passes if I use System.Data.OracleClient instead of Devart.Data.Oracle.

What I don't understand is why the following statement also fails:

Code: Select all

command.CommandText = "SELECT pk, type FROM TypeColumnTestTable";

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Pinturiccio]

We have fixed the bugs with auto-generation of UpdateCommand for OracleDataAdapter with OracleCommandBuilder when column name is "TYPE". We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

The new build of dotConnect for Oracle 7.0.17 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=24290