Page 1 of 1

Migration from MS SQL to Oracle, timestamp problem

Posted: Mon 19 Apr 2010 14:03
by mmatveev
Hello,
I try to migrate database from MS SQL 2005 to Oracle 10. There is one problem related to MS SQL timestamp data type. In MS SQL I have a table with timestamp data type:

Code: Select all

CREATE TABLE [dbo].[Users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](128) NOT NULL,
	[FirstName] [nvarchar](128) NULL,
	[MiddleName] [nvarchar](128) NULL,
	[State] [smallint] NULL,
	[Rating] [decimal](6, 2) NULL,
	[IpAddress] [nvarchar](64) NULL,
	[Phone] [nvarchar](32) NULL,
	[EMail] [nvarchar](128) NULL,
	[Contacts] [nvarchar](256) NULL,
	[Description] [nvarchar](512) NULL,
	[Avatar] [image] NULL,
	[ConcurrencyStamp] [timestamp] NOT NULL,
	[Tag] [int] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
In Oracle the same table looks like this:

Code: Select all

CREATE TABLE "Users"
(
	"Id"		  NUMBER(9)  NOT NULL ,
	"LastName"	  NVARCHAR2(128)  NOT NULL ,
	"FirstName"	  NVARCHAR2(128)  NULL ,
	"MiddleName"	  NVARCHAR2(128)  NULL ,
	"State"		  NUMBER(9)  NULL ,
	"Rating"	  NUMBER(6,2)  NULL ,
	"IpAddress"	  NVARCHAR2(64)  NULL ,
	"Phone"	  NVARCHAR2(32)  NULL ,
	"EMail"		  NVARCHAR2(128)  NULL ,
	"Contacts"	  NVARCHAR2(256)  NULL ,
	"Description"	  NVARCHAR2(512)  NULL ,
	"Avatar"	  BLOB  NULL ,
	"ConcurrencyStamp"  RAW(8)  NOT NULL ,
	"Tag"		  NUMBER(9)  NULL 
);
And I also create triggers to set ConcurrencyStamp field:

Code: Select all

CREATE TRIGGER "Users_Id_Trigger"
BEFORE INSERT ON "Users"
FOR EACH ROW
BEGIN
  IF (:new."Id" IS NULL) THEN
    SELECT "Users_Seq".nextval INTO :new."Id" from dual;
  END IF;
  :new."ConcurrencyStamp" := utl_raw.cast_to_raw(dbms_utility.get_time);
END;
/

CREATE TRIGGER "Users_Update_Trigger"
BEFORE UPDATE ON "Users"
FOR EACH ROW
BEGIN
  :new."ConcurrencyStamp" := utl_raw.cast_to_raw(dbms_utility.get_time);
END;
/
The content of Oracle SSDL file .The CSDL and MSL are the same for MS
SQL and Oracle.

Code: Select all

  
    
      
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  

On the conceptual level the ConcurrencyStamp field is still binary type (Byte[] in .NET).
OK. Then I try to change IpAddress and State fields in the program.

Code: Select all

user.IpAddress = Util.GetIPAddress();
user.State = (Int16)Constants.UserState.Connected;
dbContext.SaveChanges();
and got an exception:

{"ORA-06550: line 6, column 50:\nPL/SQL: ORA-00932: inconsistent datatypes: expected - got BLOB\nORA-06550: line 4, column 1:\nPL/SQL: SQL Statement ignored"}

And dbMonitor prints at that moment:

Code: Select all

declare
  updatedRowid ROWID;
begin
update RETAILSOLUTIONS2."Users"
   set "State" = :p0, "IpAddress" = :p1
 where (("Id" = :p2) and ("ConcurrencyStamp" = :p3))
returning ROWID into updatedRowid;
open :outParameter for select "ConcurrencyStamp" from RETAILSOLUTIONS2."Users" where ROWID = updatedRowid;
end;
Why EF tries to convert Byte[] to BLOB? Devart documentation says that RAW data type is mapped into Byte[].

Posted: Tue 20 Apr 2010 11:32
by mmatveev
Forgot to mention that I’m using VS 2008 SP1, Devart dotConnect for Oracle v. 5.60.120.0, Oracle Express 10g 10.2.0.1

Posted: Tue 20 Apr 2010 13:56
by AndreyR
I'm unable to reproduce the problem using Oracle 10g Production and the latest dotConnect for Oracle 5.60.124.
Could you please upgrade to the latest version? Please let me know if the problem persists.

Posted: Tue 20 Apr 2010 17:17
by mmatveev
I've upgrated, but the problem persists. Note, that initially the conceptual schema has been generated in Visual Studio (not Entity Developer) for MS SQL and it should work both for MS SQL and Oracle.

Posted: Wed 21 Apr 2010 07:11
by mmatveev
Also the problem is reproduced when entity is updated. Creation is OK.

Posted: Wed 21 Apr 2010 07:33
by mmatveev
I found this article http://blog.redstream.nl/2008/03/04/com ... cle-blobs/. They are talking about comparing BLOBs in Oracle and using DBMS_LOB package to fix it. I'm new in Oracle, but seems the generated SQL statement for update must somehow use this package (however I use RAW(8) datatype for ConcurrencyStamp field).

Posted: Wed 21 Apr 2010 12:34
by AndreyR
I have sent you a working sample built using your SQL script.
Please let me know if anything goes wrong.

Posted: Wed 21 Apr 2010 17:03
by mmatveev
Thank you for your code. I execute it and it really updates entity. Unfortunately, it did not help me.
This is statement generated in my code before modifications:

Code: Select all

declare
  updatedRowid ROWID;
begin
update RETAILSOLUTIONS2."Users"
   set "State" = :p0, "IpAddress" = :p1
 where (("Id" = :p2) and ("ConcurrencyStamp" = :p3))
returning ROWID into updatedRowid;
open :outParameter for select "ConcurrencyStamp" from RETAILSOLUTIONS2."Users" where ROWID = updatedRowid;
end;
And this is what your program does:

Code: Select all

declare
  updatedRowid ROWID;
begin
update (SELECT 
      "Users"."Id" AS "Id", 
      "Users"."LastName" AS "LastName", 
      "Users"."FirstName" AS "FirstName", 
      "Users"."MiddleName" AS "MiddleName", 
      "Users"."State" AS "State", 
      "Users"."Rating" AS "Rating", 
      "Users"."IpAddress" AS "IpAddress", 
      "Users"."Phone" AS "Phone", 
      "Users"."EMail" AS "EMail", 
      "Users"."Contacts" AS "Contacts", 
      "Users"."Description" AS "Description", 
      "Users"."Avatar" AS "Avatar", 
      "Users"."ConcurrencyStamp" AS "ConcurrencyStamp", 
      "Users"."Tag" AS "Tag"
      FROM RETAILSOLUTIONS2."Users" "Users")
   set "State" = :p0, "IpAddress" = :p1
 where (("Id" = :p2) and ("LastName" = :p3))
returning ROWID into updatedRowid;
open :outParameter for select "ConcurrencyStamp" from (SELECT 
      "Users"."Id" AS "Id", 
      "Users"."LastName" AS "LastName", 
      "Users"."FirstName" AS "FirstName", 
      "Users"."MiddleName" AS "MiddleName", 
      "Users"."State" AS "State", 
      "Users"."Rating" AS "Rating", 
      "Users"."IpAddress" AS "IpAddress", 
      "Users"."Phone" AS "Phone", 
      "Users"."EMail" AS "EMail", 
      "Users"."Contacts" AS "Contacts", 
      "Users"."Description" AS "Description", 
      "Users"."Avatar" AS "Avatar", 
      "Users"."ConcurrencyStamp" AS "ConcurrencyStamp", 
      "Users"."Tag" AS "Tag"
      FROM RETAILSOLUTIONS2."Users" "Users") where ROWID = updatedRowid;
end;
As you see, your update clause doesn’t take into account “ConcurrencyStamp” field and optimistic concurrency doesn’t work. I’ll be able to change entity after ConsurrencyStamp is updated in another transaction. (OptimisticConcurrencyException is not raised).
[/code]

Posted: Thu 22 Apr 2010 05:42
by mmatveev
I was able to reproduce a problem with your code too. You have to set ConcurrencyMode="Fixed"

Code: Select all



Posted: Thu 22 Apr 2010 13:01
by AndreyR
Thank you for the report, I have reproduced the problem.
I will let you know about the results of our investigation.

Posted: Tue 27 Apr 2010 13:58
by AndreyR
We have fixed the problem. The fix will be included in the nearest build.

Posted: Tue 27 Apr 2010 14:06
by mmatveev
Glad to hear it. Thank you.