OracleDataReader: GetName(...) and same field-names

[goethals.f]

Hello,

Today I had some bug-fixing to do concerning some extension-method I had written which uses under the hood the devart OracleDataReader.

Maybe my issue can be helpfull for others...


My extensions method were field-data-extract helpers and were originally based on the "fieldname". Everything ok.

I had these extended whith the same , but were based on the "fieldindex" instead of the name. Internally I was "lazy' and fetched first the fieldname of that index, and then just execute my original extensionmethod with that fieldname. Everything ok (I thought, but it will be the source of my bug)

But now, one of my collegues came with a bug when he was using the "fieldindex" related extension methods in combination with the following SQL (stripped down to the essential)

select a.ID ,b.ID from Table1 a, Table2 b where b.ID_Table1 = a.ID


Fetching this data based on index 0 and index 1 should work. But because my helper-methods both first fetch the fieldname

--> index 0 mapped to fieldname ID, and get data based on field ID == column 0
--> index 1 mapped to fieldname ID, and get data based on field ID == column 0

So, in short I was lost the data of the second field.


A quick solution was to rewrite sql and have unique field-names

select a.ID as AID ,b.ID as BID from Table1 a, Table2 b where b.ID_Table1 = a.ID


In the long way, I rewrote all my helper-extensions methods. so everything back OK.


But the issues took my attention.
Primary because when executing the SQL in vs.NET 2010 with oradeveloper tools --> the field names where name: ID, ID1, ID2 , and even stranger (the column ID2 was added , and had all "null" values)
But there was no same field-name

And when I debugged and took al look in the dotconnect internals, I found indeed some dictionary object where there was a maaping defined between the fieldnames and the fieldindexes. And indeed, that dictionary had one item less the then fieldcount property.

And when using index --> get fieldname --> get data by fieldname, both my field-indexes were at the end mapped to the same field.


I personally think it is indeed NOT a good way of writing that SQL (but maybe in some ways it is auto-generated or so... Most of the time i also work in a fieldname way instead of index-based way.

Maybe internally there could be something changed so that it reflects more the way of oradeveloper tools (ID, ID1, ID2 --> but then without the ID2 extra-null-column)


So, all this is not a bug or criticism on the dev-team. In fact, keep up the good work !!!
Just some info-sharing Instead.


Fred

[Shalex]

Fred, thank you for sharing your experience.
1.

My extensions method were field-data-extract helpers and were originally based on the "fieldname". Everything ok.

I had these extended whith the same , but were based on the "fieldindex" instead of the name. Internally I was "lazy' and fetched first the fieldname of that index, and then just execute my original extensionmethod with that fieldname. Everything ok (I thought, but it will be the source of my bug)

But now, one of my collegues came with a bug when he was using the "fieldindex" related extension methods in combination with the following SQL (stripped down to the essential)

select a.ID ,b.ID from Table1 a, Table2 b where b.ID_Table1 = a.ID


Fetching this data based on index 0 and index 1 should work. But because my helper-methods both first fetch the fieldname

--> index 0 mapped to fieldname ID, and get data based on field ID == column 0
--> index 1 mapped to fieldname ID, and get data based on field ID == column 0

So, in short I was lost the data of the second field.

Could you please send us a small test project with the corresponding DDL/DML script? Also please tell us your current version of dotConnect for Oracle (x.xx.xxx): the Tools > Oracle > About menu of VS. We will debug your code at our side and notify you about the results.

2.

But the issues took my attention.
Primary because when executing the SQL in vs.NET 2010 with oradeveloper tools --> the field names where name: ID, ID1, ID2 , and even stranger (the column ID2 was added , and had all "null" values)
But there was no same field-name.

I have executed the following SQL in OraDeveloper Tools for VS 2010 v 2.60.176. There is no additional ID2 in my case.

Code: Select all

--drop table table2;
--drop table table1;

CREATE TABLE TABLE1 (
  ID NUMBER(38),
  MYDATA VARCHAR2(20),
  CONSTRAINT PK_TABLE1 PRIMARY KEY (ID));

CREATE TABLE TABLE2 (
  ID NUMBER(38),
  MYDATA VARCHAR2(20),
  ID_TABLE1 NUMBER(38),
  CONSTRAINT FK_TABLE21 FOREIGN KEY (ID_TABLE1)
    REFERENCES ALEXSH.TABLE1(ID),
  CONSTRAINT PK_TABLE2 PRIMARY KEY (ID));

insert into TABLE1 values (1, 'some data');
insert into TABLE2 values (1, 'some data', 1);

select a.ID ,b.ID from table1 a, table2 b where b.ID_Table1 = a.ID;

Please tell us the version of your OraDeveloper Tools for VS 2010 and modify the script above if necessary.

[goethals.f]

Hello,

I have tested the SQL-statements in my DB, and when executing the SQL "select a.ID ,b.ID from table1 a, table2 b where b.ID_Table1 = a.ID;"

I still get the extra ID2 column with null values.

I also tested the SQL "SELECT a.id ,a.MYDATA ,b.ID, b.MYDATA, b.ID_TABLE1 from TABLE1 a, TABLE2 b where b.ID_TABLE1 = a.ID;" and there I get an extra ID2 and MYDATA2 column with null values...

The fieldnames shown in the grid are: ID, MYDATA, ID1, MYDATA1, ID_TABLE1, ID2, MYDATA2.

I have the v2.60.176 of oradeveloper, and use VS.NET 2010 proffesional.
I tested the SQL with a connection with oracle10 client, oracle8 client, and direct-mode. They all result in those extra columns...
So it seems we have the same software, so something else can be the problem (may be my regional / language settings, ...)


Executing SQL "SELECT a.id as AID ,a.MYDATA ,b.ID, b.MYDATA, b.ID_TABLE1 from TABLE1 a, TABLE2 b where b.ID_TABLE1 = a.ID" , where the first ID is AID, results in the fieldnames (only one null-column added)

AID, MYDATA, ID, MYDATA1, ID_TABLE1, ID2, MYDATA2.


Concerning the demo-project of the first case, I will try to do it asap.


Fred

[goethals.f]

Hi again,

I tried following c# code (VS.NET 2010, Dotconnect v6.0.69.0, oracle10 client)

In your provided SQL-script, i first changed in table2, the value of ID --> set it to 150 (so b.id is different value of a.id, which is allowed)

Table1-record: 1, 'some data'
Table2-record: 150, 'some data', 1

When executing the following code:

Code: Select all

try
            {
                string tmpConnectionString = "User Id=***;Password=***;Server=***;Home=OraClient10g_home1;Pooling=true";
                string tmpSQL = "SELECT a.id ,a.MYDATA ,b.ID, b.MYDATA, b.ID_TABLE1 from TABLE1 a, TABLE2 b where b.ID_TABLE1 = a.ID";

                string tmpFieldName = string.Empty;
                object tmpValueByIndex = null;
                object tmpValueByName = null;

                Devart.Data.Oracle.OracleConnection tmpOracleConnection = new Devart.Data.Oracle.OracleConnection(tmpConnectionString);
                tmpOracleConnection.Open();

                Devart.Data.Oracle.OracleCommand tmpOracleCommand = new Devart.Data.Oracle.OracleCommand(tmpSQL, tmpOracleConnection);
                Devart.Data.Oracle.OracleDataReader tmpOracleDataReader = tmpOracleCommand.ExecuteReader();

                while (tmpOracleDataReader.Read())
                {

                    for (int i = 0; i  return value of first field...


Fieldname: ID, ValueByIndex: 1, ValueByName: 1
Fieldname: MYDATA, ValueByIndex: some data, ValueByName: some data
Fieldname: ID, [b]ValueByIndex: 150, ValueByName: 1[/b]
Fieldname: MYDATA, ValueByIndex: some data, ValueByName: some data
Fieldname: ID_TABLE1, ValueByIndex: 1, ValueByName: 1


as a double-check, I changed the 'some data' in table2 to 'some data2', which results obviously in the following (both field3 and 4 are wrong)

Fieldname: ID, ValueByIndex: 1, ValueByName: 1
Fieldname: MYDATA, ValueByIndex: some data, ValueByName: some data
Fieldname: ID, [b]ValueByIndex: 150, ValueByName: 1[/b]
Fieldname: MYDATA, [b]ValueByIndex: some data2, ValueByName: some data[/b]
Fieldname: ID_TABLE1, ValueByIndex: 1, ValueByName: 1


At the end, I could agree that all this is occuring because of using some 'strange' SQL, and indeed everything is OK when using unique fieldnames etc...

It all seems logic to me, because asking the value of field "ID" is indeed not 100% clear, because it could mean a.id and b.id --> so when there is indeed some internal-dictonary-mapping between names and indexes, this would lead to two indexes...

and just using internally in that dictonary-mapping the names of a.id and b.id --> when in code then the field "ID" is asked, it would return no value (which is maybe a better solution than given the arbitrary choosen value)


I will surely discuss this once more with our developers, that for the best results, they always specify unique field-names/aliases in their SQL, and use that aliased-name for fetching, or the index.


Thx

Fred

[goethals.f]

Hi,

some extra info:

I just checked with a collegue which has 2.60.172 as the oradeveloper version, and there the extra "null"-columns are not visible...

On my pc the 2.60.176 shows the extra null-column....

[Shalex]

Concerning the issue with dotConnect for Oracle.
We have reproduced the described behaviour with your code. We will investigate it and notify you about the results as soon as possible.

Concerning the issue with OraDeveloper Tools for Visual Studio.
I have forwarded your request to the OraDeveloper Tools support team. Could you please specify the version of your Oracle Server, and tell us if the problem persists on your collegue's machine after updating to OraDeveloper Tools for Visual Studio v 2.60.176?

[goethals.f]

Concerning the issue with dotConnect for Oracle.
We have reproduced the described behaviour with your code. We will investigate it and notify you about the results as soon as possible.

Concerning the issue with OraDeveloper Tools for Visual Studio.
I have forwarded your request to the OraDeveloper Tools support team. Could you please specify the version of your Oracle Server, and tell us if the problem persists on your collegue's machine after updating to OraDeveloper Tools for Visual Studio v 2.60.176?

My current system (the "problematic" one), is windowsXP, VS.NET 2010 prof, and DeveloperTools 2.60.176

I have tested on a second pc: Windows 7, VS.NET 2010, DeveloperTools 2.60.176 and there I don't see the extra null-column...


But some more good news: I remember that I have changed some settings once in VS.NET, and I have found the setting which causes this strange behaviour.

If you enable in VS.NET --> tools --> options --> Devart Developer tools --> Data Editor: And enable the first checkbox "Show data view for every SQL document", Then you gave the extra null-columns.

Default it is not checked and that way it was working on my second pc and the collegues, If I enable the option on my windows 7 pc --> I have the extra columns.

The option itself is usefull for me, because each SQL-stament has his own data-result grid instead of one shared data-result grid.

[Shalex]

Concerning the issue with dotConnect for Oracle.
This is a designed behaviour (you can also reproduce it with ODP.NET and System.Data.OracleClient). Any DataReader (and OracleDataReader) doesn't rename columns which are returned from database. If your query is "SELECT 1 AS N, 2 AS N, 3 AS N, 4 AS N FROM DUAL", you will get four columns with the "N" name each. If you execute "SELECT 1 AS N1, 2 AS N2, 3 AS N3, 4 AS N4 FROM DUAL", every column will have a unique name (N1,N2,...).

It is necessary to set unique aliases if you want to work with columns by names in case of table joins and expression columns.

Please use the following query to fix the issue:

Code: Select all

"SELECT a.id ,a.MYDATA ,b.ID as ID2, b.MYDATA as MYDATA2, b.ID_TABLE1 from TABLE1 a, TABLE2 b where b.ID_TABLE1 = a.ID"

instead of

Code: Select all

"SELECT a.id ,a.MYDATA ,b.ID, b.MYDATA, b.ID_TABLE1 from TABLE1 a, TABLE2 b where b.ID_TABLE1 = a.ID"

Concerning the issue with OraDeveloper Tools for Visual Studio.
We have reproduced the problem. We will post here when it is fixed.