EF Update without ROWID

EF Update without ROWID

Postby 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;
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Re: EF Update without ROWID

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

Re: EF Update without ROWID

Postby Alladin » Mon 05 Aug 2013 09:13

Any news? Test project was send on 31 Juli.
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Re: EF Update without ROWID

Postby Shalex » Tue 06 Aug 2013 07:26

We are investigating the issue.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: EF Update without ROWID

Postby 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.
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Re: EF Update without ROWID

Postby Shalex » Mon 12 Aug 2013 14:23

Thank you for the suggestion. We are processing your request.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: EF Update without ROWID

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

Re: EF Update without ROWID

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


Return to dotConnect for Oracle