Can not use a normal LeftJoin

ThorstenSt · Post by **ThorstenSt** » Thu 24 Nov 2011 10:17

Hello Devart-Support-Team,

I currently test the dotConnect for Oracle product.
I have a problem with using Joins SQL Statements if a column of a table is declared as a unique one.

I do something like this:

Code: Select all

SELECT 
  SYSER_ID,
  SYSER_ART,
  MODUL.MOD_NAME
FROM SYSTEMEVENTS
LEFT JOIN MODUL ON SYSTEMEREIGNIS.MOD_ID=MODUL.MOD_ID

The constraint type of column "MOD_NAME" in table "MODUL" is declared as UNIQUE.

If I execute the statement above, I get the exception "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
This exception is occured while the MOD_NAME is multiple available in this statement. If I remove the MOD_NAME from the query, it works.

I also tested it with your Oracle-Sample program. The error occurs if I use the sample with "DataSet". If I use the sample "DbDataTable" I didn't get the exception and the query result is correct.

I also use the "Direct-Mode".

Can you help me on this issue?

With kind regards
ThorstenSt

Pinturiccio · Post by **Pinturiccio** » Thu 24 Nov 2011 16:19

We could not reproduce the issue. Could you please send us a DDL/DML script and a small project for reproducing the issue.
Looking forward to your reply.

ThorstenSt · Post by **ThorstenSt** » Fri 25 Nov 2011 09:29

Hello,

I have send you a little project via the contact formular.

...

Pinturiccio · Post by **Pinturiccio** » Fri 25 Nov 2011 14:13

Thank you for the project. The error message you receive is part of the designed behavior. When you use 'MissingSchemaAction.AddWithKey', a schema with constraints is added to the DataTable of your DataSet.

There are 2 constraints in your case:
1. PrimaryKey for SYSER_ID column
2. Unique for MOD_NAME

Your sample fetches data rows with the same values in the MOD_NAME column, so, when you use 'MissingSchemaAction.AddWithKey', you will get an exception, because identical values are not allowed in a column with unique constraint.

ThorstenSt · Post by **ThorstenSt** » Fri 25 Nov 2011 14:20

Hello Pinturiccio,

yes this is correct. But why did it add the unique constraint?

I didn't have this behaviour on SQLClient with MSSql or if I use the .Net Oracle Provider.

How should I solve the problem? This is a normal SQL statement to show data of two tables in one grid?

With kind regards

ThorstenSt

Pinturiccio · Post by **Pinturiccio** » Mon 28 Nov 2011 10:22

When you fetch data via OracleDataAdapter and DataSet, only one DataTable will be created in your DataSet for the fetched data. It will contain 4 column SYSER_ID, MOD_ID, SYSER_BESCHREIBUNG and MOD_NAME.
There are 2 ways:
1. By default a DataTable in a DataSet does not have any constraints. Fetching will work fine in this case (Default is MissingSchemaAction.Add).
2. When you use 'MissingSchemaAction.AddWithKey', constraints are added to the DataTable. Thus, you have a unique constraint for the MOD_NAME column, and you will receive exception during the fetching.

If you need a PrimaryKey constraint for the SYSER_ID column you have to add it manually.

Code: Select all

UniqueConstraint uniconstr = new UniqueConstraint(ds.Tables[0].Columns["SYSER_ID"], true);
ds.Tables[0].Constraints.Add(uniconstr);

Then you will have the PrimaryKey constraint for SYSER_ID column and no Unique constraint for the MOD_NAME column.

ThorstenSt · Post by **ThorstenSt** » Mon 28 Nov 2011 13:41

Hello,

my name is Raul Rodriguez. My team is testing your Oracle drivers for our software product and we had some problems with it. One of them is this case from my mate Thorsten.

I will try to explain what the problem is. If you use the standard Microsoft ADO.NET drivers and specify the MissingSchemaAction.AddWithKey you will get constraints added to the DataSet when invoking the Fill method from the DataAdapter. The difference is that with the Microsoft (and the original Oracle) drivers you get the primary keys added to the constraints, but not the alternate keys.

Lets say you have got one table A with the fields MainID (primary key), Number (alternate key) and Load, and another table B with the fields ID (primary key), MainID (foreign key to the A table) and ChildInfo.

If you do the following select:

SELECT A.MainID, A.Number, B.ChildInfo FROM A JOIN B ON A.MainID = B.MainID

you will get an exception when loading the DataSet if the MainID is repeated in more than one record. This is because the A.MainID is defined as primary key and added as constraint to the DataSet.The solution is to write the select as

SELECT B.MainID, A.Number, B.ChildInfo FROM A JOIN B ON A.MainID = B.MainID

You use the foreign key field from the child table. Now you don't get any exceptions as B.MainID isn't a primary key. Great. It is a problem as a developer but at least you know the rule.

Our problem with your driver is that if we use the statement above we get A.Number added to the constraints too! A.Number is an alternate key and we cannot use any other field instead.

Both Microsoft and Oracle drivers didn't add the alternate keys to the constraints.

This is a critical issue for us. If we somehow solve this problem and another one we had I could order a Site License of your Professional or Developer Edition. The budget is approved and I just need to say that we have no more issues with the drivers.

We need your help. Is there something we could do?

Thanks in advance
Raul Rodriguez
Team Leader
Babtec Informationssysteme GmbH

Pinturiccio · Post by **Pinturiccio** » Tue 29 Nov 2011 07:14

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

ThorstenSt · Post by **ThorstenSt** » Tue 29 Nov 2011 10:44

Thank you very much!! These are indeed good news!

The other problem I mentioned in my post is already created as another topic in this forum an has got the title "Performance problem with MissingSchemaAction.AddWithKey".

These are the two problems we have got with the drivers. Everything else is looking great.

Kind regards,
Raul Rodriguez
Team Leader
Babtec Informationssysteme GmbH

ThorstenSt · Post by **ThorstenSt** » Tue 06 Dec 2011 09:06

Could you please provide us some update about the state of this ticket?

We are evaluating your drivers for our future development and we installed the 30 days trial version. My manager is asking me for a decision. Do we use the original Oracle drivers or the Devart ones?

I would prefer to use yours as I like many things in your approach (for example the great type mapping system for native oracle types / .NET types).

The problem is that the issue in this ticket is a showstopper. We cannot continue evaluating the drivers and our deadlines are getting closer.

Kind regards,
Raul Rodriguez

Pinturiccio · Post by **Pinturiccio** » Tue 06 Dec 2011 13:52

We have fixed the bug with adding a unique constraint when fetching data more then from one table and using MissingSchemaAction.AddWithKey. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Additionally, we will extend the trial period of dotConnect for Oracle for you.

Pinturiccio · Post by **Pinturiccio** » Fri 09 Dec 2011 15:34

We have answered you by e-mail