OracleDataAdapter.SelectCommand and columns named "TYPE"

OracleDataAdapter.SelectCommand and columns named "TYPE"

Postby magnus » Mon 21 May 2012 12:50

(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/appdev.920/a42525/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.
magnus
 
Posts: 11
Joined: Mon 22 Aug 2011 11:07

Re: OracleDataAdapter.SelectCommand and columns named "TYPE"

Postby Pinturiccio » Wed 23 May 2012 14:48

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.

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

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


magnus wrote: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
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleDataAdapter.SelectCommand and columns named "TYPE"

Postby Pinturiccio » Wed 06 Jun 2012 10:33

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
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleDataAdapter.SelectCommand and columns named "TYPE"

Postby Pinturiccio » Fri 08 Jun 2012 13:43

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


Return to dotConnect for Oracle