Migration from MS SQL to Oracle, timestamp problem

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Migration from MS SQL to Oracle, timestamp problem

Post by mmatveev » Mon 19 Apr 2010 14:03

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[].

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Tue 20 Apr 2010 11:32

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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 20 Apr 2010 13:56

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.

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Tue 20 Apr 2010 17:17

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.

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Wed 21 Apr 2010 07:11

Also the problem is reproduced when entity is updated. Creation is OK.

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Wed 21 Apr 2010 07:33

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).

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 21 Apr 2010 12:34

I have sent you a working sample built using your SQL script.
Please let me know if anything goes wrong.

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Wed 21 Apr 2010 17:03

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]

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Thu 22 Apr 2010 05:42

I was able to reproduce a problem with your code too. You have to set ConcurrencyMode="Fixed"

Code: Select all



AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 22 Apr 2010 13:01

Thank you for the report, I have reproduced the problem.
I will let you know about the results of our investigation.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 27 Apr 2010 13:58

We have fixed the problem. The fix will be included in the nearest build.

mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Tue 27 Apr 2010 14:06

Glad to hear it. Thank you.

Post Reply