UpdateCommand generated updates more fields than required

UpdateCommand generated updates more fields than required

Postby vnaranjo » Fri 24 Sep 2010 16:24

Hi,

I've been using the oracle provider and I discovered that when I use the CommandBuilder with an Adapter that contains a select statement like this:

Code: Select all
Select COLUMN2 From TABLE1 where COLUMN1 = Value


Where COLUMN1 is the Primary Key of the Table, and the Column2 is just another column in the table (Not a Primary key or a Foreign Key), then the Generated UpdateCommand is something like this:

Code: Select all
UPDATE TABLE1
SET COLUMN2 = :p1
WHERE ((COLUMN2 = :p2))


But as you can see this is not the command I was expecting, because it is going to update all the records that have the same value that the record I was trying to update. For example if the COLUMN2 in the record I'm trying to update has the value 1, and I'm going to change it 2, then all the records wit a 1 value in the COLUMN2 is going to be changed to 2.

This is not a desired behavior at all and it is dangerous because it can be updating thousands of records without any warning. This is wrong, and might be causing several bugs that anyone will notice.

You can easily replicate the problem with the folowing code:

Code: Select all
using (OracleConnection connection = new OracleConnection(ConnectionString))
{

   connection.Open();
   DataSet ds = new DataSet();
   OracleCommand command = new OracleCommand("Select COLUMN2 From TABLE1 where COLUMN1 = Value", connection);
   OracleDataAdapter adapter = new OracleDataAdapter(command);
   adapter.Fill(ds);
   OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
   OracleCommand UpdateCommand = builder.GetUpdateCommand();
}


We are using at this moment the .NET's Oracle Provider and we are trying to move to this one. If I use the .NET Oracle provider when I call the GetUpdateCommand with that Select Statment, then I get an exception ("Dynamic SQL generation failed. No key information found"), and it doesn't matter, because we have code to manage that. The problem is that we have thousands of queries and potentially this is happening in some of them, this is a hard issue to track and for this getting the exception is the right behavior.
vnaranjo
 
Posts: 4
Joined: Fri 24 Sep 2010 00:46

Postby StanislavK » Tue 28 Sep 2010 10:14

This is the designed behaviour, dotConnect for Oracle allows creating update commands without primary key. However, we will investigate the possibility of implementing a flag specifying whether such commands will be generated or an exception will be thrown. We will inform you about the results of our investigation here.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Tue 28 Dec 2010 14:57

We have changed this behaviour in the OracleClient compatibility mode: generating update and delete commands for a result set with no primary key will throw an exception. To enable the compatibility mode, the static OracleUtils.OracleClientCompatible property should be set to true.

These changes will be available in the nearest build of dotConnect for Oracle which we plan to release in a week or two.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Wed 12 Jan 2011 17:12

We have released the 6.0.86 version of dotConnect for Oracle where these changes are available. This build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in this build, please refer to
http://www.devart.com/forums/viewtopic.php?t=19968
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle