EF Update without ROWID

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

EF Update without ROWID

Post by Alladin » Mon 29 Jul 2013 21:05

Hi there,

I have an updatable view with INSTEAD OF triggers defined. Such views don't support RETURNING clause (ORA-22816: unsupported feature with RETURNING clause), and dotConnect fails to update those views because of generated SQL for update:

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
UPDATE TUSERS
   SET LASTLOGINTIME = :p0
 WHERE ID = :p1 AND TS = :p2
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT TS FROM TUSERS WHERE ROWID = updatedRowid;
END;
How is it possible to tell dotConnect EF to use ENTITY KEYS (ID in this case, TS is concurrency check) instead of ROWID pseudocolumn? At the end it must look like this:

Code: Select all

BEGIN
UPDATE TUSERS
   SET LASTLOGINTIME = :p0
 WHERE ID = :p1 AND TS = :p2;
OPEN :outParameter FOR SELECT TS FROM TUSERS WHERE ID = :p1;
END;

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

Re: EF Update without ROWID

Post by Shalex » Tue 30 Jul 2013 16:51

Could you please send us a small test project with the corresponding DDL script so that we can reproduce the issue in our environment and investigate it?

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Re: EF Update without ROWID

Post by Alladin » Mon 05 Aug 2013 09:13

Any news? Test project was send on 31 Juli.

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

Re: EF Update without ROWID

Post by Shalex » Tue 06 Aug 2013 07:26

We are investigating the issue.

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Re: EF Update without ROWID

Post by Alladin » Thu 08 Aug 2013 12:53

For optimistic locking check, this SQL would work better:

Code: Select all

DECLARE
  RC$ NUMBER;

BEGIN

UPDATE MYTABLE
SET LASTLOGINTIME = :p0 
WHERE ID = :p1 AND TS = :p2;

RC$ := SQL%ROWCOUNT;

OPEN :outParameter FOR 
SELECT TS,...[other Sync OnUpdate columns] FROM MYTABLE
WHERE ID = :p1 AND RC$ > 0;

END;
Returning cursor will yield no records in case of optimistic lock.

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

Re: EF Update without ROWID

Post by Shalex » Mon 12 Aug 2013 14:23

Thank you for the suggestion. We are processing your request.

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

Re: EF Update without ROWID

Post by Shalex » Wed 28 Aug 2013 14:00

The config.DmlOptions.UseReturningClause configuration option (default value is True) is added to provide the possibility to turn off generation of RETURNING clause when obtaining database-generated values with INSERT/UPDATE commands. We will post here when the corresponding build of dotConnect for Oracle is available for download.

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

Re: EF Update without ROWID

Post by Shalex » Fri 06 Sep 2013 08:02

New version of dotConnect for Oracle 7.9 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=27875.

Post Reply