ConflictOpiton not working

ConflictOpiton not working

Postby 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

Postby 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

Postby 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

Postby 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

Postby 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.
Byron
 
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Postby Oleg » Mon 15 Sep 2008 14:38

Could you send a small test project to reproduce the problem?
Oleg
Devart Team
 
Posts: 264
Joined: Thu 28 Oct 2004 13:56

Problem clarified

Postby 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.
Byron
 
Posts: 7
Joined: Sat 06 Sep 2008 07:42

Postby 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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 07 Oct 2008 16:13

A test project will be also appreciated.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Only Unique index

Postby 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.
Byron
 
Posts: 7
Joined: Sat 06 Sep 2008 07:42


Return to dotConnect for Oracle