ConflictOpiton not working

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

ConflictOpiton not working

Post by Byron » Sat 06 Sep 2008 08:11

I get the same update statement when I change the ConflictOption to OverwriteChanges.

=ConflictOption.CompareAllSearchableValues
UPDATE "PMIS_EU"."SS_PP" SET "PG" = :p1, "PC" = :p2, "PT" = :p3, "PN" = :p4, "PP" = :p5, "PS" = :p6, "PID" = :p7 WHERE (("PG" = :p8) AND ("PC" = :p9) AND ("PT" = :p10) AND ("PN" = :p11) AND ((:p12 = 1 AND "PP" IS NULL) OR ("PP" = :p13)) AND ((:p14 = 1 AND "PS" IS NULL) OR ("PS" = :p15)) AND ("PID" = :p16))

=ConflictOption.OverwriteChanges]
UPDATE "PMIS_EU"."SS_PP" SET "PG" = :p1, "PC" = :p2, "PT" = :p3, "PN" = :p4, "PP" = :p5, "PS" = :p6, "PID" = :p7 WHERE (("PG" = :p8) AND ("PC" = :p9) AND ("PT" = :p10) AND ("PN" = :p11) AND ((:p12 = 1 AND "PP" IS NULL) OR ("PP" = :p13)) AND ((:p14 = 1 AND "PS" IS NULL) OR ("PS" = :p15)) AND ("PID" = :p16))

if I set .SetAllValues then I get the following error:
ORA-01461: can bind a LONG value only for insert into a LONG column

I'm using:
OS: Windows2003
VS2008 .Net 3.5 SP1
CoreLab.Data.dll 4.70.23.0
CoreLab.Oracle.dll 4.75.41.0
OracleDB 9i

select='SELECT PG, PC, PT, PN, PP, PS, PID FROM SS_PP'
connectstiring='Server=rob;Direct=True;Sid=rob;Persist Security Info=True;User Id=xxx_EU;Password=ee'

Code:
dda = new CLOracle.OracleDataAdapter(select, this.clOracleConn);
dda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dda.TableMappings.Add("Table", table);
scbCommand = new CLOracle.OracleCommandBuilder(dda);
scbCommand.Quoted = true;
scbCommand.UpdatingFields = "";
//OscbCommand.SetAllValues = true;
scbCommand.ConflictOption = ConflictOption.OverwriteChanges;
cmdUpdate = scbCommand.GetUpdateCommand();
Debug.WriteLine(cmdUpdate.CommandText);
records = dda.Update(dataset, table);

A first chance exception of type 'System.FormatException' occurred in mscorlib.dll
A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Last edited by Byron on Mon 08 Sep 2008 03:30, edited 1 time in total.

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Some more details

Post by Byron » Sat 06 Sep 2008 08:37

I get Concurrency violation on tables that have a field that can be null and the original value is null. I may or may not change the value. I may be changing another field. The end result is the row does not get updated because of the error. If I set SetAllvalues=true then I get the
ORA-01461: can bind a LONG value only for insert into a LONG column
I do not have any columns that have the type LONG

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Parameter mismatch

Post by Byron » Mon 08 Sep 2008 03:20

In addition to the problem above, it appears that there is a parameter mismatch. If I have column A(Allow Nulls) and B(Not null) and value is A='1' and B='2' and I set A="". The dataset action is to set A is null. But when I update the database via CommandBuilder, I get error saying cannot insert null into B. Now why would that be an error, I'm trying to insert null into A not B, so there seems to be a parameter mismatch.

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

AddWithKey does not work

Post by Byron » Thu 11 Sep 2008 02:53

Maybe this is related to the above problem. I discovered that that the
dda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
does not work.
When I switch the same code to use System.Data.OracleClient then it works. So problem is with the oraDirect provider.

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Exception not thrown

Post by Byron » Thu 11 Sep 2008 03:07

I executed a very simple update statement and the result was that no rows were updated AND no exception was thrown. I debugged the code and discovered that the Connection.ErrHandle property has an error but it is not clear because looks like the code is obfuscated here is the error:
base {"Unable to cast object of type 'CoreLab.Oracle.ab' to type 'CoreLab.Oracle.ak'."}System.Exception {System.InvalidCastException}

All I am doing is saving a string to varchar2 field.

Oleg
Devart Team
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Post by Oleg » Mon 15 Sep 2008 14:38

Could you send a small test project to reproduce the problem?

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Problem clarified

Post by Byron » Fri 03 Oct 2008 02:58

While creating a project that reproduces the problem I discovered the source.
The errors I was getting were related to Direct mode not supporting Unicode translation. So we just turned that off.

The problem that remains for us to solve is this: Our partner who controls the Oracle database setup are not willing to set primary keys... only unique keys. This was not a problem when we used System.Data.OracleClient as it can use either primary key or unique key in the dataAdaptor for overriding changes.
When we switched to OraDirect, OverridingChanges compared all values because it seems it cannot see the unique key.

Will Devart fix this problem? If not then we will have to switch back to: System.Data.OracleClient.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Oct 2008 16:07

I cannot reproduce the problem with ConflictOption working with this table:

Code: Select all

CREATE TABLE TEST.UNIQUE_KEYS (
  MY_UNIQUE NUMBER NOT NULL,
  MY_VARCHAR2 VARCHAR2(20),
  CONSTRAINT SYS_C0053947 UNIQUE (MY_UNIQUE))
Please send me the script that creates the table where ConflictOption doesn't work.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 07 Oct 2008 16:13

A test project will be also appreciated.

Byron
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Only Unique index

Post by Byron » Tue 14 Oct 2008 11:26

I believe I have found the problem. Upon further checking, it appears that our db group uses ERWin to produce there oracle tables. They create their tables then later they create unique indexes, but without primary or unique key constraints. Microsoft's Oracle Client and CommandBuilder can still determine the unique key but not oraDirect. They are unwilling to add a constraint for me as it would change 1000s of tables and they do not want to take the risk. So if I cannot get oraDirect to do overwrite changes then for some updates I'll still have to use the Microsoft Oracle client provider.
Sorry, due to time, I'm unable to create a project at this time.

Post Reply