ConflictOpiton not working

[Byron]

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.

[Byron]

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]

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]

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]

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]

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

[Byron]

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]

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]

A test project will be also appreciated.

[Byron]

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.